WINDOW clause

Purpose

The WINDOW clause is used to define named windows with specific window specifications in SELECT queries. In OceanBase Database, a window is also known as a frame. OceanBase Database supports both ROWS and RANGE frame semantics. A ROWS frame is a physical row-based window, and a RANGE frame is a logical value-based window.

You can use an analytic function and add OVER window_name to reference the window specification. Functions that can be used with the OVER clause are known as analytic functions. For more information, see Overview of functions > Analytic 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 to identify the defined window.

• PARTITION BY expression_list: specifies a list of grouping keys for an analytic function. The input data is grouped by the specified expression list, and the analytic function is applied to each group. This way, the analytic function can independently calculate each group instead of the entire dataset.

  • expression: specifies a column or expression.

• ORDER BY order_by_condition_list: specifies a list of sorting rules for the grouped result set to define the sorting method of data in the window.

  • expression [ASC | DESC]: specifies the sorting expression for the window definition. ASC | DESC is an optional parameter that specifies the sorting order. ASC indicates ascending order (the default), and DESC indicates descending order.

• win_window: specifies the window range, which can be defined by the boundary conditions of ROWS or RANGE. For more information, see win_window.

win_window

• {ROWS | RANGE} BETWEEN win_bounding AND win_bounding: specifies the boundary values that define the window. Specifically:

  • ROWS | RANGE: specifies the frame semantics.

    • ROWS: specifies a physical window. The data of the first N rows and the last N rows after the data is sorted by the ORDER BY clause are calculated.
    • RANGE: specifies a logical window. The values of the current row are specified.

  • win_bounding: specifies the boundary conditions of the window range, which can be the current row or an offset from the current row. For more information, see win_bounding.

• {ROWS | RANGE} win_bounding: specifies a single boundary value that defines the window.

win_bounding

• CURRENT ROW: specifies the current row as the boundary of the window.

• win_interval {PRECEDING | FOLLOWING}: specifies whether to use rows before or after the current row for calculation. This parameter is used to specify the start and end positions of the window. Specifically:

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

    • expression: specifies the start position of the window. The value can be an integer, which indicates the row offset, or a specific keyword. Specifically:

      • UNBOUNDED PRECEDING: specifies that the start position of the window is unbounded, that is, the first row.
      • UNBOUNDED FOLLOWING: specifies that the end position of the window is unbounded, that is, the last row.
      • CURRENT ROW: specifies that the start position of the window is the current row.

    • INTERVAL expression date_unit: specifies the definition of a time interval, which includes an expression and a time unit.

      Notice

      • The INTERVAL clause can be used only with RANGE in the current version of OceanBase Database.
      • When you use the ORDER BY clause to specify the sorting method of data in a window, you must ensure that the sorting expression in the window definition is in date format.

      • expression: specifies the interval expression. The value can be any integer, which indicates the number of intervals.
      • date_unit: specifies the unit of time interval, which can be YEAR, MONTH, DAY, HOUR, and so on.

  • PRECEDING: specifies the previous N rows.

  • FOLLOWING: specifies the next N rows.

Examples

This example queries data from the test_tbl1 table and calculates the sum of the col5 values in different windows 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 calculate the sum of the col5 values in different windows for each row. Then, sort the results by the col1 values in ascending order. The window conditions include different PARTITION BY and ORDER BY clauses, as well as ROWS and RANGE clauses. Specifically:

   1. my_window_1 does not specify any conditions. Therefore, the entire table is considered as a window. sum1 calculates the sum of the col5 values in the entire table.
   2. my_window_2 partitions the data by the col3 values. sum2 calculates the sum of the col5 values in each col3 partition.
   3. my_window_3 sorts the data by the col5 values. sum3 calculates the sum of the col5 values in the rows before (including the current row) the current row.
   4. my_window_4 sorts the rows in each col3 partition by the col5 values and sets the rows to unspecified. sum4 calculates the sum of the col5 values in each col3 partition, which are sorted by the col5 values.
   5. my_window_5 sorts the rows in each col3 partition by the col5 values and sets the rows to unbounded. sum5 calculates the sum of the col5 values in each col3 partition.
   6. my_window_6 sorts the rows in each col3 partition by the col5 values and sets the current row to the current row. sum6 calculates the col5 values in the current row in each col3 partition.
   7. my_window_7 sorts the rows in each col3 partition by the col5 values and sets the current row to the previous row. sum7 calculates the col5 values in the current row and the previous row in each col3 partition.
   8. my_window_8 sorts the rows in each col3 partition by the col5 values and sets the current row to the previous row and the next row. sum9 calculates the col5 values in the previous row to the next row in each col3 partition.
   9. my_window_9 sorts the rows in each col3 partition by the col4 values and sets the current row to the previous year and the next year. sum10 calculates the col5 values in the previous year to the next year in each col3 partition.

   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 return result 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