MySQL 中的窗口函数

提示：这篇文字适合在你已经掌握了 MySQL 的基础操作后阅读。

MySQL 自 8.0 版本开始添加了窗口函数 (window function) 功能。所谓窗口函数，是指对查询的每一行，利用和这一行相关的一些行构成一个窗口，对这个窗口进行计算，得到一个值作为结果。窗口函数和聚合函数 (aggregate function ) 有点类似，但窗口函数的功能往往要比聚合函数更强。为了方便说明，这里用一个小的数据库来做演示。这是uCourse小程序2020年3月2日和3日五个页面的访问人数和平均停留时长数据。

CREATE TABLE page(
   date    DATE  NOT NULL
  ,name    VARCHAR(4) NOT NULL
  ,num_psn INTEGER  NOT NULL
  ,time    INTEGER  NOT NULL
  ,PRIMARY KEY(date,name)
);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-02','首页',1591,3015);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-02','个人首页',756,40);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-02','院校首页',479,12);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-02','课表首页',1507,1221);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-02','设置首页',136,1);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-03','首页',876,1678);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-03','个人首页',176,18);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-03','院校首页',144,8);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-03','课表首页',814,673);
INSERT INTO page(date,name,num_psn,time) VALUES ('2020-03-03','设置首页',17,0);

SELECT * FROM page;
+------------+--------------+---------+------+
 date        name          num_psn  time 
+------------+--------------+---------+------+
 2020-03-02  首页             1591  3015 
 2020-03-02  个人首页          756    40 
 2020-03-02  院校首页          479    12 
 2020-03-02  课表首页         1507  1221 
 2020-03-02  设置首页          136     1 
 2020-03-03  首页              876  1678 
 2020-03-03  个人首页          176    18 
 2020-03-03  院校首页          144     8 
 2020-03-03  课表首页          814   673 
 2020-03-03  设置首页           17     0 
+------------+--------------+---------+------+
10 rows in set (0.001 sec)

引子

对于聚合函数来说，它有一个无法打破的禁锢：你不能把聚合后的数据和聚合前的数据混在一张表格里。比如，你不能把求和后的值和求和前的值放在同一张表里。如果你想对这张表中所有页面的访问人数进行求和，你得这么写：

SELECT 
    SUM(num_psn) AS sum_psn
FROM 
    page;

+---------+
 sum_psn 
+---------+
    6496 
+---------+
1 row in set (0.001 sec)

但是窗口函数打破了这个禁锢。请看这个：

SELECT 
    date,
    name,
    num_psn,
    SUM(num_psn) OVER () AS sum_psn
FROM 
    page;

+------------+--------------+---------+---------+
 date        name          num_psn  sum_psn 
+------------+--------------+---------+---------+
 2020-03-03  设置首页           17     6496 
 2020-03-02  设置首页          136     6496 
 2020-03-03  院校首页          144     6496 
 2020-03-03  个人首页          176     6496 
 2020-03-02  院校首页          479     6496 
 2020-03-02  个人首页          756     6496 
 2020-03-03  课表首页          814     6496 
 2020-03-03  首页              876     6496 
 2020-03-02  课表首页         1507     6496 
 2020-03-02  首页             1591     6496 
+------------+--------------+---------+---------+
10 rows in set (0.001 sec)

这对于习惯了聚合函数的我们来说未免有些神奇。所以停下来，看一下这个最简单的窗口函数实例。每个窗口函数必然有一个 OVER 关键字。OVER 后面的括号中指出窗口的范围，OVER 前面指出对窗口进行什么运算。这里的运算可以是我们熟悉的聚合运算，也有一些其他的运算。在上面的例子中，窗口的范围写了()，意思是默认的整张表格；对整张表格进行的运算是对val列进行求和，其中SUM函数就是我们熟悉的聚合函数。最后，其中AS关键字的意思一如既往，即给查询结果的那一列重命名；其实AS在此处可以省略，但为可读性考虑，这篇教程将保留它。总之，那个窗口函数语句的意思是：对于查询的每一行，创建一个范围是“整张表格”的窗口，对窗口内val列进行求和，求和结果作为查询结果的val_sum列。确保你理解了上面这个例子，后面的各种花样都是对这个例子的变形。

窗口内排序

你可以对选定的窗口内的数据进行排序。你可能会觉得，如果我们的窗口函数用的是我们常用的聚合函数，排序并没什么用，因为不管数据是什么顺序，求和、均值、计数等等运算的结果是一样的。但是，事实上，数据进行排序之后，窗口的范围就发生了改变，从原来默认的“整个表格”变成了“排序后的第一行到当前行（包含并列行）”。因此，我们可以这样求“前缀和”：

SELECT 
    date,
    name, 
    num_psn,
    SUM(num_psn) OVER (ORDER BY num_psn) AS running_sum
FROM 
    page

+------------+--------------+---------+-------------+
 date        name          num_psn  running_sum 
+------------+--------------+---------+-------------+
 2020-03-03  设置首页           17           17 
 2020-03-02  设置首页          136          153 
 2020-03-03  院校首页          144          297 
 2020-03-03  个人首页          176          473 
 2020-03-02  院校首页          479          952 
 2020-03-02  个人首页          756         1708 
 2020-03-03  课表首页          814         2522 
 2020-03-03  首页              876         3398 
 2020-03-02  课表首页         1507         4905 
 2020-03-02  首页             1591         6496 
+------------+--------------+---------+-------------+
10 rows in set (0.001 sec)

这个查询语句的意思是明确的：对表格的每一行，建立一个窗口，窗口内数据按日期升序排列，且窗口涵盖的范围是所有日期小于等于自己的行，对窗口内num_psn列进行求和作为当前行的running_sum列。在此，我们再介绍两个常用的窗口函数：RANK() 和 ROW_NUMBER()。顾名思义，RANK 返回当前行在窗口中的排名，ROW_NUMBER返回当前行在窗口中的行号。两个函数的差别在于对并列的处理：RANK对于并列行，排名一致，后面的行的排名则有一个间隙；ROW_NUMBER则是连续排下来的。想知道某一天所有页面访问人数的排名：

SELECT 
    date,
    name,
    num_psn,
    RANK() OVER (ORDER BY num_psn DESC) AS num_psn_rank 
FROM page
WHERE date = '2020-03-02'

+------------+--------------+---------+--------------+
 date        name          num_psn  num_psn_rank 
+------------+--------------+---------+--------------+
 2020-03-02  首页             1591             1 
 2020-03-02  课表首页         1507             2 
 2020-03-02  个人首页          756             3 
 2020-03-02  院校首页          479             4 
 2020-03-02  设置首页          136             5 
+------------+--------------+---------+--------------+
5 rows in set (0.001 sec)

对于page表中3月2日的每一行，建立一个窗口，窗口内数据按访问人数降序排列，且窗口的范围是所有人数大于等于自己的行，找出当前行在现在窗口中的排名，重命名为num_psn_rank列。当然，因为在这里，人数小于自己的行对自己的排名和行号都不影响，所以窗口范围限制的效果并不能显现出来。不过例外是LEAD和LAG函数，它们并不受到排序后窗口范围的限制。它们的含义是找出在当前行在窗口中前/后第若干行中某一列的值。

窗口内分组

在上面的例子中，我们只限定了某一天。如果想知道每一天中，当天的每个页面的访问人数的排名呢？你会觉察到这个问题和我们的GROUP BY有点相似。在这里我们用PARTITION BY关键字。

SELECT 
    date,
    name,
    num_psn,
    RANK() OVER (PARTITION BY date ORDER BY num_psn DESC) AS num_psn_rank
FROM page

+------------+--------------+---------+--------------+
 date        name          num_psn  num_psn_rank 
+------------+--------------+---------+--------------+
 2020-03-02  首页             1591             1 
 2020-03-02  课表首页         1507             2 
 2020-03-02  个人首页          756             3 
 2020-03-02  院校首页          479             4 
 2020-03-02  设置首页          136             5 
 2020-03-03  首页              876             1 
 2020-03-03  课表首页          814             2 
 2020-03-03  个人首页          176             3 
 2020-03-03  院校首页          144             4 
 2020-03-03  设置首页           17             5 
+------------+--------------+---------+--------------+
10 rows in set (0.001 sec)

这个语句的意思是：对于每一行，建立一个窗口，首先分组筛选保证窗口中所有行的date值和当前行一致，然后窗口内数据按访问人数降序排列，窗口的范围是所有排序后顺序在当前行之前或和当前行并列的行，最后求出当前行在窗口中的排序。那么这样的话，对于不同的日期，就有一套自己的页面排名。不过这两天这五个页面的排名是一样的，可见这两天（其实大多数日子都是这样）uCourse的用户大多数还是从首页打开小程序，然后使用课表功能。

窗口范围

你还可以自己限定窗口的范围。如果想求移动平均值，即当前行和上下两行的平均值，那么就要把这三行限定成一个窗口。这就需要自定义的窗口范围限制。自定义窗口范围限制有两个关键字可用，一是ROWS，二是RANGE。我们先看一下ROWS怎么用。下面查询语句就能查询移动平均值：

SELECT 
    date,
    name,
    num_psn,
    AVG(num_psn) OVER (PARTITION BY date ORDER BY num_psn DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS rolling_avg
FROM page

+------------+--------------+---------+-------------+
 date        name          num_psn  rolling_avg 
+------------+--------------+---------+-------------+
 2020-03-02  首页             1591    1549.0000 
 2020-03-02  课表首页         1507    1284.6667 
 2020-03-02  个人首页          756     914.0000 
 2020-03-02  院校首页          479     457.0000 
 2020-03-02  设置首页          136     307.5000 
+------------+--------------+---------+-------------+
 2020-03-03  首页              876     845.0000 
 2020-03-03  课表首页          814     622.0000 
 2020-03-03  个人首页          176     378.0000 
 2020-03-03  院校首页          144     112.3333 
 2020-03-03  设置首页           17      80.5000 
+------------+--------------+---------+-------------+
10 rows in set (0.001 sec)

两个日期之间的分割是我手动加的。这个窗口，首先是把date值和当前行一样的行筛选出来，排序，然后把窗口限定为“当前行前面一行”和“当前行后面一行”之间。有一个问题：如果当前行是第一行呢？那么“前面一行”被省略，因此这个窗口只有两行，即当前行加上当前行后面一行。如果是最后一行类似。例如，你可以注意到，上面的那个数据表格的第一行，移动平均值是1549，这个值是1591和1507的均值。你注意到ROWS的语法是

1	ROWS BETWEEN line1 AND line2

这里，每一行可以有以下几种表示方法：

X PRECEDING：当前行上面X行
X FOLLOWING：当前行下面X行
CURRENT ROW：当前行
UNBOUNDED PRECEDING：当前窗口第一行
UNBOUNDED FOLLOWING：当前窗口最后一行

那么，这样就不难理解ROWS和BETWEEN AND连在一起的意思：

ROWS BETWEEN 5 PRECEDING AND CURRENT ROW：所有满足当前行行号-5<=行号<=当前行行号的行
ROWS BETWEEN 5 FOLLOWING AND 10 FOLLOWING：所有满足当前行行号+5<=行号<=当前行行号+10的行
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：所有行号<=当前行的行

你现在可能快不认识“行号”两个字了。 RANGE和ROWS的区别在于对X PRECEDING, X FOLLOWING, CURRENT ROW的解读不同。RANGE的处理是这样：首先，它要求你必须有一个ORDER BY，并且只有一个排序键；然后，它把ROWS规则中的所有“行号”改成这个键在行内的“值”。比如RANGE BETWEEN 5 PRECEDING AND CURRENT ROW。假如当前行的排序键的值是100，那么窗口涵盖的就是所有值在区间[95, 100]内的行。比如RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING。假如当前行的排序键的值是100，那么窗口涵盖的就是所有值在区间[105, 110]内的行。比如RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：所有值<=当前行的行。请注意，这里和ROWS的区别在于对并列行的处理：ROWS对并列行是分开处理，RANGE是一定同时涵盖所有并列行。（有一些网站说RANGE和ROWS的区别就在于对并列行的处理，说的就是这种情况，但是总体而言这种说法是以偏概全的。）再次注意，如果你不自定义窗口的范围，那么如果有ORDER BY，这个范围就是默认值。 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 和 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING就是一个意思了，全选。这也就是，如果你没有自定义窗口范围，也没有使用ORDER BY语句的默认范围。最后一件事，你要注意到RANGE中X PRECEDING和X FOLLOWING中的X其实是要直接和行内的排序键的值进行加减运算的。也就是说，如果你写了RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING，那么行内的排序键的值的类型一定是整型，因为需要进行+5和+10的运算；如果行内的排序键的值是浮点数，那就要写RANGE BETWEEN 5.0 FOLLOWING AND 10.0 FOLLOWING；如果行内的排序键的值是日期格式，那么就要用INTERVAL来表示日期的加减，比如RANGE BETWEEN INTERVAL 5 DAY FOLLOWING AND INTERVAL 10 DAY FOLLOWING（注意MariaDB是不支持RANGE使用日期的）。还有一个问题是RANGE如果遇到当前行是NULL怎么办。根据运算法则，不管对NULL进行什么加减运算，结果都是NULL。这能有助于理解它的行为。但这里不具体展开了。官方文档有详细的例子。我们来（不加解释地）举几个例子。这里我们用一个非常简单的数据库：

MariaDB [test]> SELECT * FROM t;
+------+
 val  
+------+
    1 
    2 
    9 
  100 
  123 
  100 
+------+
6 rows in set (0.000 sec)

SELECT
    val,
    SUM(val) OVER () as sum
FROM t;

+------+------+
 val   sum  
+------+------+
    1   335 
    2   335 
    9   335 
  100   335 
  100   335 
  123   335 
+------+------+
6 rows in set (0.001 sec)

SELECT 
    val, 
    SUM(val) OVER (ORDER BY val) as running_sum_default
FROM t;

+------+---------------------+
 val   running_sum_default 
+------+---------------------+
    1                    1 
    2                    3 
    9                   12 
  100                  212 
  100                  212 
  123                  335 
+------+---------------------+
6 rows in set (0.001 sec)

SELECT 
    val, 
    SUM(val) OVER (ORDER BY val RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum_range
FROM t;

+------+-------------------+
 val   running_sum_range 
+------+-------------------+
    1                  1 
    2                  3 
    9                 12 
  100                212 
  100                212 
  123                335 
+------+-------------------+
6 rows in set (0.001 sec)

SELECT 
    val, 
    SUM(val) OVER (ORDER BY val ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum_rows
FROM t;

+------+------------------+
 val   running_sum_rows 
+------+------------------+
    1                 1 
    2                 3 
    9                12 
  100               112 
  100               212 
  123               335 
+------+------------------+
6 rows in set (0.001 sec)

SELECT 
    val, 
    SUM(val) OVER (ORDER BY val ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS res
FROM t;

+------+------+
 val   res  
+------+------+
    1     1 
    2     3 
    9    12 
  100   111 
  100   209 
  123   323 
+------+------+
6 rows in set (0.001 sec)

SELECT 
    val, 
    SUM(val) OVER (ORDER BY val RANGE BETWEEN 7 PRECEDING AND CURRENT ROW) AS res
FROM t;

+------+------+
 val   res  
+------+------+
    1     1 
    2     3 
    9    11 
  100   200 
  100   200 
  123   123 
+------+------+
6 rows in set (0.001 sec)

SELECT 
    val, 
    COUNT(val) OVER (ORDER BY val RANGE BETWEEN 100 PRECEDING AND CURRENT ROW) AS res
FROM t;

+------+-----+
 val   res 
+------+-----+
    1    1 
    2    2 
    9    3 
  100    5 
  100    5 
  123    3 
+------+-----+
6 rows in set (0.001 sec)

显而易见，一切都是我们所说的那样。

命名窗口&常用的窗口函数

如果你要重复使用一个窗口多次，以便用于不同的窗口函数上，你可以命名一个窗口，使用WINDOW AS语句，其位置是HAVING和ORDER BY之间。下面这个例子将展示命名窗口和一些常用的窗口函数。窗口函数的具体描述请参考官方文档。

SELECT 
    val, 
    COUNT(val) OVER w AS cnt,
    SUM(val) OVER w AS sum,
    LAG(val, 1) OVER w AS lag,
    LEAD(val, 1) OVER w AS lead,
    NTH_VALUE(val, 2) OVER w AS snd
FROM t
WINDOW w AS (ORDER BY val RANGE BETWEEN 100 PRECEDING AND CURRENT ROW);

+------+-----+------+------+------+------+
 val   cnt  sum   lag   lead  snd  
+------+-----+------+------+------+------+
    1    1     1  NULL     2  NULL 
    2    2     3     1     9     2 
    9    3    12     2   100     2 
  100    5   212     9   100     2 
  100    5   212   100   123     2 
  123    3   323   100  NULL   100 
+------+-----+------+------+------+------+
6 rows in set (0.001 sec)

除此之外，还有

NTILE
ROW_NUMBER
RANK
PERCENT_RANK

等。

结语

这文章写的我累死了。不想写结语了。总之就是博大精深就对了。