跳到主要内容

WINDOW 子句

描述​

在 SELECT 查询中，WINDOW 子句用于用于定义具有特定窗口规范的命名窗口。"窗口" 也称为 Frame，seekdb 同时支持 ROWS 与 RANGE 两种 Frame 语义，前者是基于物理行偏移的窗口，后者则是基于逻辑值偏移的窗口。

使用分析函数，并在它后面加上 OVER window_name 来引用窗口规范。适用于 OVER 子句的函数称为分析函数，有关分析函数的详细信息，请参见 函数概述 中 分析函数 章节。

语法​

window_clause:
    WINDOW window_definition_list

window_definition_list:
    window_definition [, window_definition ...]

window_definition:
    window_name AS ([PARTITION BY expression_list] [ORDER BY order_by_condition_list] [win_window])

expression_list:
    expression [, expression ...]

order_by_condition_list:
    order_by_condition [, order_by_condition ...]

order_by_condition:
    expression [ASC | DESC]

win_window:
    {ROWS | RANGE} BETWEEN win_bounding AND win_bounding
    | {ROWS | RANGE} win_bounding

win_bounding:
    CURRENT ROW
    | win_interval {PRECEDING | FOLLOWING}

win_interval:
    expression
    | INTERVAL expression date_unit

date_unit:
    DAY
    | DAY_HOUR
    | DAY_MICROSECOND
    | DAY_MINUTE
    | DAY_SECOND
    | HOUR
    | HOUR_MICROSECOND
    | HOUR_MINUTE
    | HOUR_SECOND
    | MICROSECOND
    | MINUTE
    | MINUTE_MICROSECOND
    | MINUTE_SECOND
    | MONTH
    | QUARTER
    | SECOND
    | SECOND_MICROSECOND
    | WEEK
    | YEAR
    | YEAR_MONTH

参数解释​

参数描述
WINDOW window_definition_list指定分析函数的窗口定义。window_definition_list 为窗口定义列表。
window_definition表示窗口定义，包括窗口名称、分组方式、排序方式和窗口范围。详细介绍可参见下文 window_definition。

window_definition​

  • window_name：指定窗口的名称，用于标识定义的窗口。

  • PARTITION BY expression_list：可选项，指定分析函数的分组键列表。它将输入数据根据指定的表达式列表进行分组，并对每个分组应用分析函数。这样可以在分析函数中对每个分组进行独立的计算，而不是对整个数据集进行计算。

    • expression：表示一个列或表达式。

  • ORDER BY order_by_condition_list：可选项，指定对分组后结果集的排序规则列表，定义窗口中数据的排序方式。

    • expression [ASC | DESC]：表示窗口定义的排序表达式。ASC | DESC 为可选参数，用于指定排序的顺序。ASC 表示升序（默认），DESC 表示降序。

  • win_window：可选项，指定窗口范围的定义，包括 ROWS 或 RANGE 的边界条件。详细介绍可参见下文 win_window。

win_window​

  • {ROWS | RANGE} BETWEEN win_bounding AND win_bounding：表示窗口的边界由两个边界值决定。具体如下：

    • ROWS | RANGE：指定 Frame 语义。

      • ROWS：表示物理窗口，根据 ORDER BY 子句排序后，取的前 N 行及后 N 行的数据计算。
      • RANGE：表示逻辑窗口，指定当前行对应值的范围取值。

    • win_bounding：表示窗口范围的边界条件，可以是当前行或者相对当前行的间隔。详细介绍可参见下文 win_bounding。

  • {ROWS | RANGE} win_bounding：表示窗口的边界由单个边界值决定。

win_bounding​

  • CURRENT ROW：表示使用当前行作为窗口的边界。

  • win_interval {PRECEDING | FOLLOWING}：表示在对当前行进行计算时，指定相对位置是在当前行之前还是之后。用于指定窗口的开始和结束位置。具体如下：

    • win_interval：定义窗口的间隔大小，可以是一个固定值、未指定的值、小数值、无限制、或者某种日期单位。

      • expression：表示窗口的起始位置。取值可以是一个整数，表示行数的偏移量；也可以是特定关键词，如下所示：

        • UNBOUNDED PRECEDING：表示窗口起始位置为无边界，即从最开始的行开始计算。
        • UNBOUNDED FOLLOWING：表示窗口结束位置为无边界，即到最后的行结束计算。
        • CURRENT ROW：表示窗口的起始位置为当前行。

      • INTERVAL expression date_unit：表示时间间隔的定义，包括一个表达式和时间单位。

        提示

        • seekdb 当前版本 INTERVAL 子句只能与 RANGE 一起使用。

        • 在使用 ORDER BY 子句来定义窗口中数据的排序方式时，需要确保窗口定义的排序表达式是日期格式。

        • expression：用于指定间隔表达式。取值可以是任意整数，表示间隔的数量。
        • date_unit：指定间隔的单位，取值可以是 YEAR、MONTH、DAY、HOUR 等。

    • PRECEDING：使用前 N 行。

    • FOLLOWING：使用后 N 行。

示例​

查询 test_tbl1 表中指定列的数据，并在每行中计算出不同窗口条件下 col5 的总和。

  1. 创建表 test_tbl1。

    CREATE TABLE test_tbl1 (col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      col2 INT,
      col3 VARCHAR(50),
      col4 DATE,
      col5 INT);

  2. 插入测试数据。

    INSERT INTO test_tbl1(col2, col3, col4, col5)
      VALUES(100, 'A1', '2017-01-01', 120),
            (100, 'A1', '2018-01-01', 20),
            (100, 'A1', '2019-01-01', 100),
            (100, 'A1', '2020-01-01', 40),
            (100, 'B1', '2021-01-01', 80),
            (100, 'B1', '2022-01-01', 60),
            (200, 'B1', '2017-01-01', 70),
            (200, 'B1', '2018-01-01', 50),
            (200, 'C1', '2019-01-01', 90),
            (200, 'C1', '2020-01-01', 30),
            (200, 'C1', '2021-01-01', 110),
            (200, 'C1', '2022-01-01', 10);

  3. 从 test_tbl1 表中选择 col1、col3 和 col5 列，然后使用分析函数计算不同窗口条件下 col5 的总和。最后按照 col1 的值进行升序输出结果。窗口条件包括不同的 PARTITION BY 和 ORDER BY 子句，以及 ROWS 和 RANGE 子句，具体如下：

    1. my_window_1 没有特定的条件，因此整个表被视为一个窗口。sum1 计算了整个表中 col5 的总和。
    2. my_window_2 根据 col3 进行分区。sum2 计算了每个 col3 分区中 col5 的总和。
    3. my_window_3 根据 col5 的值进行排序。sum3 计算了在每一行之前（包括当前行）的 col5 的总和。
    4. my_window_4 在 col3 分区内，按照 col5 的顺序，将行设置为不指定。sum4 计算了每个 col3 分区中根据 col5 排序的 col5 的总和。
    5. my_window_5 在 col3 分区内，按照 col5 的顺序，将行设置为无界定的。sum5 计算了在每个 col3 分区中 col5 的总和。
    6. my_window_6 在 col3 分区内，按照 col5 的顺序，将当前行设置为当前行。sum6 计算了在每个 col3 分区中当前行的 col5 的总和。
    7. my_window_7 在 col3 分区内，按照 col5 的顺序，将行设置为比当前行 1 个行。sum7 计算了在每个 col3 分区中当前行和前一行的 col5 的总和。
    8. my_window_8 在 col3 分区内，按照 col5 的顺序，将行设置为前 1 行和后 1 行。sum9 计算了在每个 col3 分区中前 1 行到后 1 行 col5 的总和。
    9. my_window_9 在 col3 分区内，按照 col4 的顺序，将行设置为前 1 年和后 1 年。sum10 计算了在每个 col3 分区中前 1 年到后 1 年 col5 的总和。
    SELECT col1, col3, col4, col5,
          SUM(col5) OVER my_window_1 AS sum1,
          SUM(col5) OVER my_window_2 AS sum2,
          SUM(col5) OVER my_window_3 AS sum3,
          SUM(col5) OVER my_window_4 AS sum4,
          SUM(col5) OVER my_window_5 AS sum5,
          SUM(col5) OVER my_window_6 AS sum6,
          SUM(col5) OVER my_window_7 AS sum7,
          SUM(col5) OVER my_window_8 AS sum8,
          SUM(col5) OVER my_window_9 AS sum9
      FROM test_tbl1
      WINDOW
        my_window_1 AS (),
        my_window_2 AS (PARTITION BY col3),
        my_window_3 AS (ORDER BY col5),
        my_window_4 AS (PARTITION BY col3 ORDER BY col5),
        my_window_5 AS (PARTITION BY col3 ORDER BY col5 ROWS UNBOUNDED PRECEDING),
        my_window_6 AS (PARTITION BY col3 ORDER BY col5 ROWS CURRENT ROW),
        my_window_7 AS (PARTITION BY col3 ORDER BY col5 ROWS 1 PRECEDING),
        my_window_8 AS (PARTITION BY col3 ORDER BY col5 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
        my_window_9 AS (PARTITION BY col3 ORDER BY col4 RANGE BETWEEN INTERVAL 1 YEAR PRECEDING AND INTERVAL 1 YEAR FOLLOWING)
      ORDER BY col1;

    返回结果如下：

    +------+------+------------+------+------+------+------+------+------+------+------+------+------+
    | col1 | col3 | col4       | col5 | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum7 | sum8 | sum9 |
    +------+------+------------+------+------+------+------+------+------+------+------+------+------+
    |    1 | A1   | 2017-01-01 |   10 |  780 |  100 |   10 |   10 |   10 |   10 |   10 |   30 |   30 |
    |    2 | A1   | 2018-01-01 |   20 |  780 |  100 |   30 |   30 |   30 |   20 |   30 |   60 |   60 |
    |    3 | A1   | 2019-01-01 |   30 |  780 |  100 |   60 |   60 |   60 |   30 |   50 |   90 |   90 |
    |    4 | A1   | 2020-01-01 |   40 |  780 |  100 |  100 |  100 |  100 |   40 |   70 |   70 |   70 |
    |    5 | B1   | 2021-01-01 |   50 |  780 |  260 |  150 |   50 |   50 |   50 |   50 |  110 |  110 |
    |    6 | B1   | 2022-01-01 |   60 |  780 |  260 |  210 |  110 |  110 |   60 |  110 |  180 |  110 |
    |    7 | B1   | 2017-01-01 |   70 |  780 |  260 |  280 |  180 |  180 |   70 |  130 |  210 |  150 |
    |    8 | B1   | 2018-01-01 |   80 |  780 |  260 |  360 |  260 |  260 |   80 |  150 |  150 |  150 |
    |    9 | C1   | 2019-01-01 |   90 |  780 |  420 |  450 |   90 |   90 |   90 |   90 |  190 |  190 |
    |   10 | C1   | 2020-01-01 |  100 |  780 |  420 |  550 |  190 |  190 |  100 |  190 |  300 |  300 |
    |   11 | C1   | 2021-01-01 |  110 |  780 |  420 |  660 |  300 |  300 |  110 |  210 |  330 |  330 |
    |   12 | C1   | 2022-01-01 |  120 |  780 |  420 |  780 |  420 |  420 |  120 |  230 |  230 |  230 |
    +------+------+------------+------+------+------+------+------+------+------+------+------+------+
    12 rows in set (0.212 sec)

相关文档​

SELECT

Contents

编辑此页