MySQL的函数和运算符 - 窗口函数 - 窗口函数介绍

数据库杂货铺 2021-09-23

996

窗口函数介绍

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

窗口函数

名称	介绍
CUME_DIST()	累积分布值
DENSE_RANK()	当前行在其分区内的排名，没有间隙
FIRST_VALUE()	来自窗口框架内第一行的参数值
LAG()	分区内滞后参数当前行的值
LAST_VALUE()	参数在窗口框架内的最后一行的值
LEAD()	参数值在分区内领先当前行的行值
NTH_VALUE()	窗口框架内参数在第N行的值
NTILE()	当前行在其分区内的桶号
PERCENT_RANK()	百分位数排名值
RANK()	当前行在其分区内的排名，可能是不连续
ROW_NUMBER()	在其分区内的当前行数

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

● CUME_DIST() over_clause

返回一个值在一组值中的累积分布，即小于或等于当前行的值在分区值的百分比。这表示窗口分区的窗口排序中小于等于当前行的行数除以窗口分区中的总行数的值。返回值的范围从 0 到 1。

这个函数应该与 ORDER BY 一起使用，以便按照所需的顺序对分区行进行排序。如果没有 ORDER BY，所有行都是平等的，值为 N/N = 1，其中 N 是分区大小。

下面的查询显示了 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的组，不会产生不连续的排名数。

此函数应与 ORDER BY 一起使用，以便按照所需的顺序对分区行进行排序。没有 ORDER BY，所有行都是平等的。

● FIRST_VALUE(expr) [null_treatment] over_clause

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

下面的查询演示了 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

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

N 必须是一个非负整数字面量。如果 N 为 0，则为当前行 expr。

从 MySQL 8.0.22 开始，N 不能是 NULL。此外，它现在必须是 1 到 2^63 之间的整数，包括以下任何形式：

■ 无符号整型常量字面值

■ 位置参数标记(?)

■ 用户定义的变量

■ 存储例程中的局部变量

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 和 default 值，即 1 和 NULL。

第一行显示了当 LAG() 没有前一行时会发生什么：函数返回 default 值(在本例中为 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 |
+------+------+------+--------+-------------+

生成初始斐波那契数集的一种方法是使用递归公共表表达式。

从 MySQL 8.0.22 开始，这个函数的行参数不能使用负值。

● LAST_VALUE(expr) [null_treatment] over_clause

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

示例请参见 FIRST_VALUE() 函数描述。

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

返回分区内领先当前行 N 行的 expr 值。如果没有这样的行，则返回值为 default。例如，如果 N 为 3，则最后两行的返回值为 default。如果缺少 N 或 default，则默认值分别为 1 和 NULL。

N 必须是一个非负整数字面量。如果 N 为 0，则计算当前行 expr。

从 MySQL 8.0.22 开始，N 不能是 NULL。此外，它现在必须是 1 到 2^63 之间的整数，包括以下任何形式：

■ 无符号整型常量字面值

■ 位置参数标记(?)

■ 用户定义变量

■ 存储例程中的局部变量

示例请参阅 LAG() 函数描述。

在 MySQL 8.0.22 及以后版本中，不允许对这个函数的行参数使用负值。

● 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 以相反的顺序排序。

示例请参见 FIRST_VALUE() 函数描述。

在 MySQL 8.0.22 和更高版本中，不能使用 NULL 作为这个函数的行参数。

● NTILE(N) over_clause

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

N 必须是一个正整数。桶号返回值的范围是 1 ~ N。

从 MySQL 8.0.22 开始，N 不能是 NULL。取值范围为 1 ~ 2^63的整数，支持以下任意一种形式：

■ 无符号整型常量字面值

■ 位置参数标记(?)

■ 用户定义变量

■ 存储例程中的局部变量

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

下面的查询显示，对于 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 是行等级，rows 是分区行数：

(rank - 1)  (rows - 1)

这个函数应该与 ORDER BY 一起使用，以便按照所需的顺序对分区行进行排序。没有 ORDER BY，所有行都是平等的。

示例请参见 CUME_DIST() 函数描述。

● RANK() over_clause

返回当前行在其分区内的排序(可能不连续)。相等值获得相同的排名。如果存在大小大于 1 的组，该函数不会为对等组分配连续的排序，结果是不连续的排序数。

这个函数应该与 ORDER BY 一起使用，以便按照所需的顺序对分区行进行排序。没有 ORDER BY，所有行都是平等的。

下面的查询显示 RANK() 和 DENSE_RANK() 之间的差异，RANK() 产生有间隙的排名，DENSE_RANK() 产生没有间隙的排名。查询显示 val 列中一组值的每个成员的排名值，其中包含一些重复值。RANK() 给对等体(重复值)分配相同的排名值。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() 给平等行分配不同的行编号。要给平等行分配相同的值，请使用 RANK() 或 DENSE_RANK()。示例请参阅 RANK() 函数描述。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 窗口函数 - 窗口函数介绍

评论