WINDOW clause

Purpose

In a SELECT query, the WINDOW clause is used to define named windows with specific window specifications. A "window" is also referred to as a Frame. OceanBase Database supports both ROWS and RANGE Frame semantics. The ROWS Frame is based on physical row offsets, while the RANGE Frame is based on logical value offsets.

To reference a window specification, use an analytic function followed by OVER window_name. Functions applicable to the OVER clause are called analytic functions. For more information about analytic functions, see the Analytic functions section in Overview of functions.

Syntax

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

Parameters

window_definition

• window_name: Specifies the name of the window, used to identify the defined window.

• PARTITION BY expression_list: Optional. Specifies the grouping keys for the analytic function. It groups the input data based on the specified expressions and applies the analytic function to each group. This allows for independent calculations within each group instead of across the entire dataset.

  • expression: Represents a column or expression.

• ORDER BY order_by_condition_list: Optional. Specifies the sorting rules for the grouped result set, defining the sorting method within the window.

  • expression [ASC | DESC]: Represents the sorting expression for the window. ASC | DESC are optional parameters that specify the sorting order. ASC indicates ascending (default), and DESC indicates descending.

• win_window: Optional. Specifies the window range definition, including the boundary conditions for ROWS or RANGE. For more information, see win_window below.

win_window

• {ROWS | RANGE} BETWEEN win_bounding AND win_bounding: Specifies the window boundaries determined by two boundary values. Details are as follows:

  • ROWS | RANGE: Specifies the Frame semantics.

    • ROWS: Represents a physical window. After sorting by the ORDER BY clause, it calculates data from the preceding N rows and the following N rows.
    • RANGE: Represents a logical window. It specifies the value range corresponding to the current row.

  • win_bounding: Specifies the window range boundary conditions, which can be the current row or a relative offset from the current row. For more information, see win_bounding below.

• {ROWS | RANGE} win_bounding: Specifies the window boundary determined by a single boundary value.

win_bounding

• CURRENT ROW: Specifies the current row as the window boundary.

• win_interval {PRECEDING | FOLLOWING}: Specifies the relative position of the current row when calculating. It defines the start and end positions of the window. Details are as follows:

  • win_interval: Defines the window interval size, which can be a fixed value, unspecified value, decimal, unlimited, or a date unit.

    • expression: Specifies the starting position of the window. It can be an integer representing the row offset or a specific keyword, as follows:

      • UNBOUNDED PRECEDING: Specifies the window start position as unbounded, meaning calculations begin from the first row.
      • UNBOUNDED FOLLOWING: Specifies the window end position as unbounded, meaning calculations end at the last row.
      • CURRENT ROW: Specifies the window start position as the current row.

    • INTERVAL expression date_unit: Defines the time interval, including an expression and a time unit.

      Notice

      • In the current version of OceanBase Database, the INTERVAL clause can only be used with RANGE.
      • When using the ORDER BY clause to define the sorting method for data in the window, ensure that the sorting expression is in date format.

      • expression: Specifies the interval expression. It can be any integer indicating the number of intervals.
      • date_unit: Specifies the interval unit, which can be YEAR, MONTH, DAY, or HOUR.

  • PRECEDING: Uses the preceding N rows.

  • FOLLOWING: Uses the following N rows.

Examples

Query the data in the col1, col3, and col5 columns of the test_tbl1 table, and calculate the sum of the col5 column in different window conditions for each row.

1. Create the test_tbl1 table.

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

2. Insert test data.

   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. Select the col1, col3, and col5 columns from the test_tbl1 table, and use an analytic function to calculate the sum of the col5 column in different window conditions. Finally, output the results in ascending order of the col1 column. The window conditions include different PARTITION BY and ORDER BY clauses, as well as ROWS and RANGE clauses, as follows:

   1. my_window_1 has no specific conditions, so the entire table is considered a window. sum1 calculates the sum of the col5 column in the entire table.
   2. my_window_2 partitions the data by the col3 column. sum2 calculates the sum of the col5 column in each col3 partition.
   3. my_window_3 sorts the data by the col5 column. sum3 calculates the sum of the col5 column in the rows before the current row (including the current row).
   4. my_window_4 sorts the data by the col5 column within each col3 partition and does not specify the rows. sum4 calculates the sum of the col5 column in each col3 partition sorted by the col5 column.
   5. my_window_5 sorts the data by the col5 column within each col3 partition and does not specify the rows. sum5 calculates the sum of the col5 column in each col3 partition.
   6. my_window_6 sorts the data by the col5 column within each col3 partition and specifies the current row. sum6 calculates the sum of the col5 column in each col3 partition for the current row.
   7. my_window_7 sorts the data by the col5 column within each col3 partition and specifies the row before the current row. sum7 calculates the sum of the col5 column in each col3 partition for the current row and the previous row.
   8. my_window_8 sorts the data by the col5 column within each col3 partition and specifies the previous and next rows. sum9 calculates the sum of the col5 column in each col3 partition from the previous row to the next row.
   9. my_window_9 sorts the data by the col4 column within each col3 partition and specifies the previous and next years. sum10 calculates the sum of the col5 column in each col3 partition from the previous year to the next year.

   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;
   

   The result set is as follows:

   +------+------+------------+------+------+------+------+------+------+------+------+------+------+
   | 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
   

References

SELECT