窗口功能框架规范
与窗口函数一起使用的窗口的定义可以包含frame子句。框架是当前分区的子集,而frame子句指定如何定义子集。
相对于当前行确定帧,这使帧可以根据分区中当前行的位置在分区中移动。例子:
- 通过将框架定义为从分区开始到当前行的所有行,您可以计算每一行的运行总计。
- 通过将框架定义为
N
当前行两侧的扩展行,可以计算滚动平均值。
以下查询演示了如何使用移动帧来计算每组按时间排序的level
值内的运行总计,以及从当前行以及紧接其前后的行计算出的滚动平均值:
mysql>SELECT time,subject , val, SUM(val)OVER (PARTITION BY subject ORDER BY timeROWS UNBOUNDED PRECEDING )AS running_total, AVG(val)OVER (PARTITION BY subject ORDER BY timeROWS BETWEEN 1PRECEDING AND 1FOLLOWING )AS running_averageFROM 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 | exprPRECEDING | exprFOLLOWING }
使用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 valunit
INTERVAL
val
unit
units
DATE_ADD()
RANGE
上的数字或时间expr
需要ORDER BY
分别上的数字或时间表达。有效
expr PRECEDING
和expr FOLLOWING
指标示例:10
PRECEDING INTERVAL 5 DAYPRECEDING 5FOLLOWING 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 wAS 'first', LAST_VALUE(val)OVER wAS 'last', NTH_VALUE(val, 2)OVER wAS 'second', NTH_VALUE(val, 4)OVER wAS 'fourth'FROM observationsWINDOW wAS (PARTITION BY subject ORDER BY timeROWS 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 BETWEENUNBOUNDED PRECEDING ANDCURRENT ROW 不使用
ORDER BY
:默认框架包括所有分区行(因为如果不使用ORDER BY
,则所有分区行都是对等的)。默认值等同于以下框架规范:RANGE BETWEENUNBOUNDED PRECEDING ANDUNBOUNDED 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
值。