• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 窗口功能框架规范

    与窗口函数一起使用的窗口的定义可以包含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_startframe_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 PRECEDINGexpr 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值。