MySQL :: MySQL 8.4 Reference Manual :: 14.20.3 Window Function Frame Specification

version 8.4

8.0 current

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Window Function Frame Specification

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 前: 对于ROWS,边界是当前行前expr行。对于RANGE,边界是当前行值等于当前行值减去expr的行；如果当前行值为NULL,边界是当前行的同伴行。

对于expr 前 (和expr 后),expr可以是?参数标记符（用于预备语句）、非负整数字面量或时间间隔形式的INTERVAL val unit。对于INTERVAL表达式,val指定非负间隔值，unit是指示值应被解释的单位的关键字。 (有关units指定符的详细信息，请参阅DATE_ADD()函数的描述在第14.7节，“日期和时间函数”中。)

RANGE在数值或时间expr上要求ORDER BY在数值或时间表达式上，分别。

以下是有效的expr 前和expr 后指示符的示例：
```
10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING
```
expr 后: 对于ROWS,边界是当前行后expr行。对于RANGE,边界是当前行值等于当前行值加上expr的行；如果当前行值为NULL,边界是当前行的同伴行。

有关expr的允许值，请参阅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。

在缺少frame子句时，缺省框架取决于是否存在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 值。

PREV HOME UP NEXT