MySQL 8.0 窗口函数(window functions)

一、窗口函数简介

窗口函数(window functions)是数据库的标准功能之一，主流的数据库比如Oracle，PostgreSQL都支持窗口函数功能，MySQL 直到 8.0 版本才开始支持窗口函数。

窗口函数，简单来说就是对于一个查询SQL，将其结果集按指定的规则进行分区，每个分区可以看作是一个窗口，分区内的每一行，根据其所属分区内的行数据进行函数计算，获取计算结果，作为该行的窗口函数结果值。

窗口函数与group聚合查询类似，都是对一组（分区）记录进行计算，区别在于group对一组记录计算后返回一条记录作为结果，而窗口函数对一组记录计算后，这组记录中每条数据都会对应一个结果。

来看一个例子：

mysql> SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer | 1500 | 7535 | 1610 |
| 2000 | Finland | Phone | 100 | 7535 | 1610 |
| 2001 | Finland | Phone | 10 | 7535 | 1610 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Computer | 1200 | 7535 | 1350 |
| 2000 | USA | Calculator | 75 | 7535 | 4575 |
| 2000 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | Calculator | 50 | 7535 | 4575 |
| 2001 | USA | Computer | 1200 | 7535 | 4575 |
| 2001 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | TV | 100 | 7535 | 4575 |
| 2001 | USA | TV | 150 | 7535 | 4575 |
+------+---------+------------+--------+--------------+----------------+


查询SQL通过 OVER 子句来标记窗口，OVER 子句中的内容可以指定窗口分区的方法。

上述例子中，第一个 OVER 子句中的参数为空，则将整个查询结果集作为一个单一的分区，分区内的每条记录的窗口函数计算结果为整个分区内的字段值求和。

第二个 OVER子 句使用 country 字段作为参数，对查询结果集进行分区，按照 country 分区，分区内求和，作为该分区内每条记录的窗口函数计算结果。

二、窗口函数语法

over_clause:
{OVER (window_spec) | OVER window_name}

order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...


• 如果 OVER() 子句里面的内容为空，则窗口大小为整个查询的结果集，使用结果集中的所有记录计算结果。
• 如果 OVER() 子句里面的内容不为空，则使用里面指定的窗口分区规则、排序规则对分区内的记录进行分区和排序。
• partition_clause，指定如何对查询结果集进行分区，窗口函数基于分区内的记录进行计算，如果没有指定 partition_clause 子句，则对整个查询结果集数据进行计算。SQL标准要求 PARTITION BY 后面只能跟字段名，MySQL扩展支持表达式，比如ts字段为TIMESTAMP类型，可以这样使用PARTITION BY HOUR(ts)。
• order_clause，可选，后面可以跟 ASC 或者 DESC 指定排序方向。order by 子句对分区内的记录进行排序。
• frame_clause，表示当前分区的一个子集，frame_clause 指定了定义当前分区子集的方法。

三、命名的窗口

窗口函数的窗口能够使用指定的名称来定义，然后可以在 OVER 子句中使用指定的名称来引用窗口。

举个例子，未使用命名窗口的窗口函数如下：

SELECT
val,
ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
RANK() OVER (ORDER BY val) AS 'rank',
DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;


使用命名窗口改写，如下：

SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);


使用命名的窗口，SQL更加清晰、简洁，更容易测试窗口的定义，如果要修改窗口的定义，只需要修改WINDOW子句，而不必每个OVER子句都修改。

四、非聚合类窗口函数

MySQL支持的非聚合类窗口函数如下：

• CUME_DIST()
• DENSE_RANK()
• FIRST_VALUE() LAG()
• LAST_VALUE()
• LEAD() NTH_VALUE()
• NTILE()
• PERCENT_RANK()
• RANK() ，
• ROW_NUMBER()

举个例子，通过ROW_NUMBER()函数，计算每条记录在其所在分区内的行号。默认条件下，分区内的记录是未排序的，因此行号也是不确定的，可以使用 order by 子句对窗口函数定义的分区内的数据集进行排序。下面例子 row_num1 未排序，row_num2 排序。

mysql> SELECT
year, country, product, profit,
ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2
FROM sales;
+------+---------+------------+--------+----------+----------+
| year | country | product | profit | row_num1 | row_num2 |
+------+---------+------------+--------+----------+----------+
| 2000 | Finland | Computer | 1500 | 2 | 1 |
| 2000 | Finland | Phone | 100 | 1 | 2 |
| 2001 | Finland | Phone | 10 | 3 | 3 |
| 2000 | India | Calculator | 75 | 2 | 1 |
| 2000 | India | Calculator | 75 | 3 | 2 |
| 2000 | India | Computer | 1200 | 1 | 3 |
| 2000 | USA | Calculator | 75 | 5 | 1 |
| 2000 | USA | Computer | 1500 | 4 | 2 |
| 2001 | USA | Calculator | 50 | 2 | 3 |
| 2001 | USA | Computer | 1500 | 3 | 4 |
| 2001 | USA | Computer | 1200 | 7 | 5 |
| 2001 | USA | TV | 150 | 1 | 6 |
| 2001 | USA | TV | 100 | 6 | 7 |
+------+---------+------------+--------+----------+----------+


另外一个例子，CUME_DIST() 函数，计算分区内，小于等于当前值的记录数占分区内总记录数的比值，取值范围为0~1。示例如下：

mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
CUME_DIST() OVER w AS 'cume_dist',
PERCENT_RANK() OVER w AS 'percent_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+--------------------+--------------+
| val | row_number | cume_dist | percent_rank |
+------+------------+--------------------+--------------+
| 1 | 1 | 0.2222222222222222 | 0 |
| 1 | 2 | 0.2222222222222222 | 0 |
| 2 | 3 | 0.3333333333333333 | 0.25 |
| 3 | 4 | 0.6666666666666666 | 0.375 |
| 3 | 5 | 0.6666666666666666 | 0.375 |
| 3 | 6 | 0.6666666666666666 | 0.375 |
| 4 | 7 | 0.8888888888888888 | 0.75 |
| 4 | 8 | 0.8888888888888888 | 0.75 |
| 5 | 9 | 1 | 1 |
+------+------------+--------------------+--------------+


五、聚合类窗口函数

窗口函数的 OVER 子句可以和许多聚合函数一起使用，这些聚合函数加上 OVER 子句，就是窗口函数，如果不加 OVER 子句，就是普通的聚合函数。支持窗口函数功能的聚合函数如下：

• AVG()
• BIT_AND()
• BIT_OR()
• BIT_XOR()
• COUNT()
• JSON_ARRAYAGG()
• JSON_OBJECTAGG()
• MAX()
• MIN()
• STDDEV_POP(), STDDEV(), STD()
• STDDEV_SAMP()
• SUM()
• VAR_POP(), VARIANCE()
• VAR_SAMP()

本文开头的第一个例子，就是使用SUM()聚合函数做窗口计算的，可以翻回去看一下。

六、窗口函数的使用限制

SQL标准加在窗口函数上的一个限制是不能用于update和delete语句来更新行记录。在update和delete语句的子查询中使用窗口函数是允许的。

• 不支持聚合窗口函数的 DISTINCT 语法
• 不支持嵌套的窗口函数
• 不支持依赖于当前行值的动态帧结束点
• 指定 GROUPS 帧单元能够被解析，但是会产生错误，仅支持 ROWS 和 RANGE
• 指定帧的 EXCLUDE 子句能够被解析，但是会产生错误
• IGNORE NULLS 子句能够被解析，但是会产生错误，目前只支持 RESPECT NULLS 子句
• FROM LAST 子句能够被解析，但是会产生错误，目前只支持 FROM FIRST 子句

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html