MySQL非聚合函数窗口函数用法(8.0+版本)

最新推荐文章于 2024-04-29 20:06:03 发布

晴空_万里

最新推荐文章于 2024-04-29 20:06:03 发布

阅读量913

点赞数

分类专栏：数据库文章标签： mysql

原文链接：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

原文链接：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_rank
说明：有道机翻，整体没什么修改，更多说明参考原文档链接（见文章开头）。

本节描述非聚合窗口函数，对于查询中的每一行，使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数;请参见12.20.1节聚合函数描述。关于窗口函数的用法和示例，以及OVER子句、window、partition、frame和peer等术语的定义，请参见12.21.2节窗口函数的概念和语法。

（非聚合）窗口函数列表：
在这里插入图片描述
在下面的函数描述中，over_clause子句表示OVER子句，在12.21.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子句在12.21.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
返回当前行在其分区内的排序，不包含间隔。相同的会认为无差别，得到相同的排名。这个函数将连续的等级分配给对等组;结果是，大于1的组不会产生不连续的序号。例如，请参阅RANK()函数描述。
这个函数应该与ORDER BY一起使用，以便将分区行按所需的顺序排序。如果没有ORDER BY，所有行都是对等的。
over_clause子句在12.21.2节“窗口函数的概念和语法”中有描述。
补充一个例子：

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

FIRST_VALUE(expr) [null_treatment] over_clause
返回窗口框架第一行中expr的值。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。（这句话的意思是，比如要返回第二的数值，当前行是第一时，那么就是NULL，即只有从第二往后，才有返回。参考上面的案例）

LAG(expr [, N[, default]]) [null_treatment] over_clause
返回分区中滞后(先于)当前行N行的expr值。如果没有这样的行，则返回值为default。例如，如果N为3，则前两行的返回值为默认值。如果缺少N或default，则缺省值分别为1和NULL。N必须是一个非负整数。如果N为0，则对当前行计算expr。从MySQL 8.0.22开始，N不能为NULL。此外,它必须是一个整数范围在1到2**63以及有下列形式:
1、一个无符号整数
2、常数文字位置参数标记(?)
3、一个用户定义的变量
4、存储例程中的局部变量
over_clause 12.21.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和缺省值1和NULL。第一行显示了LAG()没有前一行时的情况:该函数返回默认值(在本例中为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 |
+------+------+------+--------+-------------+

生成斐波那契数列初始集合的一种方法是使用递归公共表表达式。例如，参见斐波那契数列生成。从MySQL 8.0.22开始，这个函数的rows参数不能使用负值。

LAST_VALUE(expr) [null_treatment] over_clause
从窗口框架的最后一行返回expr的值。over_clause子句在12.21.2节窗口函数的概念和语法中有描述。Null_treatment在介绍部分中进行了描述。例如，请参阅FIRST_VALUE()函数描述。
LEAD(expr [, N[, default]]) [null_treatment] over_clause
在分区中，从当前行前面(后面)的N行返回expr的值。如果没有这样的行，则返回值为default。例如，如果N为3，则最后两行的返回值为默认值。如果缺少N或default，则缺省值分别为1和NULL。
N必须是一个非负整数。如果N为0，则对当前行计算expr。
从MySQL 8.0.22开始，N不能为NULL。此外，它现在必须是1到263的整数，可以是以下任何一种形式:
1、无符号整数常量字面值
2、位置参数标记(?)
3、一个用户定义的变量
4、存储例程中的局部变量
over_clause子句在12.21.2节“窗口函数的概念和语法”中有描述。Null_treatment在介绍部分中进行了描述。
有关示例，请参见LAG()函数描述。
在MySQL 8.0.22及以后版本中，这个函数的rows参数不允许使用负值。
NTH_VALUE(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子句在12.21.2节窗口函数的概念和语法中有描述。Null_treatment在介绍部分中进行了描述。例如，请参阅FIRST_VALUE()函数描述。在MySQL 8.0.22及以后版本中，您不能使用NULL作为这个函数的行参数。
NTILE(N) over_clause
将一个分区划分为N个组(桶)，为该分区中的每一行分配桶号，并返回该分区内当前行的桶号。例如，如果N是4,NTILE()将行分成4个桶。如果N是100,NTILE()将行分成100个桶。N必须是一个正整数。从MySQL 8.0.22开始，N不能为NULL。此外,它必须是：
1、无符号整数常量字面值
2、位置参数标记(?)
3、一个用户定义的变量
4、存储例程中的局部变量
这个函数应该使用命令行分割成所需的顺序进行排序。over_clause子句在12.21.2节窗口函数的概念和语法中有描述。下面的查询显示了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 |
+------+------------+--------+--------+

从MySQL 8.0.22开始，不再允许构造NTILE(NULL)。

PERCENT_RANK() over_clause
返回小于当前行中值的分区值的百分比，不包括最高值。返回值的范围从0到1，表示行相对排名，作为这个公式的结果计算，其中排名是行排名，行是分区行数：

(rank - 1) / (rows - 1)

这个函数应该与ORDER BY一起使用，以便将分区行按所需的顺序排序。如果没有ORDER BY，所有行都是对等的。over_clause子句在12.21.2节窗口函数的概念和语法中有描述。例子请参阅CUME_DIST()函数描述。

RANK() over_clause
返回当前行在其分区中的序号(带间隙)。同僚被认为是平手，得到相同的排名。如果存在大于1的组，则此函数不会将连续级别分配给对等组;结果是不连续的序号。这个函数应该与ORDER BY一起使用，以便将分区行按所需的顺序排序。如果没有ORDER BY，所有行都是对等的。over_clause子句在12.21.2节窗口函数的概念和语法中有描述。
下面的查询显示了RANK()和DENSE_RANK()之间的差异，前者生成有间隙的排名，后者生成没有间隙的排名。该查询显示val列中一组值的每个成员的级别值，其中包含一些重复值。RANK()给对等体(重复的)分配相同的秩值，下一个更大的值的秩比对等体的数目减1高。DENSE_RANK()也给对等节点分配相同的秩值，但是下一个更高的值的秩要大1。作为参考，该查询还使用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()为peer分配不同的行号。使用RANK()或DENSE_RANK()给对等体分配相同的值。例如，请参阅RANK()函数描述。over_clause子句在12.21.2节窗口函数的概念和语法中有描述。