MySQL :: MySQL 8.4 Reference Manual :: 14.20.1 Window Function Descriptions

version 8.4

8.0 current

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Window Function Descriptions

14.20.1 窗口函数说明

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

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

表 14.30 窗口函数

Name	Description
`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 |
+------+------+------+--------+-------------+
```
生成初始斐波那契数列集的一种方法是使用递归公用表表达式。有关示例，请参阅斐波那契数列生成。

不能对此函数的行参数使用负值。
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_子句

返回当前行在其分区内的排名，带间隙。同级被视为并列，并获得相同的排名。如果存在大于1的组，则此函数不会为同级组分配连续排名；结果是非连续排名数字。

此函数应与 ORDER BY 一起使用，以便将分区行按所需顺序排序。如果没有 ORDER BY，则所有行都是同级。

over_子句 的描述如第 14.20.2 节 “窗口函数概念和语法” 中所述。

以下查询显示了 RANK()（生成带间隙的排名）和 DENSE_RANK()（生成不带间隙的排名）之间的区别。该查询显示 val 列中一组值的每个成员的排名值，该列包含一些重复项。RANK() 为同级（重复项）分配相同的排名值，下一个更大的值的排名比同级数减一高。 DENSE_RANK() 也为同级分配相同的排名值，但下一个更高的值的排名高一个。作为参考，该查询还使用 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_子句

返回当前行在其分区内的行号。行号范围从 1 到分区行数。

ORDER BY 影响行编号的顺序。如果没有 ORDER BY，则行编号是不确定的。

ROW_NUMBER() 为同级分配不同的行号。要为同级分配相同的值，请使用 RANK() 或 DENSE_RANK()。例如，请参见 RANK() 函数描述。

over_子句 的描述如第 14.20.2 节 “窗口函数概念和语法” 中所述。

PREV HOME UP NEXT