MySQL 中文文档

# 12.21.1 窗口功能说明

本节描述非聚合窗口函数，对于查询中的每一行，使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数；看第 12.20.1 节，“聚合函数描述”.

有关窗口函数的使用信息和示例，以及术语的定义，例如超过子句、窗口、分区、框架和对等体，请参阅第 12.21.2 节，“窗口函数概念和语法”.

表 12.26 窗口函数

姓名 描述
CUME_DIST() 累计分配值
DENSE_RANK() 当前行在其分区内的排名，没有间隙
FIRST_VALUE() 窗口框架第一行的参数值
落后（） 来自分区内滞后当前行的行的参数值
LAST_VALUE() 窗口框架最后一行的参数值
带领（） 分区内行前导当前行的参数值
NTH_VALUE() 来自第 N 行窗口框架的参数值
NTILE() 其分区内当前行的桶数。
PERCENT_RANK() 百分比排名值
秩（） 当前行在其分区内的排名，有间隙
ROW_NUMBER() 其分区内的当前行数

在以下功能说明中，*over_clause代表超过子句，描述于第 12.21.2 节，“窗口函数概念和语法”.一些窗口函数允许null_treatment*指定如何处理的子句空值计算结果时的值。该子句是可选的。它是 SQL 标准的一部分，但 MySQL 实现只允许尊重空值（这也是默认设置）。这意味着空值计算结果时会考虑值。忽略空值已解析，但会产生错误。

  • CUME_DIST() over_clause

    返回一个值在一组值中的累积分布；即分区值小于或等于当前行值的百分比。这表示在窗口分区的窗口排序中在当前行之前或与当前行对等的行数除以窗口分区中的总行数。返回值范围从 0 到 1。

    此功能应与订购方式将分区行排序为所需的顺序。没有订购方式, 所有行都是对等的并且具有价值*ñ/ñ= 1，其中ñ*是分区大小。

    *over_clause*如中所述第 12.21.2 节，“窗口函数概念和语法”.

    以下查询显示，对于值专栏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 的组不会产生不连续的秩数。例如，请参阅秩（）功能说明。

    此功能应与订购方式将分区行排序为所需的顺序。没有订购方式，所有行都是对等的。

    *over_clause*如中所述第 12.21.2 节，“窗口函数概念和语法”.

  • FIRST_VALUE(*表达式*) [null_treatment] over_clause

    返回值*表达式*从窗框的第一行开始。

    *over_clause如中所述第 12.21.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_treatment] over_clause

    返回值*表达式从滞后（先于）当前行的行ñ其分区内的行。如果没有这样的行，则返回值为默认.例如，如果ñ为 3，返回值为默认前两行。如果ñ要么默认*缺失，默认值为 1 和空值， 分别。

    *ñ必须是文字非负整数。如果ñ为 0，表达式*对当前行进行评估。

    从 MySQL 8.0.22 开始，*ñ*不可能是空值.此外，它现在必须是范围内的整数1到2<sup>63</sup>，包括以下任何一种形式：

    • 无符号整数常量文字

    • 位置参数标记（?)

    • 用户定义的变量

    • 存储例程中的局部变量

    *over_clause如中所述第 12.21.2 节，“窗口函数概念和语法”.null_treatment*如介绍部分所述。

    落后（）（和类似的带领（）函数）通常用于计算行之间的差异。以下查询显示了一组按时间排序的观察结果，并且对于每个观察结果，落后（）和带领（）相邻行的值，以及当前行和相邻行之间的差异：

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

    在示例中，落后（）和带领（）调用使用默认值*ñ和默认*值为 1 和空值， 分别。

    第一行显示当没有前一行时会发生什么落后（）: 函数返回*默认*值（在这种情况下，空值）。当没有下一行时，最后一行显示相同的内容带领（）.

    落后（）和带领（）也用于计算总和而不是差异。考虑这个数据集，它包含斐波那契数列的前几个数字：

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

    以下查询显示落后（）和带领（）与当前行相邻的行的值。它还使用这些函数将来自前后行的值添加到当前行值。效果是生成斐波那契数列中的下一个数字，以及之后的下一个数字：

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

    生成初始斐波那契数集的一种方法是使用递归公用表表达式。例如，请参见斐波那契数列生成.

    从 MySQL 8.0.22 开始，您不能对此函数的 rows 参数使用负值。

  • LAST_VALUE(*表达式*) [null_treatment] over_clause

    返回值*表达式*从窗框的最后一行开始。

    *over_clause*如中所述第 12.21.2 节，“窗口函数概念和语法”. *null_treatment*如介绍部分所述。

    例如，请参阅FIRST_VALUE()功能说明。

  • 带领（*表达式* [, *ñ*[, *默认*]]) [null_treatment] over_clause

    返回值*表达式从引导（跟随）当前行的行开始ñ其分区内的行。如果没有这样的行，则返回值为默认.例如，如果ñ为 3，返回值为默认最后两行。如果ñ要么默认*缺失，默认值为 1 和空值， 分别。

    *ñ必须是文字非负整数。如果ñ为 0，表达式*对当前行进行评估。

    从 MySQL 8.0.22 开始，*ñ*不可能是空值.此外，它现在必须是范围内的整数1到2<sup>63</sup>，包括以下任何一种形式：

    • 无符号整数常量文字

    • 位置参数标记（?)

    • 用户定义的变量

    • 存储例程中的局部变量

    *over_clause如中所述第 12.21.2 节，“窗口函数概念和语法”.null_treatment*如介绍部分所述。

    例如，请参阅落后（）功能说明。

    在 MySQL 8.0.22 及更高版本中，不允许对此函数的 rows 参数使用负值。

  • NTH_VALUE(*表达式*, *ñ*) [从_first_last][null_treatment] over_clause

    返回值*表达式来自ñ*- 窗框的第 行。如果没有这样的行，则返回值为空值.

    *ñ*必须是文字正整数。

    *从_first_last*是 SQL 标准的一部分，但 MySQL 实现只允许从一开始（这也是默认设置）。这意味着计算从窗口的第一行开始。从最后已解析，但会产生错误。获得相同的效果从最后（从窗口的最后一行开始计算），使用订购方式以相反的顺序排序。

    *over_clause如中所述第 12.21.2 节，“窗口函数概念和语法”.null_treatment*如介绍部分所述。

    例如，请参阅FIRST_VALUE()功能说明。

    在 MySQL 8.0.22 及更高版本中，您不能使用空值对于此函数的行参数。

  • NTILE(*ñ*) over_clause

    将一个分区划分为*ñ分组（桶），为分区中的每一行分配其桶号，并返回其分区内当前行的桶号。例如，如果ñ是 4,NTILE()将行分成四个桶。如果ñ*是 100，NTILE()将行分成 100 个桶。

    ñ必须是文字正整数。桶号返回值范围从 1 到ñ.

    从 MySQL 8.0.22 开始，*ñ*不可能是空值.另外，它必须是范围内的整数1到2<sup>63</sup>，包括以下任何一种形式：

    • 无符号整数常量文字

    • 位置参数标记（?)

    • 用户定义的变量

    • 存储例程中的局部变量

      此功能应与订购方式将分区行排序为所需的顺序。

    *over_clause*如中所述第 12.21.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 |
+------+------------+--------+--------+

    从 MySQL 8.0.22 开始，构造NTILE（空）不再允许。

  • PERCENT_RANK() over_clause

    返回分区值小于当前行中的值的百分比，不包括最大值。返回值范围从 0 到 1，表示行相对排名，计算为此公式的结果，其中*秩是行秩和行*是分区行数：

    (rank - 1) / (rows - 1)

    此功能应与订购方式将分区行排序为所需的顺序。没有订购方式，所有行都是对等的。

    *over_clause*如中所述第 12.21.2 节，“窗口函数概念和语法”.

    例如，请参阅CUME_DIST()功能说明。

  • 秩（） over_clause

    返回当前行在其分区中的排名，有间隔。同行被视为平局并获得相同的排名。如果存在大小大于 1 的组，则此函数不会为对等组分配连续的等级；结果是不连续的排名数字。

    此功能应与订购方式将分区行排序为所需的顺序。没有订购方式，所有行都是对等的。

    *over_clause*如中所述第 12.21.2 节，“窗口函数概念和语法”.

    以下查询显示了秩（），这会产生有差距的等级，并且DENSE_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 到分区行数。

    订购方式影响行编号的顺序。没有订购方式，行编号是不确定的。

    ROW_NUMBER()为对等点分配不同的行号。要为对等点分配相同的值，请使用秩（）要么DENSE_RANK().例如，请参阅秩（）功能说明。

    *over_clause*如中所述第 12.21.2 节，“窗口函数概念和语法”.

← 12.21.2 窗口函数概念和语法 →