MySQL窗口函数简介「建议收藏」

全栈程序员站长

发布于 2022-11-10 15:19:02

1.3K0

发布于 2022-11-10 15:19:02

文章被收录于专栏：全栈程序员必看

大家好，又见面了，我是你们的朋友全栈君。

原文地址：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_last-value

译文：

12.21.1 Window Function Descriptions

本节描述非聚合窗口函数，对于查询中的每一行，这些函数使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数，具体可以参考Section 12.20.1, “Aggregate (GROUP BY) Function Descriptions”。

有关窗口函数使用信息和示例，以及术语的定义，如OVER语句、窗口、分区、框架和对等项，请参考Section 12.21.2, “Window Function Concepts and Syntax”.

Table 12.27 Window Functions

Name	Description
CUME_DIST()	Cumulative distribution value
DENSE_RANK()	Rank of current row within its partition, without gaps
FIRST_VALUE()	Value of argument from first row of window frame
LAG()	Value of argument from row lagging current row within partition
LAST_VALUE()	Value of argument from last row of window frame
LEAD()	Value of argument from row leading current row within partition
NTH_VALUE()	Value of argument from N-th row of window frame
NTILE()	Bucket number of current row within its partition.
PERCENT_RANK()	Percentage rank value
RANK()	Rank of current row within its partition, with gaps
ROW_NUMBER()	Number of current row within its partition

在下面的函数描述中，over_clause即Section 12.21.2, “Window Function Concepts and Syntax”中描述的OVER语句。一些窗口函数允许使用null_treatment子句，该子句指定在计算结果时如何处理NULL值。这个子句是可选的。它是SQL标准的一部分，但是MySQL实现只允许RESPECT NULLS(这也是默认值)。这意味着在计算结果时要考虑NULL值。IGNORE NULLS也可以被解析，但会产生错误。

1）CUME_DIST over_clause：

返回一组值中某个值的累积分布，即分区值小于或等于当前行的值的百分比。它表示窗口分区的窗口顺序中在当前行之前或与当前行对等的行数，除以窗口分区中的总行数。返回值的范围从0到1。这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。如果没有ORDER BY，所有行都是对等的，值N/N = 1，其中N是分区大小。

Section 12.21.2, “Window Function Concepts and Syntax”中有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 |
+------+------------+--------------------+--------------+

2）DENSE_RANk over_clause：

返回分区内当前行的排名，没有间隔。对等项被认为是平起平坐的，得到同样的排名。该函数将连续的排名分配给对等组，结果是，大小大于1的组不会产生不连续的排名。具体示例，请参见RANK()函数描述。这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。没有ORDER BY，所有行都是对等的。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

3）FIRST_VALUE(expr) [null_treatment] over_clause：

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

Section 12.21.2, “Window Function Concepts and Syntax”中有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。

4）LAG(expr [, N[, default]]) [null_treatment] over_clause：

在分区中，从比当前行落后N行的行返回expr的值。如果没有这样的行，则返回值为默认值。例如，如果N是3，则前三行的返回值是默认值(原文说的是如果N是3，则前两行的返回值是默认值，笔者经测试发现是错的。译文中已将其改正)。如果缺少N或default，则默认值分别为1和NULL。N必须是非负整数。如果N为0，则对当前行计算expr。

Section 12.21.2, “Window Function Concepts and Syntax”中有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和默认值1和NULL。

第一行显示了当当前行没有前一行时LAG()的返回值情况：函数返回默认值(在本例中为NULL)。最后一行显示相同的内容，当当前行没有下一行时LEAD()返回NULL值。

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 Series Generation。

5）LAST_VALUE(expr) [null_treatment] over_clause：

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

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

具体示例，请参见前文的FIRST_VALUE()函数描述。

6）LEAD(expr [, N[, default]]) [null_treatment] over_clause：

从在其分区中超前当前行N行的行返回expr的值。如果没有这样的行，则返回值为默认值。例如，如果N是3，则最后三行的返回值是默认值(原文说的是如果N是3，则最后两行的返回值是默认值，笔者经测试发现是错的。译文中已将其改正)。如果缺少N或default，则默认值分别为1和NULL。

N必须是非负整数。如果N为0，则对当前行计算expr。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

具体示例，请参见前文的LAG()函数描述。

7）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按相反顺序排序。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。null_treatment如介绍部分所述。

具体示例，请参见前文的FIRST_VALUE()函数描述。

8）NTILE(N) over_clause：

将一个分区分成N组(bucket)，为分区中的每一行分配其bucket号，并返回分区中当前行的bucket号。例如，如果N是4,NTILE()将行分成4个bucket。如果N是100,NTILE()将行分成100个bucket。

N必须是一个正整数。bucket号返回值的范围从1到N。

这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。

Section 12.21.2, “Window Function Concepts and Syntax”中有关于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 |
+------+------------+--------+--------+

9）PERCENT_RANK() over_clause：

返回分区值小于当前行的值的百分比，不包括最大值。返回值范围为0 ~ 1，表示行相对排名，根据公式(rank – 1) / (rows – 1)计算得到，其中rank为行排名，row为分区行数：

(rank - 1) / (rows - 1)

10）RANK() over_clause：

返回分区中当前行的排名，并带有间隔。对等行被认为是平起平坐的，得到同样的排名。如果存在大小大于一的组，则此函数不为对等组分配连续的排名；结果是不连续的排名。这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。没有ORDER BY，所有行都是对等的。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

下面的查询显示了RANK()和DENSE_RANK()之间的区别，RANK()生成有间隔的排名，DENSE_RANK()生成没有间隔的排名。查询显示val列中一组值的每个成员的排名，其中包含一些重复值。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 |
+------+------------+------+------------+

11）ROW_NUMBER() over_clause：

返回分区中当前行的编号。行号的范围从1到分区行数。

ORDER BY影响行编号的顺序。没有ORDER BY，行编号是不确定的。

ROW_NUMBER()为对等行分配不同的行号。若要为对等行分配相同的值，请使用RANK()或DENSE_RANK()。具体示例，请参见前文的RANK()函数描述。

Section 12.21.2, “Window Function Concepts and Syntax”中有over_clause的描述。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~我是分割线~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

下一篇：https://blog.csdn.net/qq_41080850/article/details/86416215(MySQL窗口函数概念和语法)

PS：水平有限，译文中难免存在谬误，欢迎批评指正。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/185768.html原文链接：https://javaforall.cn

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022年10月5日，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

编程算法

登录后参与评论

0 条评论

热度