MySQL窗口函数框架说明(frame子句)

短命女 2022-03-27 11:14 353阅读 0赞

原文地址：[https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html][https_dev.mysql.com_doc_refman_8.0_en_window-functions-frames.html]

译文：

### 12.21.3 Window Function Frame Specification ###

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

框架是相对于当前行确定的，这使得框架可以根据当前行在分区中的位置在分区中移动。例如：

1）通过将一个框架定义为从分区开始到当前行的所有行，可以计算截至当前行的累积和；

2）通过将框架定义为在当前行的任意一侧扩展N行，可以计算滚动平均。

下面的查询演示了如何使用移动框架来计算每组中按时间顺序排列的值的累积和，以及当前行和当前行前后若干行的滚动平均值：

*      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值表示当前行和框架行之间的关系类型：

1）ROWS：框架由开始行和结束行的位置来定义；

2）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值具有以下含义：

1）CURRENT ROW：对于行来说，界限是当前行。对于范围来说，界限是当前行的对等行(值相等的行)；

2）UNBOUNDED PRECEDING：界限是第一个分区行；

3）UNBOUNDED FOLLOWING：界限是最后一个分区行；

4）expr PRECEDING：对于行来说，界限是当前行之前的expr行。对于范围来说，界限是值等于当前行值减去expr的行；如果当前行值为NULL，则界限为该行的对等行；

对于expr PRECEDING(和expr FOLLOWING)， expr可以是?参数标记(用于准备语句中)、非负数值文字或INTERVAL val unit这种形式的时间区间 。对于INTERVAL的表达式来说，val指定非负区间值，而unit是一个关键字，表明应该在区间中解释该值的单元。(有关允许的单位说明符的详细信息，请参见[Section 12.7, “Date and Time Functions”][Section_12.7_ _Date and Time Functions]中DATE\_ADD()函数的描述)。

数值或时间表达式上的RANGE分别要求对数值或时间表达式按顺序进行排序。

有效的expr PRECEDING和expr FOLLOWING指示符示例：

*      10 PRECEDING
        INTERVAL 5 DAY PRECEDING
        5 FOLLOWING
        INTERVAL '2:30' MINUTE_SECOND FOLLOWING

5）expr FOLLOWING：对于行来说，界限是当前行之后的expr行。对于范围来说，界限是值等于当前行值加上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 |
        +----------+---------+------+-------+------+--------+--------+
        
        # 对查询结果的解释：
        # 上述查询中window语句中的frame子句为ROWS UNBOUNDED PRECEDING，这条frame子句指定框架的起点是
        # 分区的第一行，由于没有指定框架结束的位置，所以当前行是隐式的框架终点。
        # 因此对于查询结果中的first和last列来说，两个分区中框架的第一行是一直不变的，但框架的最后一行会
        # 随着当前行的变化而变化。同理，对于second和fourth列来说，只有框架里的行数达到了两行和四行时，
        # NTH_VALUE()才不会返回NULL值。

每个函数都使用当前框架中的行，根据显示的窗口定义，当前行从第一个分区行扩展到当前行。对于NTH\_VALUE()调用，当前框架并不总是包含所请求的行；在这种情况下，NTH\_VALUE的返回值为NULL。

在缺少frame子句的情况下，默认框架取决于ORDER BY子句是否存在：

1）有ORDER BY的情况：默认框架包括从分区起始行到当前行的行，包括当前行的所有对等行(根据ORDER BY子句确定的值等于当前行的行)。默认情况相当于下面这个框架说明：

*      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

2）没有ORDER BY的情况：默认框架包括所有分区行(因为没有ORDER BY，所有分区行都是对等的)。默认情况相当于下面这个框架说明：

*      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

由于默认框架的不同取决于ORDER BY的存在与否，因此向查询中添加ORDER BY以获得确定性结果可能会更改原来的结果。(例如，SUM()生成的值可能会改变)。要获得相同的结果，但按ORDER BY排序，可以提供一个显式的框架说明，无论ORDER BY是否存在，都将使用该说明。

当当前行值为NULL时，框架说明的含义可能不明显。假设是这样，下面这些例子说明了各种框架说明是如何应用的：

1）ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND 15 FOLLOWING

框架从NULL值开始并在NULL值处停止，因此只包含值为NULL的行。

2）ORDER BY X ASC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

框架从NULL值开始，在分区的末尾停止。因为ASC排序将NULL值放在首位，所以框架就是整个分区。

3）ORDER BY X DESC RANGE BETWEEN 10 FOLLOWING AND UNBOUNDED FOLLOWING

框架从NULL值开始，在分区的末尾停止。因为DESC排序将空值放在最后，所以框架只是空值。

4）ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND UNBOUNDED FOLLOWING

框架从NULL值开始，在分区的末尾停止。因为ASC排序将NULL值放在首位，所以框架就是整个分区。

5）ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING

框架从NULL值开始并在NULL值处停止，因此只包含值为NULL的行。

6）ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 1 PRECEDING

框架从NULL值开始并在NULL值处停止，因此只包含值为NULL的行。

7）ORDER BY X ASC RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING

框架从分区的开始处开始，在值为NULL的行处停止。因为ASC排序将空值放在首位，所以框架只是空值。

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~我是分割线~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

下一篇：[https://blog.csdn.net/qq\_41080850/article/details/86416319][https_blog.csdn.net_qq_41080850_article_details_86416319](MySQL命名窗口及窗口函数的限制)

PS：由于水平有限，译文中难免存在谬误，欢迎批评指正。

[https_dev.mysql.com_doc_refman_8.0_en_window-functions-frames.html]: https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html
[Section_12.7_ _Date and Time Functions]: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
[https_blog.csdn.net_qq_41080850_article_details_86416319]: https://blog.csdn.net/qq_41080850/article/details/86416319