CUME_DIST() over_clause

返回一组值中某个值的累积分布；即小于或等于当前行中值的分区值的百分比。这表示窗口分区中窗口排序中当前行之前或与当前行同行的行数除以窗口分区中的总行数。返回值范围从 0 到 1。Returns the cumulative distribution of a value within a group of values; that is, the percentage of partition values less than or equal to the value in the current row. This represents the number of rows preceding or peer with the current row in the window ordering of the window partition divided by the total number of rows in the window partition. Return values range from 0 to 1.

此函数应与 ORDER BY 一起使用，以将分区行按所需顺序排序。如果没有 ORDER BY，所有行都是同等的，其值为 N/N = 1，其中 N 是分区大小。This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers and have value N/N = 1, where N is the partition size.

over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”.

以下查询显示了，对于 val 列中的值集合，每行的 CUME_DIST() 值，以及类似函数 PERCENT_RANK() 返回的百分比排名值。作为参考，该查询还使用 ROW_NUMBER() 显示行号：The following query shows, for the set of values in the val column, the CUME_DIST() value for each row, as well as the percentage rank value returned by the similar PERCENT_RANK() function. For reference, the query also displays row numbers using 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()函数说明。Returns the rank of the current row within its partition, without gaps. Peers are considered ties and receive the same rank. This function assigns consecutive ranks to peer groups; the result is that groups of size greater than one do not produce noncontiguous rank numbers. For an example, see the RANK() function description.

该函数应与ORDER BY一起使用，以将分区行排序为所需的顺序。如果没有ORDER BY，所有行都被认为是同行。This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.

over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”.

FIRST_VALUE(expr) [null_treatment] over_clause

返回窗口帧第一行的 expr 的值。Returns the value of expr from the first row of the window frame.

over_clause 的说明请参见 第 14.20.2 节，“窗口函数概念与语法”。null_treatment 的说明请参见本节介绍部分。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”. null_treatment is as described in the section introduction.

下面的查询演示了 FIRST_VALUE()、LAST_VALUE() 以及两个 NTH_VALUE() 的用例：The following query demonstrates FIRST_VALUE(), LAST_VALUE(), and two instances of 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。Each function uses the rows in the current frame, which, per the window definition shown, extends from the first partition row to the current row. For the NTH_VALUE() calls, the current frame does not always include the requested row; in such cases, the return value is NULL.

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

返回分区内滞后（前置）当前行 N 行的 expr 的值。如果不存在这样的行，返回值为 default。例如，如果 N 为 3，则前三行的返回值为 default。如果缺少 N 或 default，默认值分别为 1 和 NULL。Returns the value of expr from the row that lags (precedes) the current row by N rows within its partition. If there is no such row, the return value is default. For example, if N is 3, the return value is default for the first three rows. If N or default are missing, the defaults are 1 and NULL, respectively.

N 必须是字面上的非负整数。如果 N 为 0，则 expr 会对当前行进行评估。N must be a literal nonnegative integer. If N is 0, expr is evaluated for the current row.

N 不能为 NULL，并且必须是以下任一形式中从 0 到 263（含）范围内的整数：N cannot be NULL, and must be an integer in the range 0 to 263, inclusive, in any of the following forms:

over_clause 的说明请参见 第 14.20.2 节，“窗口函数概念与语法”。null_treatment 的说明请参见本节介绍部分。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”. null_treatment is as described in the section introduction.

LAG()（以及类似的 LEAD() 函数）常用于计算行之间的差异。以下查询显示了一组按时间排序的观测值，并且对于每一个观测值，显示相邻行的 LAG() 和 LEAD() 值，以及当前行与相邻行之间的差异：LAG() (and the similar LEAD() function) are often used to compute differences between rows. The following query shows a set of time-ordered observations and, for each one, the LAG() and LEAD() values from the adjoining rows, as well as the differences between the current and adjoining rows:

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。In the example, the LAG() and LEAD() calls use the default N and default values of 1 and NULL, respectively.

第一行显示了当 LAG() 没有前一行时会发生什么：函数返回 default 值（在本例中为 NULL）。最后一行显示了当 LEAD() 没有下一行时同样的情况。The first row shows what happens when there is no previous row for LAG(): The function returns the default value (in this case, NULL). The last row shows the same thing when there is no next row for LEAD().

LAG() 和 LEAD() 也可以用来计算和而不是差。考虑这个数据集，它包含了斐波那契数列的前几个数字：LAG() and LEAD() also serve to compute sums rather than differences. Consider this data set, which contains the first few numbers of the Fibonacci series:

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

下面的查询显示了当前行相邻行的 LAG() 和 LEAD() 值。它还使用这些函数将前一行和下一行的值加到当前行的值上。效果是生成斐波那契数列中的下一个数字，以及其后的下一个数字：The following query shows the LAG() and LEAD() values for the rows adjacent to the current row. It also uses those functions to add to the current row value the values from the preceding and following rows. The effect is to generate the next number in the Fibonacci series, and the next number after that:

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

生成初始斐波那契数列的一种方法是使用递归公共表表达式。有关示例，请参见 斐波那契数列生成。One way to generate the initial set of Fibonacci numbers is to use a recursive common table expression. For an example, see Fibonacci Series Generation.

你不能对这个函数的 rows 参数使用负值。You cannot use a negative value for the rows argument of this function.

LAST_VALUE(expr) [null_treatment] over_clause

返回窗口帧中最后一行的 expr 的值。Returns the value of expr from the last row of the window frame.

over_clause 的说明请参见 第 14.20.2 节，“窗口函数概念与语法”。null_treatment 的说明请参见本节介绍部分。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”. null_treatment is as described in the section introduction.

有关示例，请参见 FIRST_VALUE() 函数描述。For an example, see the FIRST_VALUE() function description.

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

返回在其分区中领先（跟随）当前行 N 行的行的 expr 的值。如果没有这样的行，返回值为 default。例如，如果 N 为 3，则对于最后三行，返回值为 default。如果 N 或 default 缺失，则默认值分别为 1 和 NULL。Returns the value of expr from the row that leads (follows) the current row by N rows within its partition. If there is no such row, the return value is default. For example, if N is 3, the return value is default for the last three rows. If N or default are missing, the defaults are 1 and NULL, respectively.

N 必须是字面上的非负整数。如果 N 为 0，则 expr 会对当前行进行评估。N must be a literal nonnegative integer. If N is 0, expr is evaluated for the current row.

N 不能为 NULL，并且必须是以下任一形式中从 0 到 263（含）范围内的整数：N cannot be NULL, and must be an integer in the range 0 to 263, inclusive, in any of the following forms:

over_clause 的说明请参见 第 14.20.2 节，“窗口函数概念与语法”。null_treatment 的说明请参见本节介绍部分。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”. null_treatment is as described in the section introduction.

有关示例，请参见 LAG() 函数描述。For an example, see the LAG() function description.

不允许在此函数的 rows 参数中使用负值。Use of a negative value for the rows argument of this function is not permitted.

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

返回窗口帧中第 N 行的 expr 的值。如果不存在这样的行，则返回值为 NULL。Returns the value of expr from the N-th row of the window frame. If there is no such row, the return value is NULL.

N 必须是一个正整数常量。N must be a literal positive integer.

from_first_last 是 SQL 标准的一部分，但 MySQL 实现只允许 FROM FIRST（这也是默认值）。这意味着计算从窗口的第一行开始。FROM LAST 会被解析，但会产生错误。要获得与 FROM LAST 相同的效果（计算从窗口的最后一行开始），请使用 ORDER BY 倒序排序。from_first_last is part of the SQL standard, but the MySQL implementation permits only FROM FIRST (which is also the default). This means that calculations begin at the first row of the window. FROM LAST is parsed, but produces an error. To obtain the same effect as FROM LAST (begin calculations at the last row of the window), use ORDER BY to sort in reverse order.

over_clause 的说明请参见 第 14.20.2 节，“窗口函数概念与语法”。null_treatment 的说明请参见本节介绍部分。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”. null_treatment is as described in the section introduction.

有关示例，请参见 FIRST_VALUE() 函数描述。For an example, see the FIRST_VALUE() function description.

此函数的行参数不能使用 NULL。You cannot use NULL for the row argument of this function.

NTILE(N) over_clause

将一个分区划分为 N 个组（桶），为分区中的每一行分配其桶号，并返回当前行在其分区中的桶号。例如，如果 N 是 4，NTILE() 将行划分为四个桶。如果 N 是 100，NTILE() 将行划分为 100 个桶。Divides a partition into N groups (buckets), assigns each row in the partition its bucket number, and returns the bucket number of the current row within its partition. For example, if N is 4, NTILE() divides rows into four buckets. If N is 100, NTILE() divides rows into 100 buckets.

N 必须是字面值的正整数。桶号的返回值范围从 1 到 N。N must be a literal positive integer. Bucket number return values range from 1 to N.

N 不能为 NULL，并且必须是以下任一形式中从 0 到 263（含）范围内的整数：N cannot be NULL, and must be an integer in the range 0 to 263, inclusive, in any of the following forms:

此函数应与 ORDER BY 一起使用，以将分区行按所需顺序排序。This function should be used with ORDER BY to sort partition rows into the desired order.

over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”.

以下查询显示了，对于 val 列中的值集合，将行划分为两组或四组后得到的百分位数值。作为参考，该查询还使用 ROW_NUMBER() 显示行号：The following query shows, for the set of values in the val column, the percentile values resulting from dividing the rows into two or four groups. For reference, the query also displays row numbers using 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)。The construct NTILE(NULL) is not permitted.

PERCENT_RANK() over_clause

返回当前行值以下的分区值百分比，不包括最大值。返回值范围从 0 到 1，表示行的相对排名，计算公式如下，其中 rank 是行的排名，rows 是分区中的行数：Returns the percentage of partition values less than the value in the current row, excluding the highest value. Return values range from 0 to 1 and represent the row relative rank, calculated as the result of this formula, where rank is the row rank and rows is the number of partition rows:

(rank - 1) / (rows - 1)

该函数应与ORDER BY一起使用，以将分区行排序为所需的顺序。如果没有ORDER BY，所有行都被认为是同行。This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.

over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”.

示例请参见 CUME_DIST() 函数说明。For an example, see the CUME_DIST() function description.

RANK() over_clause

返回当前行在其分区内的排名，可能有间隔。具有相同值的行被视为并列，并获得相同的排名。如果存在大小大于一的分组，该函数不会为并列组分配连续的排名；结果是非连续的排名数字。Returns the rank of the current row within its partition, with gaps. Peers are considered ties and receive the same rank. This function does not assign consecutive ranks to peer groups if groups of size greater than one exist; the result is noncontiguous rank numbers.

该函数应与ORDER BY一起使用，以将分区行排序为所需的顺序。如果没有ORDER BY，所有行都被认为是同行。This function should be used with ORDER BY to sort partition rows into the desired order. Without ORDER BY, all rows are peers.

over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”.

以下查询显示了RANK()（会产生带间隙的排名）与DENSE_RANK()（会产生无间隙的排名）之间的差异。查询显示了val列中值集合中每个成员的排名值，该列包含一些重复值。RANK()会将相同的值（重复值）分配相同的排名，之后的更大值的排名会比同级值的数量少一名。DENSE_RANK()也会为相同的值分配相同的排名，但之后的更高值的排名只比前一个大一。为了参考，查询还使用ROW_NUMBER()显示了行号：The following query shows the difference between RANK(), which produces ranks with gaps, and DENSE_RANK(), which produces ranks without gaps. The query shows rank values for each member of a set of values in the val column, which contains some duplicates. RANK() assigns peers (the duplicates) the same rank value, and the next greater value has a rank higher by the number of peers minus one. DENSE_RANK() also assigns peers the same rank value, but the next higher value has a rank one greater. For reference, the query also displays row numbers using 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 到分区中的行数。Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.

ORDER BY 会影响行编号的顺序。如果没有 ORDER BY，行编号是非确定性的。ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is nondeterministic.

ROW_NUMBER() 会为同行分配不同的行号。要为同行分配相同的值，请使用 RANK() 或 DENSE_RANK()。有关示例，请参阅 RANK() 函数说明。ROW_NUMBER() assigns peers different row numbers. To assign peers the same value, use RANK() or DENSE_RANK(). For an example, see the RANK() function description.

over_clause 如 第 14.20.2 节，“窗口函数概念和语法” 中所述。over_clause is as described in Section 14.20.2, “Window Function Concepts and Syntax”.