Mysql窗口函数

参考：

一、mysql窗口函数简介

MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库和部分开源数据库中早已支持，有的也叫分析函数。

窗口：记录的集合
窗口函数：在满足某种条件的记录集合上执行的特殊函数 要和聚合函数进行区分
分类：

静态窗口函数：函数随着记录不同，窗口大小都是固定的
动态窗口函数：不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
聚合函数也可以用于窗口函数中

按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

序号函数：row_number() / rank() / dense_rank()
分布函数：percent_rank() / cume_dist()
前后函数：lag() / lead()
头尾函数：first_val() / last_val()
其他函数：nth_value() / nfile()

原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP　BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。它执行时GROUP　BY的聚合过程已经完成了，所以不会再产生数据聚合。

一个窗口函数的例子

select user_id,avg(diff)
from
(
    select user_id,lead(log_time)over(partition user_id order by log_time) - log_time as diff
    from user_log
)t
where datediff(now(),t.log_time)<=30
group by user_id

以上代码是得到30天之内登陆的用户的平均时间间隔
lead(log_time)over(partition user_id order by log_time) 是将log_time 按每个id分组按登陆时间排序前置一项这样就可以得到每个用户每次登陆与前一次的差值

二、窗口函数的基本用法：

函数名（[expr]） over子句

其中，over是关键字，用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下四种语法来设置窗口：

window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读。上面例子中如果指定一个别名w，则改写如下：

select user_id,avg(diff)
from
(
    select user_id,lead(log_time) over w - log_time as diff
    from user_log
    WINDOW w AS(partition user_id order by log_time)
)t
where datediff(now(),t.log_time)<=30
group by user_id

partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上，按照order by的顺序分别生成从1开始的顺序编号。
order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。上例中二者同时使用，如果没有partition子句，则会按照所有用户的登陆时间排序来生成序号。
frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口：

从结果可以看出，order_id为5订单属于边界值，没有前一行，因此平均订单金额为(900+800)/2=850；order_id为4的订单前后都有订单，所以平均订单金额为（900+800+300）/3=666.6667，以此类推就可以得到一个基于滑动窗口的动态平均订单值。此例中，窗口函数用到了传统的聚合函数avg()，用来计算动态的平均值。

基于行
通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
UNBOUNDED PRECEDING 边界是分区中的第一行
UNBOUNDED FOLLOWING 边界是分区中的最后一行
expr PRECEDING 边界是当前行减去expr的值
expr FOLLOWING 边界是当前行加上expr的值

例如：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。

rows  UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。

rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写。

上图的例子就是以上第一行的情况

基于范围
和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。

有的函数不管有没有frame子句，它的窗口都是固定的，也就是前面介绍的静态窗口，这些函数包括如下：

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

三、序号函数

row_number() / rank() / dense_rank()。

用途：显示分区中的当前行号
使用场景：希望查询每个用户订单金额最高的前三个订单

ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序，跳过重复序号——1、1、3
DENSE_RANK():并列排序，不跳过重复序号——1、1、2

四、分布函数

说实话没想到有啥用

分布函数——percent_rank()/cume_dist()。

percent_rank():

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

cume_dist():

用途：分组内小于等于当前rank值的行数/分组内总行数，这个函数比percen_rank使用场景更多。
应用场景：大于等于当前订单金额的订单比例有多少。

五、前后函数:lag(expr,n),lead(expr,n)

这个在R与python中很常见

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
应用场景：查询前1名同学的成绩和当前同学成绩的差值或查询上一个订单距离当前订单的时间间隔。

六、头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)

用途：得到分区中的第一个/最后一个指定参数的值。
使用场景：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。

七、其他函数

其他函数——nth_value(expr,n)/nfile(n）。
其实与六类似

nth_value(expr,n)

用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。
应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。

nfile(n)

用途：将分区中的有序数据分为n个桶，记录桶号。
应用场景：将每个用户的订单按照订单金额分成3组。

八、聚合函数作为窗口函数

用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。
应用场景：每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少？