窗口功能框架规范

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

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

• 通过将框架定义为从分区开始到当前行的所有行，您可以计算每一行的运行总计。
• 通过将框架定义为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指定在整个分区上运行的窗口函数不应具有frame子句。MySQL允许此类函数使用frame子句，但忽略它。即使指定了框架，这些函数也会使用整个分区：

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

如果给定frame子句，则其语法如下：

frame_clause:
    frame_units frame_extent

frame_units:
    {ROWS | RANGE}

在没有frame子句的情况下，默认框架取决于是否存在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 valunitINTERVALvalunitunitsDATE_ADD()

  RANGE上的数字或时间expr需要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。

在没有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，因此仅包含value的行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，因此仅包含value的行NULL。

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

  框架始于，NULL止于NULL，因此仅包含value的行NULL。

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

  该帧从分区的开头开始，在具有value的行处停止NULL。因为ASC排序将NULL值放在第一位，所以框架只是NULL值。