CUME_DIST() over_clause

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.这表示窗口分区的窗口顺序中与当前行相邻或对等的行数除以窗口分区中的总行数。返回值的范围从0到1。

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

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

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. 对于val列中的一组值，下面的查询显示每行的CUME_DIST()值，以及类似的PERCENT_rank()函数返回的百分比秩值。For reference, the query also displays row numbers using ROW_NUMBER():作为参考，查询还使用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

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. 同龄人被认为是联系在一起的，并获得相同的排名。此函数将连续的列组分配给对等组；结果是大于1的组不会产生非连续的秩数。For an example, see the RANK() function description.有关示例，请参见RANK()函数说明。

This function should be used with ORDER BY to sort partition rows into the desired order. 此函数应与ORDER BY一起使用，以便按所需顺序对分区行进行排序。Without ORDER BY, all rows are peers.如果没有ORDER BY，则所有行都是对等行。

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

FIRST_VALUE(expr) [null_treatment] over_clause

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

over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”. over_clause如第12.21.2节，“窗口函数概念和语法”所述。null_treatment is as described in the section introduction.null_treatment如引言部分所述。

The following query demonstrates FIRST_VALUE(), LAST_VALUE(), and two instances of NTH_VALUE():下面的查询演示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 |
+----------+---------+------+-------+------+--------+--------+

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.对于第n个VALUE()调用，当前帧并不总是包含请求的行；在这种情况下，返回值为NULL。

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

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

N must be a literal nonnegative integer. N必须是文本非负整数。If N is 0, expr is evaluated for the current row.如果N为0，则对当前行计算expr。

Beginning with MySQL 8.0.22, N cannot be NULL. 从MySQL 8.0.22开始，N不能为空。In addition, it must now be an integer in the range 1 to 263, inclusive, in any of the following forms:此外，它现在必须是1到263（含）范围内的整数，形式如下：

over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”. over_clause如第12.21.2节，“窗口函数概念和语法”所述。null_treatment is as described in the section introduction.null_treatment如引言部分所述。

LAG() (and the similar LEAD() function) are often used to compute differences between rows. LAG()（和类似的LEAD()函数）通常用于计算行之间的差异。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:下面的查询显示了一组按时间顺序排列的观察值，对于每个观察值，还显示了相邻行的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 |
+----------+------+------+------+----------+-----------+

In the example, the LAG() and LEAD() calls use the default N and default values of 1 and NULL, respectively.在本例中，LAG()和LEAD()调用分别使用默认的N和默认值1和NULL。

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

LAG() and LEAD() also serve to compute sums rather than differences. LAG()和LEAD()还用于计算和而不是差。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 |
+------+

The following query shows the LAG() and LEAD() values for the rows adjacent to the current row. 下面的查询显示与当前行相邻的行的LAG()和LEAD()值。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.有关示例，请参阅斐波那契级数生成。

Beginning with MySQL 8.0.22, you cannot use a negative value for the rows argument of this function.从MySQL8.0.22开始，这个函数的rows参数不能使用负值。

LAST_VALUE(expr) [null_treatment] over_clause

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

over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”. over_clause如第12.21.2节，“窗口函数概念和语法”所述。null_treatment is as described in the section introduction.null_treatment如引言部分所述。

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

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

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

N must be a literal nonnegative integer. N必须是文本非负整数。If N is 0, expr is evaluated for the current row.如果N为0，则对当前行计算expr。

Beginning with MySQL 8.0.22, N cannot be NULL. 从MySQL 8.0.22开始，N不能为NULL。In addition, it must now be an integer in the range 1 to 263, inclusive, in any of the following forms:此外，它现在必须是1到263（含）范围内的整数，形式如下：

over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”. over_clause如第12.21.2节，“窗口函数概念和语法”所述。null_treatment is as described in the section introduction.null_treatment如引言部分所述。

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

In MySQL 8.0.22 and later, use of a negative value for the rows argument of this function is not permitted.在MySQL 8.0.22及更高版本中，不允许对该函数的rows参数使用负值。

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

Returns the value of expr from the N-th row of the window frame. 从窗口框架的第N行返回expr的值。If there is no such row, the return value is NULL.如果没有这样的行，则返回值为NULL。

N must be a literal positive integer.N必须是文本正整数。

from_first_last is part of the SQL standard, but the MySQL implementation permits only FROM FIRST (which is also the default). from_first_last是SQL标准的一部分，但是MySQL实现只允许FROM FIRST（这也是默认值）。This means that calculations begin at the first row of the window. 这意味着计算从窗口的第一行开始。FROM LAST is parsed, but produces an error. FROM LAST被解析，但产生一个错误。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.要获得与FROM LAST相同的效果（从窗口的最后一行开始计算），请使用ORDER BY按相反顺序排序。

over_clause is as described in Section 12.21.2, “Window Function Concepts and Syntax”. over_clause如第12.21.2节，“窗口函数概念和语法”所述。null_treatment is as described in the section introduction.null_treatment如引言部分所述。

For an example, see the FIRST_VALUE() function description.有关示例，请参见FIRST_VALUE()说明。

In MySQL 8.0.22 and later, you cannot use NULL for the row argument of this function.在MySQL8.0.22及更高版本中，此函数的行参数不能使用NULL。

NTILE(N) over_clause

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. 将分区划分为N个组（bucket），为分区中的每一行分配桶编号，并返回分区中当前行的桶编号。For example, if N is 4, NTILE() divides rows into four buckets. 例如，如果N是4，那么NTILE()将行划分为四个存储桶。If N is 100, NTILE() divides rows into 100 buckets.如果N是100，那么NTILE()将行划分为100个桶。

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

Beginning with MySQL 8.0.22, N cannot be NULL. 从MySQL 8.0.22开始，N不能为空。In addition, it must be an integer in the range 1 to 263, inclusive, in any of the following forms:此外，它必须是1到263（含）范围内的整数，形式如下：

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

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

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. 下面的查询显示，对于val列中的一组值，将行划分为两个或四个组所产生的百分位值。For reference, the query also displays row numbers using ROW_NUMBER():作为参考，查询还使用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 |
+------+------------+--------+--------+

Beginning with MySQL 8.0.22, the construct NTILE(NULL) is no longer permitted.从MySQL8.0.22开始，不再允许构造NTILE(NULL)。

PERCENT_RANK() over_clause

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:返回值的范围从0到1，表示行的相对秩，根据此公式计算，其中rank是行秩，row是分区行数：

(rank - 1) / (rows - 1)

This function should be used with ORDER BY to sort partition rows into the desired order. 此函数应与ORDER BY一起使用，以便按所需顺序对分区行进行排序。Without ORDER BY, all rows are peers.如果没有ORDER BY，则所有行都是对等行。

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

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

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.如果存在大于一个的组，则此函数不会将连续的列组分配给对等组；结果是非连续的秩数。

This function should be used with ORDER BY to sort partition rows into the desired order. 此函数应与ORDER BY一起使用，以便按所需顺序对分区行进行排序。Without ORDER BY, all rows are peers.如果没有ORDER BY，则所有行都是对等行。

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

The following query shows the difference between RANK(), which produces ranks with gaps, and DENSE_RANK(), which produces ranks without gaps. 下面的查询显示RANK()和densed_RANK()之间的差异，前者生成有间隙的列组，后者生成没有间隙的列组。The query shows rank values for each member of a set of values in the val column, which contains some duplicates. 查询显示val列中一组值的每个成员的秩值，其中包含一些重复项。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. RANK()为对等方（重复方）分配相同的秩值，下一个较大的值的秩高出对等方数量减1。DENSE_RANK() also assigns peers the same rank value, but the next higher value has a rank one greater. DENSE_RANK()还为对等方分配相同的秩值，但下一个较高的值的秩值较大。For reference, the query also displays row numbers using ROW_NUMBER():作为参考，查询还使用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

Returns the number of the current row within its partition. 返回分区中当前行的编号。Rows numbers range from 1 to the number of partition rows.行数的范围从1到分区行数。

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

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

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