MySQL的函数和运算符 - 窗口函数 - 窗口函数帧规范

林员外聊编程 2021-09-25

423

窗口函数帧（Frame）规范

用窗口函数定义的窗口可以包含一个帧子句。帧是当前分区的一个子集，而帧子句指定了如何定义这个子集。

帧是相对于当前行确定的，这使得帧可以根据当前行在其分区中的位置在分区中移动。示例：

● 通过将一个帧定义为从分区开始到当前行的所有行，可以计算每一行的运行总数。

● 通过将帧定义为扩展到当前行两边的 N 行，可以计算滚动平均数。

下面的查询演示了如何使用移动帧来计算每组按时间顺序排列的层次值中的运行总数，以及从当前行及其前后紧接的行计算的滚动平均数：

mysql> SELECT
         time, subject, val,
         SUM(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS UNBOUNDED PRECEDING)
           AS running_total,
         AVG(val) OVER (PARTITION BY subject ORDER BY time
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
           AS running_average
       FROM observations;
+----------+---------+------+---------------+-----------------+
| time     | subject | val  | running_total | running_average |
+----------+---------+------+---------------+-----------------+
| 07:00:00 | st113   |   10 |            10 |          9.5000 |
| 07:15:00 | st113   |    9 |            19 |         14.6667 |
| 07:30:00 | st113   |   25 |            44 |         18.0000 |
| 07:45:00 | st113   |   20 |            64 |         22.5000 |
| 07:00:00 | xh458   |    0 |             0 |          5.0000 |
| 07:15:00 | xh458   |   10 |            10 |          5.0000 |
| 07:30:00 | xh458   |    5 |            15 |         15.0000 |
| 07:45:00 | xh458   |   30 |            45 |         20.0000 |
| 08:00:00 | xh458   |   25 |            70 |         27.5000 |
+----------+---------+------+---------------+-----------------+

对于 running_average 列，在第一行之前或最后一行之后没有帧行。在这些情况下，AVG() 计算可用行的平均值。

作为窗口函数使用的聚合函数对当前行框架中的行进行操作，这些非聚合窗口函数也是如此：

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

标准 SQL 指定在整个分区上操作的窗口函数应该没有帧子句。MySQL 允许这样的函数使用帧子句，但会忽略它。即使指定了帧，这些函数也会使用整个分区：

CUME_DIST()
DENSE_RANK()
LAG()
LEAD()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

如果给定帧子句，它的语法如下：

frame_clause:
    frame_units frame_extent


frame_units:
    {ROWS | RANGE}

在没有帧子句的情况下，默认帧取决于 ORDER BY 子句是否存在，如本部分后面所述。

frame_units 值表示当前行和帧行之间的关系类型：

● ROWS: 帧由行的开始和结束位置定义。偏移量是行数与当前行编号的差异。

● RANGE: 帧是由值范围内的行定义的。偏移量是行值与当前行值之间的差异。

frame_extent 值表示帧的开始和结束点。可以只指定帧的开始(在这种情况下，当前行代表隐式的结束)或使用 BETWEEN 来指定帧的两个端点：

frame_extent:
    {frame_start | frame_between}


frame_between:
    BETWEEN frame_start AND frame_end


frame_start, frame_end: {
    CURRENT ROW
  | UNBOUNDED PRECEDING
  | UNBOUNDED FOLLOWING
  | expr PRECEDING
  | expr FOLLOWING
}

使用 BETWEEN 语法，frame_start 不能出现在 frame_end 之后。

允许的 frame_start 和 frame_end 值有以下含义：

● CURRENT ROW: 对于 ROWS，边界是当前行。对于 RANGE，边界是当前行的对等行。

● UNBOUNDED PRECEDING: 边界是第一个分区行。

● UNBOUNDED FOLLOWING: 边界是最后一个分区行。

● expr PRECEDING: 对于 ROWS，边界是当前行之前的 expr 行。对于 RANGE，边界是值等于当前行值减去 expr 的行，如果当前行值为 NULL，则边界是该行的对等体。

对于 expr PRECEDING (以及 expr FOLLOWING)， expr 可以是 ? 参数标记(用于预编译语句)、非负数字面值或形式为 INTERVAL val unit 的时间间隔。对于 INTERVAL 表达式，val 指定了非负的间隔值，unit 关键字表示应该用哪个单位解释该值。

数字或时间 expr 上的 RANGE 要求分别在数字表达式或时间表达式上使用 ORDER BY。

有效的 expr PRECEDING 和 expr FOLLOWING 指示符示例：

10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING

● expr FOLLOWING: 对于 ROWS，边界是在当前行之后的 expr 行。对于 RANGE，边界是值等于当前行值加上 expr 的行，如果当前行值为 NULL，则边界是该行的对等体。

expr 的允许值请参见 expr PRECEDING 的描述。

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

在没有帧子句的情况下，默认帧取决于 ORDER BY 子句是否存在：

● 带有 ORDER BY：默认帧包括从分区开始到当前行的行，包括当前行的所有对等体(根据 ORDER BY 子句等于当前行的行)。

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

● 没有 ORDER BY：默认帧包括所有分区行(因为，没有 ORDER BY，所有分区行都是对等的)。

RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

由于默认帧因是否存在 ORDER BY 而不同，因此向查询中添加 ORDER BY 以获得确定性结果可能会改变结果。(例如，由 SUM() 产生的值可能会改变。)要获得相同的结果，但按 ORDER BY 排序，请提供一个显式的帧规范，无论 ORDER BY 是否存在。

当前行值为 NULL 时，帧规范的含义可能不明显。假设是这种情况，下面的例子说明了各种帧规范是如何应用的：

● ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING

帧从 NULL 开始，到 NULL 结束，因此只包含值为 NULL 的行。

● ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

该帧从 NULL 开始，并在分区的末尾停止。因为 ASC 排序将 NULL 值放在首位，所以帧就是整个分区。

● ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

该帧从 NULL 开始，并在分区的末尾停止。因为 DESC 排序将 NULL 值放在最后，所以帧只是 NULL 值。

● ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING

该帧从 NULL 开始，并在分区的末尾停止。因为 ASC 排序将 NULL 值放在首位，所以帧就是整个分区。

● ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING

帧从 NULL 开始，到 NULL 结束，因此只包含值为 NULL 的行。

● ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING

帧从 NULL 开始，到 NULL 结束，因此只包含值为 NULL 的行。

● ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING

帧从分区的开始处开始，在值为 NULL 的行处停止。因为 ASC 排序将 NULL 值放在首位，所以帧只是 NULL 值。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

mysql

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

MySQL的函数和运算符 - 窗口函数 - 窗口函数帧规范

评论