12.21.3 窗函数框架规范

# 12.21.3 窗函数框架规范

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

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

通过将框架定义为从分区开始到当前行的所有行，您可以计算每行的运行总计。
通过将框架定义为扩展*ñ*当前行两侧的行，您可以计算滚动平均值。

以下查询演示了使用移动帧来计算每组按时间排序的运行总计等级值，以及从当前行和紧随其前后的行计算的滚动平均值：

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 |
+----------+---------+------+---------------+-----------------+

为了运行平均列，在第一个之前或最后一个之后没有框架行。在这些情况下，平均（）计算可用行的平均值。

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

FIRST_VALUE()
LAST_VALUE()
NTH_VALUE()

标准 SQL 指定对整个分区进行操作的窗口函数不应有框架子句。MySQL 允许此类函数的框架子句但忽略它。即使指定了框架，这些函数也会使用整个分区：

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

如果给出了 frame 子句，则具有以下语法：

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

在没有框架子句的情况下，默认框架取决于是否订购方式子句存在，如本节后面所述。

这*帧单位*value 表示当前行和框架行之间的关系类型：

行：框架由开始和结束行位置定义。偏移量是行号与当前行号的差异。
范围：框架由值范围内的行定义。偏移量是行值与当前行值的差异。

这*frame_extent*value 表示帧的起点和终点。您可以仅指定帧的开始（在这种情况下，当前行隐式结束）或使用之间指定两个框架端点：

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
}

和之间句法，frame_start不得晚于帧结束.

允许的*frame_start和帧结束*值具有以下含义：

当前行：为了行，边界是当前行。为了范围，边界是当前行的对等点。
前无界：边界是第一个分区行。
无界跟随：边界是最后一个分区行。
*表达式* 前文：为了行, 界限是*表达式当前行之前的行。为了范围，边界是值等于当前行值减去的行表达式*;如果当前行值为空值，边界是行的对等点。

为了*表达式* 前文（和*表达式* 下列的),*表达式可以是一个?参数标记（用于准备好的语句），非负数字文字，或形式的时间间隔间隔 *值* *单元*.为了间隔表达式，值指定非负区间值，并且单元是一个关键字，表示值应该被解释的单位。（有关允许的详细信息单位*说明符，请参阅DATE_ADD()作用于第 12.7 节，“日期和时间函数”.)

范围在数字或时间上*表达式*需要订购方式分别在数字或时间表达式上。

有效的例子*表达式* 前文和*表达式* 下列的指标：
```
10 PRECEDING
INTERVAL 5 DAY PRECEDING
5 FOLLOWING
INTERVAL '2:30' MINUTE_SECOND FOLLOWING
```
*表达式* 下列的：为了行, 界限是*表达式当前行之后的行。为了范围，边界是值等于当前行值加上的行表达式*;如果当前行值为空值，边界是行的对等点。

对于允许的值*表达式*，见描述*表达式* 前文.

以下查询演示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()调用时，当前帧并不总是包含请求的行；在这种情况下，返回值为空值.

在没有框架子句的情况下，默认框架取决于是否订购方式子句存在：

和订购方式：默认帧包括从分区开始到当前行的行，包括当前行的所有对等点（行等于当前行根据订购方式条款）。默认等效于此帧规范：
```
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
```
没有订购方式：默认框架包括所有分区行（因为，没有订购方式，所有分区行都是对等的）。默认等效于此帧规范：
```
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
```
因为默认框架根据是否存在而有所不同订购方式, 添加订购方式查询以获取确定性结果可能会更改结果。（例如，由和（）可能会改变。）要获得相同的结果，但按顺序排序订购方式, 提供一个明确的框架规范，无论是否订购方式存在。

当当前行值为空值.假设是这种情况，这些示例说明了各种框架规范是如何应用的：
10 个以下和 15 个以下之间的按 X ASC 范围排序

帧开始于空值并停在空值，因此仅包括具有值的行空值.
ORDER BY X ASC 范围在 10 个以下和无界以下之间

帧开始于空值并停在分区的末尾。因为一个ASC排序放空值值第一，框架是整个分区。
ORDER BY X DESC 范围在 10 个以下和无界以下之间

帧开始于空值并停在分区的末尾。因为一个DESC排序放空值值最后，框架只是空值价值观。
ORDER BY X ASC 范围在 10 之前和无界之后

帧开始于空值并停在分区的末尾。因为一个ASC排序放空值值第一，框架是整个分区。
前 10 和后 10 之间的按 X ASC 范围排序

帧开始于空值并停在空值，因此仅包括具有值的行空值.
前 10 和前 1 之间的按 X ASC 范围排序

帧开始于空值并停在空值，因此仅包括具有值的行空值.
无界前和后 10 之间按 X ASC 范围排序

框架从分区的开头开始，在有值的行处停止空值.因为一个ASC排序放空值值第一，框架只是空值价值观。

← 12.21.2 窗口函数概念和语法 12.21.4 命名窗口 →