1.定义
- MySQL 的开窗函数(Window Functions)是一类特殊的 SQL 函数,用于在查询结果的某个窗口(即结果集的一个分区)中进行计算。
- 窗口函数为了解决想要即显示聚集前的数据,又要显示聚集后的数据。
- 开窗函数对一组值进行操作,不需要group by 子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列
- 开窗函数不会压缩行数,而是在原始行数的基础上进行计算。
- 这允许对数据进行复杂的分析和排序操作,而不影响结果集的完整性。
- 开窗函数不会修改源数据表的结构,会在表的最后一列添加想要的结果,如果分组存在多行数据,则重复显示
注意:开窗函数在mysql8.0后才有
2.语法
函数名(列) over(选项【选项可以为 partition by 列 order by 列】)
- 方式一:按照列所有行进行分组
over(partition by 列)
- 方式二:按照列排序
over(order by 列)
- 方式三:按照列1分组,按照列2排序
over(partition by 列1 order by 列2)
3. 开窗函数汇总
4.聚合开窗函数
-
常见聚合函数
常见聚合函数 含义 min() 最小值 max() 最大值 avg() 平均值 sum() 求和 count() 计数 -
排序开窗函数
排序开窗函数 含义 描述 例子 row_number( ) 行数 生成连续的序号,不考虑分数相同 例如:值:15556,排序:123456… rank( ) 排名 相同分数的排名一样,后面排名为真正的序号,排名存在跳跃性; 例子:值:13335,排序:12225 dense_rank( ) 密集排序 相同分数的排名一样,是连续的排名 ; 例如:值:15558,排序:12223 ntile( ) 分组排序 ntile有桶的概念; 排序的结果数字大小只能用于桶与桶之间,桶内部虽然序号相同,但是num不一定相同 ntile(6),将总记录划分为6桶,如果是12条记录,就是一桶2条记录;排名就是:112233445566 -
前后函数
函数 描述 lag(col,n) 用于获取当前行之前第个值,即统计窗口内向上第n行值 lead(col,n) 用于获取当前行之后第个值,即统计窗口内往下第n行值 -
头尾函数
函数 描述 first_value(column) 取窗口中字段col的第一个值 last_value(column) 取窗口中字段col的最后一个值 nth_value(column,n) 取窗口中第n顺序的值 -
分布函数
函数 描述 cume_dist() row/sum(row);用于查询小于或等于某个值的比例 percent_rank() 等级值百分比:(rank-1) / (rows-1)。rank的值为使用RANK()函数产生的序号,rows的值为当前窗口的总记录数
1. 常见聚合函数
常见聚合函数 | 含义 |
---|---|
min() | 最小值 |
max() | 最大值 |
avg() | 平均值 |
sum() | 求和 |
count() | 计数 |
-
函数名如果是聚合函数,则成为聚合开窗函数
-
语法:
聚合函数(列) over(partition by 列 order by 行) as 列名
-
不同聚合函数对比
SELECT a.*, max(s_score) over(PARTITION by c_id ) max_score, min(s_score) over(PARTITION by c_id ) min_score, avg(s_score) over(PARTITION by c_id ) avg_score, sum(s_score) over(PARTITION by c_id ) sum_score, count(s_score) over(PARTITION by c_id ) count_score FROM score a
-
例一:计算每个学生的及格数
- 使用聚合函数
SELECT student_id, count(sid) FROM score WHERE num >= 60 GROUP BY student_id;
- 使用开窗函数
SELECT distinct student_id, count(sid) over ( PARTITION BY student_id ORDER BY student_id ) 及格数 FROM score WHERE num >= 60;
- 使用聚合函数
2. 排序开窗函数
排序开窗函数 | 含义 | 描述 | 例子 |
---|---|---|---|
row_number( ) | 行数 | 生成连续的序号,不考虑分数相同 | 例如:值:15556,排序:123456… |
rank( ) | 排名 | 相同分数的排名一样,后面排名为真正的序号,排名存在跳跃性; | 例子:值:13335,排序:12225 |
dense_rank( ) | 密集排序 | 相同分数的排名一样,是连续的排名 ; | 例如:值:15558,排序:12223 |
ntile( ) | 分组排序 | ntile有桶的概念; 排序的结果数字大小只能用于桶与桶之间,桶内部虽然序号相同,但是num不一定相同 | ntile(6),将总记录划分为6桶,如果是12条记录,就是一桶2条记录;排名就是:112233445566 |
- 例子:各种排名的区别
SELECT a.*, rank() over(PARTITION by c_id order by s_score desc) rank排名, row_number() over(PARTITION by c_id order by s_score desc) row_number排名, dense_rank() over(PARTITION by c_id order by s_score desc) dense_rank排名, ntile(3) over(PARTITION by c_id order by s_score desc) ntile排名 FROM score a
- 例子:查询各科成绩前三名的学生成绩信息
SELECT * FROM ( SELECT s.sid, s1.sname, s1.gender, c.cname, s.num, dense_rank () over ( PARTITION BY c.cname ORDER BY num DESC ) AS dense_rank排名 FROM score AS s JOIN student s1 ON s.student_id = s1.sid LEFT JOIN course c ON s.course_id = c.cid ) AS e WHERE dense_rank排名 <= 3;
3. 前后函数
函数 | 描述 |
---|---|
lag(col,n) | 用于获取当前行之前第个值,即统计窗口内向上第n行值 |
lead(col,n) | 用于获取当前行之后第个值,即统计窗口内往下第n行值 |
-
这两种函数可以用于同列中相邻行的数据相减操作
-
例子
SELECT * , lag(s_score, 1) over ( PARTITION BY s_id ORDER BY c_id ) lag_score, lead(s_score, 1) over ( PARTITION BY s_id ORDER BY c_id ) lead_score from score;
-
案例:对于下面的数据,对于同一用户(uid)如果在2分钟之内重新登录,则判断为作弊,统计哪些用户有作弊行为,并计算作弊次数
SELECT uid,count(1) 作弊次数 FROM ( SELECT id, uid, login_time, -- 加一列时间 lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ) lead_time, -- 求2列时间的差值 timestampdiff( SECOND, login_time, ( lead (login_time, 1) over ( PARTITION BY uid ORDER BY login_time ))) 相差秒数 FROM lag_table ) as e where format(相差秒数 / 60, 3)<=2 group by uid;
4. 头尾函数
函数 | 描述 |
---|---|
first_value(column) | 取窗口中字段col的第一个值 |
last_value(column) | 取窗口中字段col的最后一个值 |
nth_value(column,n) | 取窗口中第n顺序的值 |
-
nth_value函数依赖row_number来排序。故存在相等但排序不重复的情况。
-
例子:查询成绩第二和第三的分数
SELECT *, row_number() over(PARTITION by c_id order by s_score desc) as rn, nth_value(s_score,2) over(PARTITION by c_id order by s_score desc) as second_score, nth_value(s_score,3) over(PARTITION by c_id order by s_score desc) as third_score from score;
-
例一:不分组
SELECT * , first_value(s_score) over ( ORDER BY s_id) max_score, last_value(s_score) over ( ORDER BY s_id) min_score, nth_value(s_score,2) over(order by s_id) as nth_score from score;
-
例二:分组
SELECT c_id, s_score, first_value(s_score) over ( PARTITION by c_id ORDER BY s_score desc) max_score, last_value(s_score) over ( PARTITION by c_id ORDER BY s_score desc) min_score, nth_value(s_score,2) over(PARTITION by c_id order by s_score desc) as nth_score from score;
-
例一:取科目成绩最大值(first_value)
-- 根据降序,第一个值是最大值 select s.sid, s1.sname, s1.gender, c.cname, s.num, first_value(num) over(partition by c.cname order by num desc) as firstvalue from score s join student s1 on s.student_id=s1.sid left join course c on s.course_id=c.cid;
5. 分布函数
函数 | 描述 |
---|---|
cume_dist() | row/sum(row);用于查询小于或等于某个值的比例 |
percent_rank() | 等级值百分比:(rank-1) / (rows-1)。rank的值为使用RANK()函数产生的序号,rows的值为当前窗口的总记录数 |
- 例一
SELECT *, rank() over(PARTITION by c_id order by s_score) as "rk", count(s_score) over(PARTITION by c_id) as "count", PERCENT_RANK() over(PARTITION by c_id order by s_score) as "PERCENT_RANK", cume_dist() over(PARTITION by c_id order by s_score) as "cume_dist" from score;
6. order by修改范围
属性 | 含义 |
---|---|
unbounded | 无限制的 |
preceding | 分区的当前记录向前的偏移量 |
current | 当前 |
following | 分区的当前记录向后偏移量 |
-
当前分组数据与数据进行比较,取最后一个值
order by rows between unbounded preceding and unbounded following
-
例子
-- 取科目成绩最小值 -- 根据降序,最后一个值是最小值,要修改范围 select s.sid, s1.sname, s1.gender, c.cname, s.num, last_value(num) over(partition by c.cname order by num desc rows between unbounded preceding and unbounded following) as lastvalue from score s join student s1 on s.student_id=s1.sid left join course c on s.course_id=c.cid;