一般的商业数据库（其实也就是DB2，Oracle，SQL Server）都具备窗口函数这个功能，只不过名称不同，我比较熟悉的Oracle叫做分析函数，DB2好像叫做OLAP函数？

MySQL以前是不支持这个的，很多时候要实现一些功能就会非常麻烦，不过在8.0加入了这个功能，而且还比其他数据库多了一些有趣的支持模式。

以下内容部分参考：
https://zhuanlan.zhihu.com/p/49082967

窗口的概念

什么叫窗口?

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数，有的函数，随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

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

聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
聚合函数也可以用在窗口函数中，这个后面会举例说明。

窗口函数的格式：

函数名（[expr]） over子句

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

1. window_name：给窗口指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读（主要是方便），例如：

SELECT
    rank ( ) over w1 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC )

很明显，from table后面加了一个关键字WINDOW，后面跟了一个子句，这样在select中就可以使用这个w1作为窗口了。

2. partition子句：窗口按照那些字段进行分组，窗口函数在不同的分组上分别执行。比如上面例子中，就是用Company字段分组。（跟group by一个意思，但是这里用PARTITION BY）

3. order by子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用，也可以单独使用。

4. frame子句：frame是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用，这是个很有意思的特性，不同于Oracle，例如：

SELECT
    avg( Salary ) over w1,
    Company,
    Salary 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC rows BETWEEN 1 preceding AND unbounded following )

这条语句的意思是，以Company分组窗口，Salary排序，从当前行的前一行到该组的最后一行，取平均Salary（工资）。
则有如下图：

frame子句

注意Microsoft中的平均Salary，第一个行是60000，他没有前一行，最后一行是50000（因为是根据Company分组了的），那么平均Salary就是
(60000+60000+55000+50000)/4 = 56250。
而第二行也是60000，他的上一行是60000，到最后一行求平均值就是：
(60000+60000+55000+50000)/4 = 56250（不要混淆）
第三行是55000，他的上一行是60000，到最后一行，平均值就是：
(660000+55000+50000)/3 = 55000.

这种窗口范围的限定，因为是随着当前查询行的变化而变化的，就叫做动态窗口，对于动态窗口的范围指定，有两种方式，基于行和基于范围，具体区别如下：

• 基于行：通常使用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。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。

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

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

常用函数

窗口函数的核心还是在over前面的函数上，除了常用的聚合函数之外，还可以用一些特定的窗口函数，下面举例：

序号函数--row_number()/rank()/dense_rank()

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

SELECT
    row_number ( ) over w1,
    Company,
    Salary 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC rows BETWEEN 1 preceding AND unbounded following )

row_number

可以看到，根据Company进行分组之后，按照Salary倒序排列，给了行号，依次是1，2，3，4，如果有相同的，那么随机处理。

rank和dense_rank就是用来处理有相同情况的：

SELECT
    row_number ( ) over w1,
    rank ( ) over w1,
    dense_rank ( ) over w1,
    Company,
    Salary 
FROM
    employee window w1 AS ( PARTITION BY Company ORDER BY Salary DESC rows BETWEEN 1 preceding AND unbounded following )

image.png

还是关注Microsoft，可以看到，rank是把相同Salary的取了同样的排名，再下一位的，按照实际行号直接取排名（于是变成了3），而dense_rank则是依次顺延，下一个是第二高的Salary，那么就是2.

dense_rank，密集排序，可以理解为需要更密集一点展示排名，所以就不按照行号来排。

分布函数--percent_rank()/cume_dist()

percent_rank()

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

例子：(这里没有使用窗口的别名，感受一下正常使用这个函数的格式)

SELECT
    percent_rank ( ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

结果有：

percent_rank

很明显，按照倒序排列，统计学中有个分位数的概念（有序数列中的百分之多少的数）。
总共5行，第2行就是(2-1)/(5-1) = 0.25，正好是25分位数，以此类推。

cume_dist()

用途：分组内小于等于当前rank值的行数/分组内总行数。

SELECT
    cume_dist ( ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

cume_dist

注意是按照rank值来求值，而不是实际大小。

以第二行为例，rank值小于等于他的有第一行和第二行，总行数是5，那么有：2/5=0.4，所以就是0.4.

前后函数--lead(字段，n)/lag(字段，n)

这个是带参数的，要注意。
用途：分区中位于当前行前n行（lead）/后n行(lag)的记录值

SELECT
    lag ( salary, 1 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

lag

从图中可以看到，lag取的是当前行的上一个行的对应字段的值，当然我们也可以对lag中的字段进行计算（非聚合）。
lead就是当前行的下一个行的值，虽然有点难理解，但是只能记住，如下图：

SELECT
    lead ( salary - 10000, 1 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

lead

头尾函数--first_value (expr)/last_value(expr)

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

很好理解，就是求当前分组的第一个值和最后一个值（这个有order by决定。），注意求的是对应的expr（字段名）的值，而不是其他的值。

SELECT
    first_value ( id ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

first_value

意思就是，当前分组中，按照Salary倒序排列，第一行是70000，这行对应的id是12.

SELECT
    last_value ( Salary ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

last_value

而last中需要注意，指的是最后一行，是当前分组的最后一行，所以会出现如上的情况。

所以不能用于最大值最小值判断。

nth_value(expr,n)/ntile(n）

nth_value(expr,n)

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

SELECT
    nth_value ( Salary,3 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

nth_value

第三个是60000的薪水，那么就显示60000，注意是分组内的每一行都有值。

ntile(n)

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

SELECT
    ntile ( 3 ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

ntile

数据分桶，根据先后顺序，把行分为n个桶，优先满足前面的行，图中要分为3个桶，因此最后一桶只有1行。
桶号就是1，2，3……

聚合函数作为窗口函数

用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。
这就很好理解了，以avg为例：

SELECT
    avg( Salary ) over ( PARTITION BY Company ORDER BY Salary DESC ),
    Company,
    Salary 
FROM
    employee 
WHERE
    Company = 'Microsoft'

avg( Salary )

按照分组的每一行，求累计的平均值。

通过这个，应该能明白窗口的含义。（根据over子句分组之后，挨个行进行select，并执行over前面的函数，因为over子句已经把记录变成了符合条件的一些行集，所以select的方式就改变了。）

窗口函数还是非常常用的，简单总结一下，备忘。

窗口函数