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

version 8.3

8.0 current

8.0 Japanese

MySQL 8.3 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

在当前行的分区中返回排名，不留间隙。同行被视为平局，获得相同的排名。该函数将连续的排名分配给同行组；结果是大于一组的组不产生非连续的排名号码。例如，见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() 也可以用来计算总和，而不是差异。考虑以下数据集，其中包含 Fibonacci 序列的前几个数字：

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

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

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 数字的一种方法是使用递归公共表表达式。例如，见 Fibonacci 序列生成。

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

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() 函数描述。

不允许使用负值作为该函数的行参数。
第N值(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 节，“窗口函数概念和语法” 所述。

以下查询显示了将值集划分为两个或四个组的百分位值结果。为参考，该查询还显示了使用 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

返回当前行在其分区中的排名，带有间隙。同伴被视为平局，具有相同的排名。该函数不将连续的排名分配给同伴组，如果组的大小大于一；结果是非连续的排名数字。

此函数应与 ORDER BY 一起使用，以对分区行进行排序。如果没有 ORDER BY，所有行都是同等的。

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

以下查询显示了 RANK() 和 DENSE_RANK() 之间的差异。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_clause

返回当前行在其分区中的编号。行号从 1 到分区行数。

ORDER BY 影响行编号的顺序。没有 ORDER BY，行编号是非确定性的。

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

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

PREV HOME UP NEXT