MySQL :: MySQL 9.0 参考手册 :: 14.20.3 窗口函数框架规范

版本 9.0

8.4 当前
8.0

8.0 日语

MySQL 9.0 参考手册 / ... / 窗口函数框架规范

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 说明符的详细信息，请参阅第 14.7 节“日期和时间函数”中 DATE_ADD() 函数的说明。）
数字或时间 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 值。