点击关注上方“
程序员私房菜
”，设为“置顶或星标”，第一时间送达技术干货。
来源：知乎
转自：大数据分析挖掘和Python机器学习
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 学生;
在这个例子中sum()函数作为窗口函数，通过对‘学生’分区后，加总各个科目的分数得到总分，得分结果与sum()聚合函数的结果一致，但结果中保留了每一行的信息，出现了重复的总分行。
因为，所有窗口函数的执行在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] )
首先需要指定窗口函数的函数名，也就是在上个例子中用的sum()，之后的OVER子句中即使没有内容，括号也需要保留，窗口由[partition_defintion]，[order_definition]，[frame_definition]确定，任何一个都不是必须的。
1.partition_defintio 窗口分区
PARTITION BY expr [, expr] ...
根据表达式的计算结果来进行分区（列名也是一种表达式）。在例子中"PARTITION BY 学生"对学生列的值分区。
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}
窗口框架的作用对分区进一步细分，frame_unit有两种，分别是ROWS和RANGE，ROWS通过指定当前行之前或之后的固定数目的行来限制分区中的行，RANGE按照排序列的当前值，根据相同值来确定分区中的行。以下通过计算当前行的前两行的平均值计算分数的移动平均分数。
SELECT 学生,科目,分数, AVG(分数) OVER (PARTITION BY 学生 ORDER BY 分数 ASC ROWS 2 preceding) AS moving_avg FROM Marks;
下面我们使用RANGE对每个分区内从第一行到当前行计算平均值，可以看到由于RANGE根据当前值来确定行，张三的第二行就已经出现了三门的均分，对于三门分数不同的李四，滑动平均值得结果没有变化。
SELECT 学生,科目,分数, 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。
SELECT 学生,科目,分数, MAX(分数) OVER (PARTITION BY 学生 ORDER BY 分数 ASC) AS '最高分' FROM Marks; MAX(分数) OVER (PARTITION BY 学生 ORDER BY 分数 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS '最高分' FROM Marks;
SELECT 学生,科目,分数, MAX(分数) OVER (PARTITION BY 学生) AS '最高分' FROM Marks;
SELECT 学生,科目,分数, MAX
