一、mysql窗口函数简介
MySQL从8.0开始支持窗口函数,这个功能在大多商业数据库和部分开源数据库中早已支持,有的也叫分析函数。
窗口函数:在满足某些条件的记录集合上执行的特殊函数,对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同,窗口大小都是固定的,称为静态窗口;有的函数则相反,不同的记录对应着不同的窗口,称为滑动窗口。
1.窗口函数和普通聚合函数的区别:
①聚合函数是将多条记录聚合为一条;窗口函数是每条记录都会执行,有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。
2.窗口函数的基本用法:
函数名 OVER 子句
over 关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下4中语法来设置窗口。
①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读;
②PARTITION BY 子句:窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行;
③ORDER BY子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号;
④FRAME子句:FRAME是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用。
3.按功能划分,可将MySQL支持的窗口函数分为如下几类:
窗口函数 | window_name |
---|---|
序号函数 | row_number() / rank() / dense_rank() |
分布函数 | percent_rank() / cume_dist() |
前后函数 | lag() / lead() |
头尾函数 | first_val() / last_val() |
其他函数 | nth_value() / nfile() |
二、窗口函数使用示例
演示数据:
# 序号,学号,课程号,分数
create table score (
id int(10) NOT NULL,
sid int(10) NOT NULL,
lid varchar(10) NOT NULL,
score int(10) NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO score(id,sid,lid,score) VALUES
('1','1001','L01','100'),('2','1001','L02','98'),('3','1001','L03','99'),
('4','1001','L04','100'),('5','1001','L05','89'),('6','1002','L01','99'),
('7','1002','L02','90'),('8','1002','L03','89'),('9','1002','L04','87'),
('10','1002','L05','89'),('11','1003','L01','90'),('12','1003','L02','96'),
('13','1003','L03','79'),('14','1003','L04','89'),('15','1003','L05','96'),
('16','1004','L01','100'),('17','1004','L02','89'),('18','1004','L03','97'),
('19','1004','L04','79'),('20','1004','L05','85');
1.序号函数: row_number、rank、dense_rank
- 用途:显示分区中的当前行号
- 应用场景:查询每个学生的分数最高的前N门课程 (分组排序),如 1、2、3 、4
row_number : 排序序号连续,不重复(相同分数的人序号递增)
SELECT
sid,
lid,
score,
ROW_NUMBER () OVER (PARTITION BY sid ORDER BY score desc) AS srank
FROM
score;
rank : 排序序号不连续,相同分数的人序号相同,但会占用序号位置,如 1、1、3 、4
SELECT
sid,
lid,
score,
RANK () OVER (
PARTITION BY sid
ORDER BY
score DESC
) AS srank
FROM
score;
dense_rank : 序号连续,且分数相同的人序号相同,如1、1 、2 、3
SELECT
sid,
lid,
score,
dense_rank () OVER (
PARTITION BY sid
ORDER BY
score DESC
) AS srank
FROM
score;
2.分布函数:percent_rank()、cume_dist()
percent_rank():
- 每行按照公式(rank-1) / (rows-1)进行计算。
- 其中,rank为rank()函数产生的序号,rows为当前窗口的记录总行数
SELECT
sid,
lid,
score,
rank() over w as row_num,
percent_rank() over w as percent
from score
window w as (partition by sid order by score desc);
cume_dist():
- 用途:分组内小于、等于当前rank值的行数 / 分组内总行数
- 应用场景:查询大于等于当前成绩(score)的比例 (大于主要取决排序是降序,若为升序,则就是小于)
SELECT
sid,
lid,
score,
rank () over w AS row_num,
cume_dist () over w AS percent
FROM score
window w AS ( PARTITION BY sid ORDER BY score DESC );
3.前后函数: lag(expr,n)、lead(expr,n)
- 用途:返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值
- 应用场景:查询当前同学和前1名同学的成绩的差值
SELECT
sid,
lid,
score,
srank,
prescore,
(prescore - score) AS diffscore
FROM
(
SELECT
sid,
lid,
score,
dense_rank() over w as srank,
lag (score, 1) over w AS prescore
FROM score
window w AS ( PARTITION BY lid ORDER BY score DESC )
) a
4.头尾函数: FIRST_VALUE(expr)、LAST_VALUE(expr)
- 用途:返回分组中第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))指定参数的值
- 应用场景:查询截止当前行,各科成绩最大和最小的分数
SELECT
sid,
lid,
score,
first_value(score) over w AS max_score,
last_value(score) over w AS min_score
FROM score
window w AS ( PARTITION BY lid ORDER BY score DESC );
5.其它函数:NTH_VALUE(expr, n)、NTILE(n)
NTH_VALUE(expr,n)
- 用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名
- 应用场景:截止到当前成绩,显示每个同学的成绩中排名第2和第3的成绩的分数
SELECT
sid,
lid,
score,
row_number() over w as srank,
nth_value(score,2) over w AS second_score,
nth_value(score,3) over w AS third_score
FROM score
window w AS ( PARTITION BY sid ORDER BY score desc )
NTILE(n)
- 用途:将分区中的有序数据分为n个等级,记录等级数
- 应用场景:将每门课程按照成绩分成3组
SELECT
sid,
lid,
score,
row_number() over w as srank,
ntile(3) over w AS nf
FROM score
window w AS ( PARTITION BY lid ORDER BY score desc );
NTILE(n)函数在数据分析中应用较多,比如由于数据量大,需要将数据平均分配到n个并行的进程分别计算,此时就可以用NTILE(n)对数据进行分组(由于记录数不一定被n整除,所以数据不一定完全平均),然后将不同桶号的数据再分配。
三、聚合函数作为窗口函数
- 用途:在窗口中每条记录动态地应用聚合函数(SUM()、AVG()、MAX()、MIN()、COUNT()),可以动态计算在指定的窗口内的各种聚合函数值
- 应用场景:截止到当前时间,查询每个学生的累计分数、平均分数、分数最高的科目、分数最低的科目
SELECT
sid,
lid,
score,
sum(score) over w as sumscore,
avg(score) over w as avgscore,
max(score) over w as maxscore,
min(score) over w as minscore
FROM score
window w AS ( PARTITION BY sid ORDER BY score desc );