窗口功能说明

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

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

表窗口功能

名称	描述
`CUME_DIST()`	累积分布值
`DENSE_RANK()`	当前行在其分区内的排名，没有空格
`FIRST_VALUE()`	窗框第一行的自变量值
`LAG()`	分区中当前行滞后的参数值
`LAST_VALUE()`	窗口框架最后一行的参数值
`LEAD()`	来自分区内当前行的行的参数值
`NTH_VALUE()`	窗框第N行的自变量值
`NTILE()`	分区中当前行的存储桶号。
`PERCENT_RANK()`	百分比排名值
`RANK()`	当前行在其分区内的排名，带有空格
`ROW_NUMBER()`	分区中当前行的数量

在以下功能描述中，over_clause表示“窗口函数的概念和语法”中OVER描述的子句。一些窗口函数允许使用一个子句来指定在计算结果时如何处理值。此子句是可选的。它是SQL标准的一部分，但是MySQL实现仅允许（这也是默认设置）。这意味着在计算结果时会考虑值。被解析，但产生错误。null_treatmentNULLRESPECT NULLSNULLIGNORE NULLS

CUME_DIST()over_clause

返回一组值在一个值中的累积分布；也就是说，分区值的百分比小于或等于当前行中的值。这表示窗口分区的窗口顺序中当前行之前或对等的行数除以窗口分区的总行数。返回值范围为0到1。

此函数应用于ORDER BY将分区行按所需顺序排序。如果不使用ORDER BY，则所有行都是对等端，并且值N/N= 1，其中N是分区大小。

over_clause如“窗口函数的概念和语法”中所述。

以下查询针对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如“窗口函数的概念和语法”中所述。

FIRST_VALUE(expr)[ null_treatment]over_clause

expr从窗口框架的第一行返回值。

over_clause如“窗口函数的概念和语法”中所述。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

expr从滞后于当前行的N行的行中返回其值。如果没有这样的行，则返回值为default。例如，如果N为3，则返回值为default前两行。如果N或default缺少，则默认分别为1和NULL。

N必须是文字非负整数。如果N为0，expr则为当前行求值。

over_clause如“窗口函数的概念和语法”中所述。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和default1值和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 	|
+------	+------	+------	+--------	+-------------	+

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

LAST_VALUE(expr)[ null_treatment]over_clause
expr从窗口框架的最后一行返回值。
over_clause如“窗口函数的概念和语法”中所述。null_treatment如部分介绍中所述。
有关示例，请参见FIRST_VALUE()功能说明。
LEAD(expr[,N[,default]])[ null_treatment]over_clause
返回expr从当前行开始（跟随）N其分区内的每一行的行的值。如果没有这样的行，则返回值为default。例如，如果N为3，则返回值为default最后两行。如果N或default缺少，则默认分别为1和NULL。
N必须是文字非负整数。如果N为0，expr则为当前行求值。
over_clause如“窗口函数的概念和语法”中所述。null_treatment如部分介绍中所述。
有关示例，请参见LAG()功能说明。
NTH_VALUE(expr,N)[ from_first_last][ null_treatment]over_clause
expr从N窗口框架的第-行返回值。如果没有这样的行，则返回值为NULL。
N必须为文字正整数。
from_first_last是SQL标准的一部分，但MySQL实现仅允许FROM FIRST（这也是默认设置）。这意味着计算从窗口的第一行开始。FROM LAST被解析，但产生错误。要获得与FROM LAST（在窗口的最后一行开始计算）相同的效果，请使用ORDER BY相反的顺序进行排序。
over_clause如“窗口函数的概念和语法”中所述。null_treatment如部分介绍中所述。
有关示例，请参见FIRST_VALUE()功能说明。

NTILE(N)over_clause

将分区划分为N组（存储桶），为分区中的每一行分配其存储桶编号，然后返回其分区中当前行的存储桶编号。例如，如果N为4，NTILE()则将行分为四个存储桶。如果N为100，NTILE()则将行分为100个存储桶。

N必须为文字正整数。值区编号的返回值范围是1到N。

此函数应用于ORDER BY将分区行按所需顺序排序。

over_clause如“窗口函数的概念和语法”中所述。

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

PERCENT_RANK()over_clause
返回小于当前行中值（最高值除外）的分区值的百分比。返回值的范围是0到1，并表示行相对排名（根据此公式的结果计算得出），其中rank是行排名和rows分区行数：
```
(rank - 1) / (rows - 1)
```
此函数应用于ORDER BY将分区行按所需顺序排序。没有ORDER BY，所有行都是对等体。
over_clause如“窗口函数的概念和语法”中所述。
有关示例，请参见CUME_DIST()功能说明。
RANK()over_clause
返回当前行在其分区内的间隔（带间隔）。对等体被视为纽带，并获得相同的等级。如果存在大小大于1的组，则此功能不会将连续的等级分配给对等组。结果是不连续的等级编号。
此函数应用于ORDER BY将分区行按所需顺序排序。没有ORDER BY，所有行都是对等体。
over_clause如“窗口函数的概念和语法”中所述。
以下查询显示之间的差异RANK()，产生的差距为空位，而DENSE_RANK()产生的差距为空差。该查询在val列中显示一组值中每个成员的等级值，其中包含一些重复项。RANK()向同级（重复项）分配相同的等级值，下一个更大的值的等级更高，即同级数减去1。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如“窗口函数的概念和语法”中所述。