MySQL知识点总结（二）：窗口函数 1.什么是窗口函数 ？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名TopN 问题：找出每个部门排名前 N 的员工进行奖励

面对这类需求，就需要使用 sql 的高级功能窗口函数了。

窗口函数，也叫 OLAP 函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口其实是指一个记录集合，而窗口函数则是在满足某些条件的记录集合上执行指定的函数方法。在日常工作中比较常见的例子比如求学生的单科成绩排名、求前三名等等之类的。

窗口函数的基本语法如下：

<窗口函数> OVER ( PARTITION BY <用于分组的列名> ORDER BY <用于排序的列名> )

那么语法中的<窗口函数>都有哪些呢？<窗口函数> 的位置，可以放以下两种函数：

像一些聚合函数如 SUM()、AVG()、COUNT()、MAX() 与 MIN() 等等以及专用的窗口函数 RANK()、DENSE_RANK() 与 ROW_NUMBER() 等等

因为窗口函数是对 where 或者 group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在 select 子句中。

2.排序函数

就是进行排序操作，显示排名。常用的是 RANK()、DENSE_RANK() 与 ROW_NUMBER()。另外，NTILE 也可视为特殊的排序函数。

SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS RANK_排名, DENSE_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS DENSE_RANK_排名, ROW_NUMBER() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS ROW_NUMBER_排名FROM 成绩单

函数说明RANK根据排序字段为每个分组中的每一行分配一个序号。排名值相同时，序号相同，但序号中存在间隙，例如：1、1、1、4、5DENSE_RANK根据排序字段为每个分组中的每一行分配一个序号。排名值相同时，序号相同，序号中没有间隙，例如：1、1、1、2、3ROW_NUMBER为表中的每一行分配一个序号，可以指定分组（也可以不指定）及排序字段（连续且不重复），例如：1、2、3、4、5

我们要获取各科目排名第一的学生及得分，就可以再加个条件判断即可，需要注意这里用到了子查询。

SELECT * FROM ( SELECT *, DENSE_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS DENSE_RANK_排名 FROM 成绩单 ) WHERE DENSE_RANK_排名 = 1;

窗口函数具备了我们之前学过的 group by 子句分组的功能和 order by 子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by 分组汇总后改变了表的行数，一行只有一个类别。而 partiition by 和 rank 函数不会减少原表中的行数。

NTILE

另外还有个 NTILE(n) 将分区中的有序数据分为 n 个等级，记录等级数。NTILE(n) 在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到 n 个并行的进程分别计算，此时就可以用 NTILE(n) 对数据进行分组（由于记录数不一定被 n 整除，所以数据不一定完全平均），然后将不同桶号的数据再分配。

比如按照学号分区、得分排序分成 2 个等级。

SELECT *, NTILE(2) OVER ( PARTITION BY 学号 ORDER BY 得分 DESC ) AS NTILE_FROM 成绩单

3.分布函数

分布函数有两个：PERCENT_RANK() 和 CUME_DIST()

PERCENT_RANK() 的用途是每行按照公式 (rank-1) / (rows-1) 进行计算。其中，rank 为 RANK() 函数产生的序号，rows 为当前窗口的记录总行数。

SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 , PERCENT_RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS PERCENT_RANK_FROM 成绩单

CUME_DIST() 的用途是分组内小于、等于当前 rank 值的行数 / 分组内总行数。

查询小于等于当前成绩的比例。

SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 , CUME_DIST() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS CUME_DIST_FROM 成绩单

可以看到，数学科目中有 0.5 也就是 50% 的学生得分 120，超过 66.66% 的学生成绩在 118 分及以上。

4.前后函数

查询当前行指定字段往 前 N 行数据：LAG()

查询当前行指定字段往 后 N 行数据：LEAD()

LEAD() 和 LAG() 主要用于将某个时间段与给定指标的前一个时间段进行比较，比如：

获得每年销售额与上一年销售额之间的差值获得每月注册 / 转换 / 网站访问次数的增量按月比较用户流失率

前 N 行 LAG(expr[,N[,default]])。比如我们看各科目同学每个人往前 3 名的同学得分。

SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS RANK_排名 , LAG(得分, 3) OVER ( PARTITION BY 科目 ORDER BY 得分 DESC) AS LAG_FROM 成绩单

可以看到，各科目前三行都是 NULL 空值，这是因为前三行不存在它们往前 3 行的值。rank 4 的前 3 是 rank 1，对应得分是120。

这个可以用于进行一些诸如环比的情况，在这里我们可以计算当前同学与前 1 名同学得分差值，操作如下：

SELECT *, LAG_ - 得分 FROM ( SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS RANK_排名, LAG(得分, 1 ) OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS LAG_ FROM 成绩单 )

5.首尾函数

查询指定字段第一或最后的数据 FIRST_VALUE(expr) 和 LAST_VALUE(expr)。

查询各科目得分第 1 的分值。

SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS RANK_排名, FIRST_VALUE(得分) OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS FIRST_VALUE_得分 FROM 成绩单

我们可以计算各个同学与第 1 名的差距（上面前后函数部分介绍了和前 1 名的差距）。

SELECT *, FIRST_VALUE_得分 - 得分 FROM ( SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS RANK_排名, FIRST_VALUE(得分) OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS FIRST_VALUE_得分 FROM 成绩单 )

LAST_VALUE(expr) 就是最后 1 名了，这里不再赘述。

另外还有 NTH_VALUE(EXPR, N) 返回窗口中第 N 个 EXPR 的值，EXPR 可以是表达式，也可以是列名，使用 NTH_VALUE() 函数。

比如查询得分排名第 4 的数据。

SELECT *, RANK() OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS RANK_排名, NTH_VALUE(得分,4) OVER ( PARTITION BY 科目 ORDER BY 得分 DESC ) AS NTH_VALUE_得分 FROM 成绩单

6.聚合函数

在窗口中每条记录动态地应用聚合函数 SUM()、AVG()、MAX()、MIN()、COUNT()，可以动态计算在指定的窗口内的各种聚合函数值。

AVG()在窗口函数中非常强大，因为它可以计算一段时间内的移动平均值。移动平均线是一种简单但有效的短期预测值的方法。比如

获取每周销售额的总体趋势获取每周转换或网站访问的总体趋势

例：获取转化次数的 10 天移动平均值

SELECT Date, dailyConversions, AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverageFROM conversions 7.滑动窗口 7.1 PRECEDING SELECT product_id, product_name, sale_price, AVG(sale_price) OVER ( ORDER BY product_id ROWS 2 PRECEDING) AS moving_avgFROM Product

Rows 2 preceding 中文的意思是之前的两行，preceding 可以把它理解为不含当前行情况下截止到之前几行。根据上图可以看出在每一行，都会求出当前行附近的 3 行（当前行+附近 2 行）数据的平均值，这种方法也叫作 移动平均。

7.2 FOLLOWING

Rows 2 following 中文意思是之后的两行，跟 preceding 正好相反，preceding 是向前，following 是向后。

7.3 PRECEDING 和 FOLLOWING 结合 SELECT product_id, product_name, sale_price, AVG(sale_price) OVER (ORDER BY product_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avgFROM Product

从以上的运行结果可以看出是把每一行（当前行）的前一行和后一行作为汇总的依据。

8.实例

假设有一张含两列（用户 id、登陆日期）的表，查询每个用户连续登陆的天数、最早登录时间、最晚登录时间和登录次数。

首先要对数据进行去重，防止同一个用户一天之内出现连续登录的情况；假如一个用户是连续登录的话，用 login_time - 窗口函数的排序 后得到的日期应该是一样的，连续登录的用户前后之间的时间差就是一个差值为 1 的等差数列；

第一步，先用 row_number() 函数排序，然后用登录日期减去排名，得到辅助列日期，如果辅助列日期是相同的话，证明用户是连续登录。

select a.user_id, a.date, a.排序, date_sub(a.date, interval a.排序 day) as 辅助列from ( select user_id, date(login_time) as date, row_number() over (partition by user_id order by login_time) as 排序 from user_login) as a

第二步，用 user_id 和辅助列作为分组依据，分到一组的就是连续登录的用户。在每一组中最小的日期就是最早的登陆日期，最大的日期就是最近的登陆日期，对每个组内的用户进行计数就是用户连续登录的天数。

select b.user_id, max(b.date) as 最近登陆日期, min(b.date) as 最早登陆日期, count(b.date) as 登陆次数from ( select distinct a.user_id, a.date, a.排序, date_sub(a.date, interval a.排序 day) as 辅助列 from ( select user_id, date(login_time) as date, row_number() over (partition by user_id order by login_time) as 排序 from user_login ) as a) as bgroup by b.user_id, b.'辅助列'

若求解每个用户的最大登录天数。其实可以在以上的查询结果为基础，利用聚合函数就可以求出最大的登录天数问题。假如求解连续登录 5 天的用户，除了可以使用上述的方法，还可以使用 lead 函数进行窗口偏移来进行求解。

示例：数据还是上题中的数据，求解连续登录五天的用户。

第一步，用 lead 函数进行窗口偏移，查找每个用户 5 天后的登陆日期是多少，如果是空值，说明他没有登录。

select distinct user_id, date(login_time) as 日期, lead(date(login_time),4) over (partition by user_id order by login_time) as 第五次登陆日期from user_login

在 lead 函数里，为何偏移行数的参数设置为 4 而不是 5 呢，这是因为求解的是连续登录 5 天的用户，包括当前行在内一共是 5 行，所以应该向下偏移 4 行。 第二步，用 datediff 函数计算 (第五次登陆日期 - 日期) + 1 是否等于 5，等于 5 证明用户是连续 5 天登录的，为空值或者大于 5 都不是 5 天连续登陆的用户。

第三步，用 where 设定条件，差值等于 5 筛选连续登录的用户。

with b as ( select user_id, a.日期, a.第五次登陆日期, datediff(a.第五次登录日期, a.日期) + 1 as 相差天数 from ( select distinct user_id, date(login_time) as 日期, lead(date(login_time),4) over (partition by user_id order by login_time) as 第五次登陆日期 from user_login ) as a)select distinct b.user_idfrom bwhere b.'相差天数' = 5

用 lead 函数求解连续登录的问题还有一个好处就是当表中的数据不在同一个月份时也可以完美的解决，不用再考虑月份带来的影响。