1. 应用场景

组内排名，如：

• 排名问题：每个部门按业绩排名
• topN问题：找出每个部门排名前N的员工

2. 简介

从版本8.0开始，MySQL支持窗口函数。

如何查看mysql版本

进入Mac下的终端，通过命令行mysql -uroot -p然后输入密码，可以查看mysql的版本号，如下图：

进入mysql交互环境后，也可以通过select version();语句查看版本号。

从聚合函数开始

学习窗口函数从聚合函数开始可能更容易理解。
聚合函数将来自多行的数据汇总到单个结果行中。

假设有一个sales表，employee：员工姓名，years：年份，sale：销售额，如下图所示：

• 以下SUM()函数返回记录年份中所有员工的总销售额

select sum(sale) from sales;

• GROUP BY子句可以将聚合函数应用于行的子集。例如，按年度计算总销售额：

select years, sum(sale) from sales group by years; 

在上面两个示例中，聚合函数都会减少查询返回的行数。

与带有GROUP BY子句的聚合函数一样，窗口函数也对行的子集进行操作，但它们不会减少查询返回的行数。

例如，以下查询返回每个员工的销售额，以及按年度计算的员工总销售额：

select years, employee, sale,
    sum(sale) over (partition by years) total_sales
from sales; 

在此示例中，SUM()函数用作窗口函数，函数对由OVER子句内容定义的一组行进行操作。SUM()应用函数的一组行称为窗口。

SUM()窗口函数计算的年度总销售额不是与GROUP BY子句一样，将每一年度汇总成一行，而是将求和结果展现在每一行。

3. 语法

调用窗口函数的一般语法如下：

window_func(expression) 
    OVER (
        partition by [col1,col2…]
        [order_definition]
        [frame_definition]
    ) 

在这个语法中：

• 首先，指定窗口函数名称，后跟表达式。
• 其次，指定OVER具有三个可能元素的子句：分区定义，顺序定义和帧定义。

OVER子句后面的开括号和右括号是强制性的，即使没有表达式，例如：

window_func(expression) OVER() 

3.1 window_func

window_func的位置，可以放以下两种函数：

1. 专用窗口函数，包括后面讲到的rank, dense_rank, row_number等专用窗口函数.

2. 聚合函数，如sum, avg, count, max, min等.

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

3.2 partition_clause

partition by部分用来指定开窗的列。

分区列的值相同的行被视为在同一个窗口内。同一窗口内最多包含1亿行数据（建议不超过500万行），否则运行时报错。该限制适用于所有内建窗口函数。

说明:例如group by UID，同1个UID对应的记录视为同一个窗口，即1个UID对应记录数最多包含1亿行数据（建议不超过500万行）。

可以在partition by子句中指定一个或多个列，用逗号分隔。

3.3 order_by_clause

order by用于指定数据在一个窗口内如何排序。

说明:当遇到相同的order by值的时候，排序结果不稳定。为了减少随机性，应当尽可能保持order by值的唯一性。

order_by_clause语法如下：

ORDER BY <expression> [ASC|DESC], [{,<expression>...}] 

ORDER BY与PARTITION BY子句类似，所有窗口函数都支持ORDER BY子句。但是，仅对ORDER BY顺序敏感的窗口函数使用该子句才有意义。

3.4 frame_clause

frame是当前分区的子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

frame_unit {<frame_start>|<frame_between>} 

对于滑动窗口的范围指定，有两种方式，基于行ROWS或基于范围RANGE，具体区别如下：

从结果可以看出，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	窗口范围是当前分区中所有行，等同于不写。

说明:

• 指定窗口范围时，BETWEEN x PRECEDING AND y,x、y必须为大于等于0的整数常量，限定范围0~10000，值为0时表示当前行。必须指定order by才可以用rows方式指定窗口范围。
• 并非所有的窗口函数都可以使用rows指定开窗方式。支持这种用法的窗口函数有AVG、COUNT、MAX、MIN、STDDEV和SUM。

基于范围：

和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。

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

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

一个例子

比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以设置如下frame子句来创建滑动窗口。

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

4. MySQL窗口函数列表

下表显示了MySQL中的窗口函数：

名称	描述
序号函数
ROW_NUMBER	为其分区中的每一行分配一个连续整数
DENSE_RANK	根据ORDER BY子句为其分区中的每一行分配一个排名。 它为具有相同值的行分配相同的排名。 如果两行或更多行具有相同的等级，则排序值序列中将没有间隙。
RANK	与DENSE_RANK（）函数类似，只是当两行或更多行具有相同的排名时，排序值序列中存在间隙。
分布函数
PERCENT_RANK	计算分区或结果集中行的百分位数
CUME_DIST	计算一组值中值的累积分布。
前后函数
LAG	返回分区中当前行之前的第N行的值。 如果不存在前一行，则返回NULL。
LEAD	返回分区中当前行之后的第N行的值。 如果不存在后续行，则返回NULL。
头尾函数
FIRST_VALUE	返回指定表达式相对于窗口框架中第一行的值。
LAST_VALUE	返回指定表达式相对于窗口框架中最后一行的值。
其他函数
NTH_VALUE	返回窗口框架第N行的参数值
NTILE	将每个窗口分区的行分配到指定数量的已排名组中。
聚合函数
SUM,AVG,COUNT,MAX,MIN
MEDIAN	中位数
STDDEV	总体标准差
STDDEV_SAMP	样本标准差

5. 窗口函数详解-结合案例

以订单表orders为例，来介绍每个函数的使用方法。表中各字段含义按顺序分别为order_id订单号、user_no用户id、amount订单金额、create_date订单创建日期。

CREATE TABLE orders(
    order_id INT NOT NULL,
	user_no INT NOT NULL,
	amount DECIMAL(14,2) NOT NULL,
	create_date DATETIME NOT NULL,	
    PRIMARY KEY(order_id)
);

INSERT INTO orders(order_id,user_no,amount,create_date)
VALUES(1,1,100,'2020/01/01 00:00:00'),
      (2,1,300,'2020-01-02 00:00:00'),
      (3,1,500,'2020-01-02 00:00:00'),
      (4,1,800,'2020-01-03 00:00:00'),
      (5,1,900,'2020-01-04 00:00:00'),
      (6,2,500,'2020-01-03 00:00:00'),
      (7,2,600,'2020-01-04 00:00:00'),
      (8,2,300,'2020-01-10 00:00:00'),
      (9,2,800,'2020-01-16 00:00:00'),
      (10,2,800,'2020-01-22 00:00:00');

select * from orders

5.1 序号函数

序号函数——row_number() / rank() / dense_rank()。

• 用途：显示分区中的当前行号

• 使用场景：查询每个用户订单金额最高的前三个订单

select * from
(
	select
    row_number()over(partition by user_no order by amount desc) as row_num,
    order_id,user_no,amount,create_date
    from orders
) t where row_num<=3;

此时可以使用ROW_NUMBER()函数按照用户进行分组并按照订单金额进行由大到小排序，最后查找每组中序号<=3的记录。

对于用户‘2’的订单，大家发现订单金额为800的有两条，序号随机排了1和2，但很多情况下二者应该是并列第一，而订单为600的序号则可能是第二名，也可能为第三名，这时候，row_number就不能满足需求，需要rank和dense_rank出场。

这两个函数和row_number()非常类似，只是在出现重复值时处理逻辑有所不同。

上面例子我们稍微改一下，需要查询不同用户的订单中，按照订单金额进行排序，显示出相应的排名序号，SQL中用row_number() / rank() / dense_rank()分别显示序号，我们看一下有什么差别：

select * from
(
	select
    row_number() over(partition by user_no order by amount desc) as row_num1,
    rank() over (partition by user_no order by amount desc) as row_num2,
    dense_rank() over (partition by user_no order by amount desc) as row_num3,
    order_id,user_no,amount,create_date
    from orders
) t where row_num1<=3;

上面红色方框内的部分显示了三个函数的区别，row_number()在amount都是800的两条记录上随机排序，但序号按照1、2递增，后面amount为600的的序号继续递增为3，中间不会产生序号间隙；rank()/dense_rank()则把amount为800的两条记录序号都设置为1，但后续amount为600的需要则分别设置为3（rank）和2（dense_rank）。即rank()会产生序号相同的记录，同时可能产生序号间隙；而dense_rank()也会产生序号相同的记录，但不会产生序号间隙。

5.2 分布函数

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

percent_rank()

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

• 应用场景：没想出来……感觉不太常用，看个例子吧↓

select * from
(
	select
    rank() over w as row_num,
    percent_rank() over w as percent,
    order_id,user_no,amount
    from orders
    window w as (partition by user_no order by amount desc)
) t;

从结果看出，percent列按照公式(rank - 1) / (rows - 1)带入rank值（row_num列）和rows值（user_no为‘1’和‘2’的值均为5）。

cume_dist()

• 用途：分组内大于等于当前rank值的行数/分组内总行数，这个函数比percent_rank使用场景更多。

• 应用场景：大于等于当前订单金额的订单比例有多少。

select * from
(
    select
    rank() over w as row_num,
    cume_dist() over w as percent,
    order_id,user_no,amount
    from orders
    window w as (partition by user_no order by amount desc)
) t;

5.3 前后函数

前后函数——lead(n)/lag(n)。

• 用途：分区中位于当前行前n行（lead）/后n行(lag)的记录值。

• 使用场景：查询上一个订单距离当前订单的时间间隔。

select order_id,user_no,amount,create_date,last_date,datediff(create_date,last_date) as diff
from(
    select order_id,user_no,amount,create_date,
    lag(create_date,1) over w as last_date
    from orders
    window w as (partition by user_no order by create_date)
) t;

内层SQL先通过lag函数得到上一次订单的日期，外层SQL再将本次订单和上次订单日期做差得到时间间隔diff。

5.4 头尾函数

头尾函数——first_val(expr)/last_val(expr)。

• 用途：得到分区中的第一个/最后一个指定参数的值。

• 使用场景：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。

select * from
(
    select
    order_id,user_no,amount,create_date,
    first_value(amount) over w as first_amount,
    last_value(amount) over w as last_amount
    from orders
    window w as (partition by user_no order by create_date)
) t;

结果和预期一致，比如order_id为4的记录，first_amount和last_amount分别记录了用户‘1’截止到时间2018-01-03 00:00:00为止，第一条订单金额100和最后一条订单金额800，注意这里是按时间排序的最早订单和最晚订单，并不是最小金额和最大金额订单。

5.5 其他函数

其他函数——nth_value(expr,n)/nfile(n）。

nth_value(expr,n)

• 用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。

• 应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。

select * from
(
    select
    order_id,user_no,amount,create_date,
    nth_value(amount,2) over w as second_amount,
    nth_value(amount,3) over w as third_amount
    from orders
    window w as (partition by user_no order by amount)
) t;

nfile(n)

• 用途：将分区中的有序数据分为n个桶，记录桶号。

• 应用场景：将每个用户的订单按照订单金额分成3组。

select * from
(
    select
    ntile(3) over w as nf,
    order_id,user_no,amount,create_date
    from orders
    window w as (partition by user_no order by amount)
) t;

此函数在数据分析中应用较多，比如由于数据量大，需要将数据平均分配到N个并行的进程分别计算，此时就可以用NFILE(N)对数据进行分组，由于记录数不一定被N整除，所以数据不一定完全平均，然后将不同桶号的数据再分配。

5.6 聚合函数作为窗口函数

• 用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。

• 应用场景：每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少？

select * from
(
    select
    order_id,user_no,amount,create_date,
    sum(amount) over w as sum1,
    avg(amount) over w as avg1,
    max(amount) over w as max1,
    min(amount) over w as min1,
    count(amount) over w as count1
    from orders
    window w as (partition by user_no order by order_id)
) t;

6. 总结

窗口函数语法

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

<窗口函数>的位置，可以放以下两种函数：
1） 专用窗口函数，比如rank, dense_rank, row_number等
2） 聚合函数，如sum, avg, count, max, min等

窗口函数的功能

1）同时具有分组（partition by）和排序（order by）的功能
2）不减少原表的行数，所以经常用来在每组内排名

注意事项

1） 窗口函数只能出现在select子句中
2） 窗口函数中不能嵌套使用窗口函数和聚合函数
3） 窗口函数不能和同级别的聚合函数一起使用

参考文章：
阿里云窗口函数
MySQL 窗口函数
MySQL 8.0窗口函数
通俗易懂的学会：SQL窗口函数