MySQL :: MySQL 8.4 参考手册 :: 14.20.1 窗口函数描述

版本 8.4

9.0
8.0

8.0 日本语

MySQL 8.4 参考手册 / ... / 窗口函数描述

14.20.1 窗口函数描述

本节介绍非聚合窗口函数，这些函数针对查询中的每一行执行计算，使用与该行相关的行。大多数聚合函数也可以用作窗口函数；参见第 14.19.1 节，“聚合函数描述”。

有关窗口函数使用信息和示例，以及诸如 OVER 子句、窗口、分区、框架和同类之类的术语的定义，请参见第 14.20.2 节，“窗口函数概念和语法”。

表 14.30 窗口函数

名称	描述
`CUME_DIST()`	累积分布值
`DENSE_RANK()`	当前行在其分区中的排名，无间隙
`FIRST_VALUE()`	窗口框架中第一行的参数值
`LAG()`	分区中滞后于当前行的参数值
`LAST_VALUE()`	窗口框架中最后一行参数的值
`LEAD()`	分区中领先于当前行的参数值
`NTH_VALUE()`	窗口框架中第 N 行的参数值
`NTILE()`	当前行在其分区中的桶号。
`PERCENT_RANK()`	百分比排名值
`RANK()`	当前行在其分区中的排名，有间隙
`ROW_NUMBER()`	当前行在其分区中的编号

在以下函数描述中，over_clause 代表 OVER 子句，如第 14.20.2 节，“窗口函数概念和语法” 中所述。某些窗口函数允许使用 null_treatment 子句，该子句指定在计算结果时如何处理 NULL 值。此子句是可选的。它是 SQL 标准的一部分，但 MySQL 实现只允许 RESPECT NULLS（这也是默认值）。这意味着在计算结果时会考虑 NULL 值。IGNORE NULLS 将被解析，但会产生错误。

CUME_DIST() over_clause

返回一组值中某个值的累积分布；也就是说，小于或等于当前行中值的窗分区值的百分比。这表示窗口分区窗口排序中位于当前行之前或与当前行同类的行数除以窗口分区中的行总数。返回值范围从 0 到 1。

此函数应与 ORDER BY 一起使用，以便按所需顺序对窗分区行进行排序。如果没有 ORDER BY，则所有行都是同类，并且具有 N/N = 1 的值，其中 N 是窗分区的大小。

over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。

以下查询显示 val 列中的值集，以及每行的 CUME_DIST() 值以及类似的 PERCENT_RANK() 函数返回的百分比排名值。为了参考，该查询还使用 ROW_NUMBER() 显示行号

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 |
+------+------------+--------------------+--------------+

DENSE_RANK() over_clause
返回当前行在其分区中的排名，无间隙。同类被视为平局，并接收相同的排名。此函数为同类组分配连续排名；结果是，大小大于 1 的组不会产生不连续的排名号。有关示例，请参见 RANK() 函数描述。
此函数应与 ORDER BY 一起使用，以便按所需顺序对窗分区行进行排序。如果没有 ORDER BY，则所有行都是同类。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。

FIRST_VALUE(expr) [null_treatment] over_clause

返回窗口框架中第一行中 expr 的值。

over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。null_treatment 如本节介绍中所述。

以下查询演示了 FIRST_VALUE()、LAST_VALUE() 以及两个 NTH_VALUE() 实例。

mysql> SELECT
         time, subject, val,
         FIRST_VALUE(val)  OVER w AS 'first',
         LAST_VALUE(val)   OVER w AS 'last',
         NTH_VALUE(val, 2) OVER w AS 'second',
         NTH_VALUE(val, 4) OVER w AS 'fourth'
       FROM observations
       WINDOW w AS (PARTITION BY subject ORDER BY time
                    ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time     | subject | val  | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113   |   10 |    10 |   10 |   NULL |   NULL |
| 07:15:00 | st113   |    9 |    10 |    9 |      9 |   NULL |
| 07:30:00 | st113   |   25 |    10 |   25 |      9 |   NULL |
| 07:45:00 | st113   |   20 |    10 |   20 |      9 |     20 |
| 07:00:00 | xh458   |    0 |     0 |    0 |   NULL |   NULL |
| 07:15:00 | xh458   |   10 |     0 |   10 |     10 |   NULL |
| 07:30:00 | xh458   |    5 |     0 |    5 |     10 |   NULL |
| 07:45:00 | xh458   |   30 |     0 |   30 |     10 |     30 |
| 08:00:00 | xh458   |   25 |     0 |   25 |     10 |     30 |
+----------+---------+------+-------+------+--------+--------+

每个函数都使用当前框架中的行，根据显示的窗口定义，该框架从第一个窗分区行扩展到当前行。对于 NTH_VALUE() 调用，当前框架并不总是包含请求的行；在这种情况下，返回值为 NULL。

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

返回在分区内当前行之前N行的expr的值。如果不存在这样的行，则返回值为default。例如，如果N为 3，则前三行的返回值为default。如果N或default缺失，则默认值为 1 和NULL。

N必须是字面非负整数。如果N为 0，则expr在当前行上进行求值。

N不能为NULL，并且必须是以下任何形式的介于0到2⁶³（包含）之间的整数

无符号整数常量字面量
位置参数标记(?)
用户定义的变量
存储例程中的局部变量

over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。null_treatment 如本节介绍中所述。

LAG()（以及类似的LEAD()函数）通常用于计算行之间的差异。以下查询显示了一组按时间排序的观测值，以及每个观测值的LAG()和LEAD()值（来自相邻行），以及当前行与相邻行之间的差异

mysql> SELECT
         t, val,
         LAG(val)        OVER w AS 'lag',
         LEAD(val)       OVER w AS 'lead',
         val - LAG(val)  OVER w AS 'lag diff',
         val - LEAD(val) OVER w AS 'lead diff'
       FROM series
       WINDOW w AS (ORDER BY t);
+----------+------+------+------+----------+-----------+
| t        | val  | lag  | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 |  100 | NULL |  125 |     NULL |       -25 |
| 13:00:00 |  125 |  100 |  132 |       25 |        -7 |
| 14:00:00 |  132 |  125 |  145 |        7 |       -13 |
| 15:00:00 |  145 |  132 |  140 |       13 |         5 |
| 16:00:00 |  140 |  145 |  150 |       -5 |       -10 |
| 17:00:00 |  150 |  140 |  200 |       10 |       -50 |
| 18:00:00 |  200 |  150 | NULL |       50 |      NULL |
+----------+------+------+------+----------+-----------+

在示例中，LAG()和LEAD()调用使用默认的N和default值 1 和NULL。

第一行显示了当LAG()没有前一行时会发生什么：该函数返回default值（在本例中为NULL）。最后一行显示了当LEAD()没有下一行时会发生相同的情况。

LAG()和LEAD()还可以用于计算总和，而不是差异。考虑以下数据集，其中包含斐波那契数列的前几个数字

mysql> SELECT n FROM fib ORDER BY n;
+------+
| n    |
+------+
|    1 |
|    1 |
|    2 |
|    3 |
|    5 |
|    8 |
+------+

以下查询显示了当前行相邻行的LAG()和LEAD()值。它还使用这些函数将前一行和后一行的值添加到当前行的值中。效果是生成斐波那契数列中的下一个数字，以及下一个数字

mysql> SELECT
         n,
         LAG(n, 1, 0)      OVER w AS 'lag',
         LEAD(n, 1, 0)     OVER w AS 'lead',
         n + LAG(n, 1, 0)  OVER w AS 'next_n',
         n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
       FROM fib
       WINDOW w AS (ORDER BY n);
+------+------+------+--------+-------------+
| n    | lag  | lead | next_n | next_next_n |
+------+------+------+--------+-------------+
|    1 |    0 |    1 |      1 |           2 |
|    1 |    1 |    2 |      2 |           3 |
|    2 |    1 |    3 |      3 |           5 |
|    3 |    2 |    5 |      5 |           8 |
|    5 |    3 |    8 |      8 |          13 |
|    8 |    5 |    0 |     13 |           8 |
+------+------+------+--------+-------------+

生成初始斐波那契数列的一种方法是使用递归公用表表达式。有关示例，请参见Fibonacci Series Generation。

您不能为该函数的行参数使用负值。

LAST_VALUE(expr) [null_treatment] over_clause
返回窗口帧中最后一行expr的值。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。null_treatment 如本节介绍中所述。
有关示例，请参见FIRST_VALUE()函数说明。
LEAD(expr [, N[, default]]) [null_treatment] over_clause
返回在分区内当前行之后N行的expr的值。如果不存在这样的行，则返回值为default。例如，如果N为 3，则最后三行的返回值为default。如果N或default缺失，则默认值为 1 和NULL。
N必须是字面非负整数。如果N为 0，则expr在当前行上进行求值。
N不能为NULL，并且必须是以下任何形式的介于0到2⁶³（包含）之间的整数
- 无符号整数常量字面量
- 位置参数标记(?)
- 用户定义的变量
- 存储例程中的局部变量
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。null_treatment 如本节介绍中所述。
有关示例，请参见LAG()函数说明。
不允许为该函数的行参数使用负值。
NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause
返回窗口帧中第N行的expr的值。如果不存在这样的行，则返回值为NULL。
N必须是字面正整数。
from_first_last是 SQL 标准的一部分，但 MySQL 实现仅允许FROM FIRST（这也是默认值）。这意味着计算从窗口的第一行开始。FROM LAST将被解析，但会产生错误。若要获得与FROM LAST相同的效果（从窗口的最后一行开始计算），请使用ORDER BY以反向顺序排序。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。null_treatment 如本节介绍中所述。
有关示例，请参见FIRST_VALUE()函数说明。
您不能为该函数的行参数使用NULL。
NTILE(N) over_clause
将分区划分为N个组（桶），为分区中的每行分配其桶号，并返回分区内当前行的桶号。例如，如果N为 4，则NTILE()将行划分为四个桶。如果N为 100，则NTILE()将行划分为 100 个桶。
N必须是字面正整数。桶号返回值范围为 1 到N。
N不能为NULL，并且必须是以下任何形式的介于0到2⁶³（包含）之间的整数
- 无符号整数常量字面量
- 位置参数标记(?)
- 用户定义的变量
- 存储例程中的局部变量
此函数应与ORDER BY一起使用，以便将分区行按所需顺序排序。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。
以下查询显示了val列中值集的百分位数，这些百分位数是通过将行划分为两组或四组得出的。为了便于参考，该查询还使用ROW_NUMBER()显示行号
```
mysql> SELECT
         val,
         ROW_NUMBER() OVER w AS 'row_number',
         NTILE(2)     OVER w AS 'ntile2',
         NTILE(4)     OVER w AS 'ntile4'
       FROM numbers
       WINDOW w AS (ORDER BY val);
+------+------------+--------+--------+
| val  | row_number | ntile2 | ntile4 |
+------+------------+--------+--------+
|    1 |          1 |      1 |      1 |
|    1 |          2 |      1 |      1 |
|    2 |          3 |      1 |      1 |
|    3 |          4 |      1 |      2 |
|    3 |          5 |      1 |      2 |
|    3 |          6 |      2 |      3 |
|    4 |          7 |      2 |      3 |
|    4 |          8 |      2 |      4 |
|    5 |          9 |      2 |      4 |
+------+------------+--------+--------+
```
不允许使用NTILE(NULL)构造。
PERCENT_RANK() over_clause
返回分区值小于当前行中值的百分比，不包括最高值。返回值范围为 0 到 1，表示行的相对排名，它是根据以下公式计算得出的，其中rank是行排名，rows是分区行数
```
(rank - 1) / (rows - 1)
```
此函数应与 ORDER BY 一起使用，以便按所需顺序对窗分区行进行排序。如果没有 ORDER BY，则所有行都是同类。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。
有关示例，请参见CUME_DIST()函数说明。
RANK() over_clause
返回当前行在其分区内的排名，存在间隙。同级被视为并列，并获得相同的排名。此函数不会为同级组分配连续排名（如果存在大于 1 的同级组）；结果是非连续排名编号。
此函数应与 ORDER BY 一起使用，以便按所需顺序对窗分区行进行排序。如果没有 ORDER BY，则所有行都是同类。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。
以下查询显示了RANK()（生成有间隙的排名）与DENSE_RANK()（生成无间隙的排名）之间的区别。该查询显示了val列中值集的每个成员的排名值，该列包含一些重复项。RANK()为同级（重复项）分配相同的排名值，下一个更大的值具有比同级数量减 1 大的排名。DENSE_RANK()也为同级分配相同的排名值，但下一个更高的值排名高 1 位。为了便于参考，该查询还使用ROW_NUMBER()显示行号
```
mysql> 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);
+------+------------+------+------------+
| val  | row_number | rank | dense_rank |
+------+------------+------+------------+
|    1 |          1 |    1 |          1 |
|    1 |          2 |    1 |          1 |
|    2 |          3 |    3 |          2 |
|    3 |          4 |    4 |          3 |
|    3 |          5 |    4 |          3 |
|    3 |          6 |    4 |          3 |
|    4 |          7 |    7 |          4 |
|    4 |          8 |    7 |          4 |
|    5 |          9 |    9 |          5 |
+------+------------+------+------------+
```
ROW_NUMBER() over_clause
返回当前行在其分区内的编号。行号范围为 1 到分区行数。
ORDER BY影响行编号的顺序。如果没有ORDER BY，则行编号是非确定性的。
ROW_NUMBER()为同级分配不同的行号。若要为同级分配相同的值，请使用RANK()或DENSE_RANK()。有关示例，请参见RANK()函数说明。
over_clause 如第 14.20.2 节，“窗口函数概念和语法” 中所述。

PREV HOME UP NEXT