MySQL :: MySQL 8.4 参考手册 :: 14.20.3 窗口函数帧规范

版本 8.4

9.0
8.0

8.0 日本语

MySQL 8.4 参考手册 / ... / 窗口函数帧规范

14.20.3 窗口函数帧规范

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

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

通过将帧定义为从分区开始到当前行的所有行，可以为每一行计算累计总计。
通过将帧定义为从当前行的两侧扩展 N 行，可以计算滚动平均值。

以下查询演示了使用移动帧在每个时间排序的 level 值组中计算累计总计，以及从当前行及其前后紧邻的行计算滚动平均值

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 是一个关键字，表示应以其为单位来解释该值。（有关允许的 units 说明符的详细信息，请参阅 DATE_ADD() 函数在第 14.7 节，“日期和时间函数” 中的描述。）
在数值或时间 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 值。