Mysql从8.0版本开始,也和Sql Server、Oracle一样支持在查询中使用窗口函数,本文将根据官方文档,通过实例介绍窗口函数并举例分组排序函数的使用。
本文用到的学生分数表格和语句如下:
DROP TABLE IF EXISTS Marks;CREATE TABLE Marks(学生 VARCHAR(10), 科目 CHAR(2), 分数 INT); insert into Marks values ('赵四','语文',88),('赵四','数学',48),('赵四','英语',75), ('张三','语文',30),('张三','数学',75),('张三','英语',75), ('王五','语文',90),('王五','数学',94),('王五','英语',70), ('李四','语文',82),('李四','数学',69),('李四','英语',90); SELECT * FROM Marks;
首先创建了如下班级分数表格为例:
窗口函数可以大体分为两大类,第一类是能够作为窗口函数的聚合函数:SUM、AVG、COUNT、MAX、MIN,第二类是以RANK、DENSE_RANK、ROW_NUMBER为代表的专用窗口函数。为了便于理解窗口函数,首先以聚合函数sum()为例,下面分别使用窗口函数和聚合函数展示每个学生的成绩总分:
-- 作为窗口函数 SELECT 学生,科目,分数, SUM(分数) OVER (PARTITION BY 学生) AS '总分' FROM Marks;
-- 与直接使用sum()聚合函数得到的结果一样 SELECT 学生,SUM(分数) AS '总分' FROM Marks GROUP BY 学生;
因为,所有窗口函数的执行在JOIN, WHERE, GROUP BY, HAVING的结果集之后,在ORDER BY, LIMIT, SELECT DISTINCT之前。当PARTITION BY执行时GROUP BY的聚合过程已经完成了,因此不会再产生数据聚合。
窗口函数的语法
window_function_name(expression)
OVER (
[partition_defintion]
[order_definition]
[frame_definition]
)
1.partition_defintio 窗口分区
PARTITION BY expr [, expr] ...
2. order_definition 窗口排序
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
SELECT 学生,科目,分数, MAX(分数) OVER (PARTITION BY 学生 ORDER BY 分数 DESC) AS '最高分' FROM Marks;
frame_clause: frame_units frame_extentframe_units: {ROWS | RANGE}frame_extent: {frame_start | frame_between}frame_between: BETWEEN frame_start AND frame_endframe_start, frame_end: { CURRENT ROW | UNBOUNDED PRECEDING | UNBOUNDED FOLLOWING | expr PRECEDING | expr FOLLOWING}
SELECT 学生,科目,分数, AVG(分数) OVER (PARTITION BY 学生 ORDER BY 分数 ASC ROWS 2 preceding) AS moving_avg FROM Marks;
下面我们使用RANGE对每个分区内从第一行到当前行计算平均值,可以看到由于RANGE根据当前值来确定行,张三的第二行就已经出现了三门的均分,对于三门分数不同的李四,滑动平均值得结果没有变化。
,科目,分数,
AVG(分数) OVER (PARTITION BY 学生
ORDER BY 分数 ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS AVGFROM Marks;
frame_extent指定帧的起止点,在其中也可以只用frame_start(结束位置就默认为当前行)和frame_between指定起点和终点
frame_start和frame_end可以是以下几种:
-
CURRENT ROW: 当前行
-
UNBOUNDED PRECEDING: 区间的第一行
-
UNBOUNDED FOLLOWING:区间的最后一行
-
N PRECEDING: 当前行之前的N行,可以是数字,也可以是一个能计算出数字的表达式
-
N FOLLOWING:当前行之后的N行,可以是数字,也可以是一个能计算出数字的表达式
如果没指定帧的话,默认的frame取决于ORDER BY。
-
如果有ORDER BY,SQL会默认帧是区间内从第一行(UNBOUNDED PRECEDING)到当前行(CURRENTROW)
学生 ORDER BY 分数 ASC) AS '最高分' FROM Marks;-- 结果相同SELECT 学生,科目,分数, MAX(分数) OVER (PARTITION BY 学生 ORDER BY 分数 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS '最高分' FROM Marks;
-
如果没有ORDER BY,SQL会默认帧是区间内从第一行(UNBOUNDED PRECEDING)到最后一行(UNBOUNDED FOLLOWING)
SELECT 学生,科目,分数, MAX(分数) OVER (PARTITION BY 学生) AS '最高分' FROM Marks; -- 结果相同 SELECT 学生,科目,分数, MAX(分数) OVER (PARTITION BY 学生 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS '最高分' FROM Marks;
日常我们更常用的是在窗口函数中使用排序函数:
-
ROW_NUMBER: 函数名即是排序方法,也就是输出结果集分区的行号(例如:1,2,3,4,5...)
-
RANK: 返回结果集的分区内数据进行跳跃排序。也就是为相同数值的行输出相同排序结果,对于下一行不同的数据将返回行号(例如:1,1,3,4...)
-
DENSE_RANK: 返回结果集分区中每行的连续排名,排名值没有间断。行排名等于该行之前不同排名值的数量加一(例如:1,1,2,3,4...)
-
NTILE: 将有序分区中的数据分发到指定数目的组中。以本文数据为例,将60-90分的分数等分为4组,即第1组为[90, 82.5),第2组为[82.5, 75),第3组为[75, 67.5),第4组为[67.5, 60]
SELECT 学生,科目,分数 ,ROW_NUMBER() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS "Row_Number" ,RANK() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS 'Rank' ,DENSE_RANK() OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS "Dense_Rank" ,NTILE(4) OVER (PARTITION BY 科目 ORDER BY 分数 DESC) AS 'Quartile' FROM Marks;