MySQL 8.0 支持的窗口函数

MySQL 8.0 窗口函数

前言

MySQL在8.0开始全面支持窗口函数提高利用SQL直接进行数据分析的能力，当初在工作几年后从Oracle转到MySQL最不习惯的就是再也不能在DB层直接进行各种复杂的统计SQL了，工作中只能在代码应用层进行中转统计结合定时JOB产出报表数据。

虽然现在实际工作中还没接触到这方面的应用，但既然官方已经支持而且也确实是很实用的功能，个人花时间去了解学习基础用法留作备用还是有必要的，本文是在学习 官方文档关于窗口函数的内容后翻译整理而来。

概念和语法

窗口函数是对查询结果集进行类似聚合的操作，但不同于聚合操作将分组数据聚合成一行，窗口行数是针对每一行生成对应的结果。

查询中的每个窗口操作都包含一个 OVER 子句来指明窗口函数执行时如何分组，由于窗口函数是出现在 SELECT 子句中的，按照标准SQL各种子句执行顺序(FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT/OFFSET )，窗口函数在 ORDER BY 和 LIMIT 之前执行， DISTINCT 也是在窗口函数结果返回后执行。

MySQL既支持常见的聚合函数（如 SUM, AVG, MAX, COUNT 等，详细内容不是本文重点，感兴趣的可以去看 官方文档中关于聚合函数的介绍），也提供一些很实用且只能作为窗口函数的非聚合函数，本文后面有详细介绍。

调用窗口函数的整体语法是 "window_func over_clause", OVER 子句又分为两种形式： "OVER (window_spec)" 和 "OVER window_name", 第二种引用在其它地方命名的窗口的使用形式在文中后续会单独再展开，这里先继续说最基础的第一种方式。

OVER (windows_spec) 的括号内部是由以下几个可选的部分组成：

• window_name:

  指定 WINDOW 子句定义的窗口别名，如果只包含窗口名不包含后续三个可选子句，那就等同于"OVER window_name"，否则就是在该窗口基础上进一步调整后的定义（详细示例可以看后面关于窗口命名的内容）。

• partition_clause

  PARTITION BY 子句指定了窗口进行分组的基准，可以是表的单个字段或者多个字段，甚至也可以是字段相关的函数调用、运算等表达式结果（标准的SQL规范中只允许纯粹的字段，应该是考虑到使用函数等表达式后无法利用索引？），如果不指定那就将整个结果集作为一个分组。

• order_clause

  OVER 内部的 ORDER BY 指定了分组内部的排序规则，非聚合函数作为窗口函数时几乎都要依赖固定的排序才有实际意义，如果不指定的话就是无序的，重复执行时可能就有不同结果。

• frame_clause

  窗口框架是能进一步将函数作用域从当前分组划分出更小粒度的子集，具体的语法逻辑也需要在本文后面单独展开细讲。

常见函数

在下面函数描述中， over_clause 代表OVER子句。部分函数允许通过 null_treatment 子句来指定如何处理NULL值（可选项，标出来只是因为它是SQL的标准规范，但是实际MySQL实现时只允许 RESPECT_NULLS，不指定时的默认策略也是这个，所以 null_treatment 子句可以忽略）。 expr 代表列名相关的表达式，可以是单独某列，也可以是多个列加减乘除的计算结果，也可以是用 IFNULL 等函数处理的列等。

• CUME_DIST() over_clause

  返回当前行在分组中的累积分布值，即小于或等于当前行值的数据在该窗口分组内的占比。

  数据示例见图一：分组总行数是9，前两行的累积分布相同就是2/9，第4~6行的累积分布都是6/9意味着有6行排名没有超过它们所在的行，很明显最后一行函数值就是1.
  

• LAG(expr [, N[, default]]) [null_treatment] over_clause

  返回分组中排在当前行前面的第N行的值，如果不存在就返回default值，N可以是大于等于0的整数（N=0就是返回该行自身的值），不指定时（从8.0.22版本开始必须指定不能为空了）视为1，即返回上一行的数据。

  数据示例见图二：没有指定N所以默认取的是前面一行的值，也没有指定 default 所以第一行该函数值返回了NULL，第二行返回的是第一行的val值。
  

• LEAD(expr [, N[, default]]) [null_treatment] over_clause

  返回分组中排在当前行后面的第N行的值，其它各参数选项含义与 LAG 相同。

• RANK() over_clause

  返回当前行在分组中的排名，重复行的排名值相同，但是下一个排名就从有多少行排在它前面开始，即排名值是有间隙跨度的。

  具体数据示例见图三：前两行数值相同所以排名都是1，第三行数值不同但前面有两行排在前面了所以排名值从3开始。
  

• DENSE_RANK() over_clause

  返回的也是分组中的排名，只是在遇到重复值时返回的也是重复排名值，下一个不同值的排名值会出现跨度，

  数据示例见图三：重复的行返回相同的排名值，且下一个排名仍然保持连续没有间隙。

• ROW_NUMBER() over_clause

  返回当前行在分组内的行号，从1递增到分组内的行数。 ORDER BY 会影响行的编号，如果没有 ORDER BY 那么行号就是不确定的。不管行的值是否相同都是返回不同的行号，这方面是与 RANK() 或 DENSE_RANK() 都不同的。

  数据示例见图三：所有行的编号从1开始依次递增，排名相同的行也是不同的行号。

• PERCENT_RANK() over_clause

  返回分组中排名小于当前行的行数占比，分母中去掉排名最高的那一行。返回值介于0和1代表当前行的相对排名，可以通过公式 (rank - 1) / (total_rows - 1) 去理解逻辑，其中rank等于 RANK() 函数返回值，total_rows代表分组内的总行数。

  数据示例见图一：第一行和最后一行固定为0和1，第二行排名与第一行相同所以也是返回值也是0，再以第4~6行为例，排名值都是4所以函数返回值就是3/8.

• FIRST_VALUE(expr) [null_treatment] over_clause

  返回分组范围中第一行的值，需要注意的是如果 OVER 子句中加了 frame 框架参数的话可能会影响"范围内第一行"的逻辑，如果没有进一步限定框架的话默认就是根据 OVER 子句中的 ORDER BY 排序后的首行数据。

  具体数据示例见图四：按subject分组并按time升序时，组内所有数据行对应 FIRST_VALUE() 函数返回的都是第一行的值。
  

• LAST_VALUE(expr) [null_treatment] over_clause

  返回窗口范围中末尾行的值，但是要注意的是有排序又不指定 frame 框架参数的情况下（默认框架范围其实是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，也就是从第一行到当前行结束）其实返回的就是当前行的值，这个逻辑很反直觉（在介绍窗口框架时会展开讲一下默认逻辑）。

  具体数据示例见图四：没弄明白窗口框架范围时下意识觉得应该就是各分组最后一行的值，前四行的返回值应该都是20才对，实际上默认框架结束于当前行，所以这个SQL中完全就等同于本行的字段值。

• NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause

  最灵活的取值函数，可以指定分组内第N行的值， from_first_last 在SQL规范中是可以选 FROM FIRST 或者 FROM LAST 的，但是MySQL只支持 FROM FIRST，建议在 ORDER BY 中用逆序来实现 FROM LAST ，所以和 null_treatment 一样直接忽略，语法就记 NTH_VALUE(expr, N) over_clause 。

  同样需要注意的是，返回值也是要看窗口范围定义逻辑的，如果 N 大于当前行对应的窗口范围总行数就会返回NULL。

  具体数据示例见图四：第一个分组中第一行 NTH_VALUE(val, 2) 函数返回值是NULL的原因就是截止到当前行为止行数还没到2，同理第二个分组中的前三行的 fourth 字段值也都是NULL。

• NTILE(N) over_clause

  将当前分组再分成N个小组（桶），返回划分后该行分配到的桶编号，N必须是正整数，返回值是1~N的整数。

  如果遇到没办法平均分配的情况，会在编号从小到大的桶中额外分配一行，直到剩下的桶能平分剩余的行数。即假设分组内的 partition_rows % N = r , 那么从1到r的编号桶中会多分配一行，从r+1开始的桶就只分配 partition_rows / N 行数据。

  具体数据示例见图五：总共9行数据，分成两个桶时余数为1，那么编号为1的桶会有 9 / 2 + 1 = 5 条数据，剩下4行数据的编号就是2，同理分成四个桶时也是第一个桶多分配一行，剩下6行等分到其余三个桶中。
  

窗口框架（窗口帧）规范

窗口定义中可以通过 frame 语句来指定当前分组的某种子集，窗口框架是相对于当前行来说的，基于当前行在当前分组内的位置来划分范围，例如可以通过定义一个从分组首行开始到当前行的框架来计算累计总和，也可以通过定义由当前行向前或向后扩展N行来计算移动平均值，数据示例（计算移动平均值时首行没有前置行所以只计算首位两行的平均值，末行同理）见图六：

窗口函数中的聚合函数作用范围由当前行的框架决定，作用范围等同于 FIRST_VALUE(), LAST_VALUE(), NTH_VALUE() 等非聚合函数。标准SQL中窗口函数是作用于整个分组范围，而MySQL是支持的但是也有限制，这些函数即使指定了窗口框架也仍然是作用于整个分组范围： CUME_DIST(), LAG(), LEAD(), NTILE(), PERCENT_RANK(), RANK(), DENSE_RANK(), ROW_NUMBER() 。

窗口框架的语法是： frame_units frame_extent, 其中 frame_units 要么是行边界"ROWS"指定起止行的位置，要么是范围边界"RANGE"指定基于当前行数值的特定范围， frame_extent 要么是单独的一个 frame_start 代表从指定起始位置到当前行的范围，要么是一个完整的"BETWEEN frame_start AND frame_end"代表明确的起止范围。框架边界 frame_start 和 frame_end 都取自以下几种值：

• CURRENT ROW: "ROWS"单位下的边界是当前行，"RANGE"单位下的边界是与当前行相等的值。
• UNDOUNDED PRECEDING: 边界是分组内的首行。
• UNBOUNDED FOLLOWING: 边界是分组内的末行。
• expr PRECEDING: "ROWS"下的边界是当前行前面的 expr 行，"RANGE"单位下的边界是当前行的值减去 expr 后的值对应的行，如果当前行为NULL那么范围边界就是同等的NULL行。expr 表达式的值可以是非负整数（如"10 PRECEDING"），也可以是以"INTERVAL val unit"形式的时间跨度（如"INTERVAL 5 DAY FOLLOWING"），范围边界"RANGE"应用在数字或者时间上的表达式时，要求 ORDER BY 也分别是基于对应的数字或时间上的表达式。
• expr FOLLOWING: 含义同上，只是方向是向后延伸。

如果没有指定 frame 语句，默认的窗口框架范围根据窗口定义中包不包含 ORDER BY 的情况会有所不同：

有 ORDER BY 时: 默认范围是从分组首行到当前行为止，包括在排序规则下与当前行相同的对等行，也就是等同于"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"。

没有 ORDER BY 时: 默认范围是整个分组，也就是等同于"RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"

基于这两种不同的默认逻辑，如果在窗口函数中需要指定排序获取确定顺序的查询结果，建议要显式指定窗口框架定义明确作用范围，防止后面如果升级版本时有默认逻辑的变动会产生错误结果。

最后用一些示例补充说明如果当前行的值为NULL时的窗口框架应用的范围：

1. ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING: 范围从NULL开始直到NULL结束，也就是只包含值为NULL的行。
2. ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING: 范围从NULL行开始直到分组末尾，因为NULL在升序时被视为最小值。
3. ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING: 范围是整个分组，因为NULL在降序时会被视为最大值排在最前面，所以从NULL行到分组末尾也就代表着整个分组。
4. ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING: 也是整个分组，因为升序时NULL排在最前面。
5. ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING: 同1。
6. ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING: 同1。
7. ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING: 范围含义是从分组首行到NULL行为止，又因为在升序时NULL行就排在最前面，所以还是只包含NULL行，等同于示例1。

窗口命名

窗口可以定义后自定义别名再由 OVER 子句去引用，查询时通过在 HAVING 和 ORDER BY 中间加上 WINDOW 子句来定义并命名一个或多个窗口，具体语法是: WINDOW window_name AS (window_spec)[, window_name AS (window_spec)...] ，其中 window_name 就是指定的别名， window_spec 含义与前文 OVER 子句中的相同。

在同一个查询中如果有多个窗口函数应用于同一个窗口时，通过单独的窗口命名可以简化SQL，并且后续如果需要调整窗口定义也只需要修改一处就能让所有用到的函数结果调整到位。

OVER 子句也可以通过在 OVER (window_name ...) 的语法下对已定义的窗口进行调整，比如说同一个分组标准下由两个不同的窗口函数按照不同排序规则执行不同的计算，即在"WINDOW w AS (PARTITION BY country)"的基础上，可以通过"FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first"和"FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last"分别获取按照year排序时的最小和最大年份。

不过需要注意的是，在 OVER 子句中只能通过添加额外子句属性去赋予已命名窗口额外的逻辑而不能通过重复的属性直接修改已定义的基础逻辑，也就是说在命名窗口中使用了 partition_clause, order_clause, frame_clause 的情况下， OVER 子句中是不能再使用相同的子句属性，即上述示例中不允许再用"OVER (w PARTITION BY year)"来修改分组逻辑。

在同时定义多个窗口时可以互相引用别名，先后顺序不会产生影响，只要引用关系不会产生闭环即可。比如"WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)"是合法的，但是"WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1)"形成了闭环的依赖所以是非法的。