Purpose
In a SELECT query, the WINDOW clause is used to define named windows with specific window specifications. A "window", also known as a frame, can be either ROWS-based or RANGE-based in OceanBase AI Database. The former is based on physical row offsets, while the latter is based on logical value offsets.
Use an analytic function and add OVER window_name after it to reference the window specification. Functions that can be used in the OVER clause are called analytic functions. For more information about analytic functions, see the Analytic functions section in Function overview.
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
Parameter |
Description |
|---|---|
| WINDOW window_definition_list | Specifies the window definition for the analytic function. window_definition_list Defines a list for the window. |
| window_definition | Represents the window definition, including the window name, grouping method, sorting method, and window range. For more information, see window_definition. |
window_definition
window_name: The name of the window, used to identify the defined window.PARTITION BY expression_list: Optional. Specifies the list of grouping keys for the analytic function. It groups the input data based on the specified list of expressions and applies the analytic function to each group. This allows independent calculations to be performed on each group within the analytic function, rather than calculating the entire dataset.expression: a column or expression.
ORDER BY order_by_condition_list: Optional. Specifies the list of sorting rules for the grouped result set, defining how data within the window is ordered.expression [ASC | DESC]: the sorting expression defined for the window.ASC | DESCis an optional parameter that specifies the sorting order.ASCindicates ascending order (default), andDESCindicates descending order.
win_window: Optional. Specifies the definition of the window range, including the boundary conditions forROWSorRANGE. For more information, see win_window.
win_window
{ROWS | RANGE} BETWEEN win_bounding AND win_bounding: Indicates that the window boundaries are determined by two boundary values. Specifically:ROWS | RANGE: specifies the frame semantics.ROWS: Indicates a physical window. The calculation is based on the firstNand lastNrows after sorting according to theORDER BYclause.RANGE: specifies the range of values for the corresponding column in the current row.
win_bounding: specifies the boundary condition for the window range. It can be the current row or an interval relative to the current row. For more information, see win_bounding.
{ROWS | RANGE} win_bounding: Indicates that the window boundary is determined by a single boundary value.
win_bounding
CURRENT ROW: Indicates that the current row is used as the window boundary.win_interval {PRECEDING | FOLLOWING}: Specifies whether the relative position is before or after the current row when calculating the current row. It specifies the start and end positions of the window. The details are as follows:win_interval: Defines the interval size of the window. It can be a fixed value, an unspecified value, a decimal number, unlimited, or a date unit.expression: Indicates the starting position of the window. The value can be an integer representing the number of rows to offset, or a specific keyword, as follows:UNBOUNDED PRECEDING: The window starts from the beginning of the dataset.UNBOUNDED FOLLOWING: indicates that the end of the window is unbounded, meaning the calculation extends to the end of the last row.CURRENT ROW: The starting position of the window is the current row.
INTERVAL expression date_unit: defines the time interval, consisting of an expression and a time unit.Notice
- In the current version of OceanBase AI Database, the
INTERVALclause can only be used together withRANGE. - When using the
ORDER BYclause to define the sorting order of data within a window, ensure that the sorting expression in the window definition is in date format.
expression: the interval expression. The value can be any integer, indicating the number of intervals.date_unit: the unit of the interval. Valid values:YEAR,MONTH,DAY, andHOUR.
- In the current version of OceanBase AI Database, the
PRECEDING: the firstNrows.FOLLOWING: the nextNrows.
Examples
Query the data in specified columns of the test_tbl1 table and calculate the sum of col5 across different window conditions for each row.
Create the table
test_tbl1.CREATE TABLE test_tbl1 (col1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col2 INT, col3 VARCHAR(50), col4 DATE, col5 INT);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);Select columns
col1,col3, andcol5from thetest_tbl1table, then use aggregate functions to calculate the sum ofcol5under different window conditions. Finally, output the results in ascending order by the value ofcol1. The window conditions include differentPARTITION BYandORDER BYclauses, as well asROWSandRANGEclauses, as follows:my_window_1has no specific condition, so the entire table is considered a window.sum1calculates the sum ofcol5across the entire table.my_window_2is partitioned bycol3.sum2calculates the sum ofcol5for eachcol3partition.my_window_3is sorted by the value ofcol5.sum3calculates the sum ofcol5values up to (including) the current row.- In
my_window_4, thecol5column is set to unspecified in thecol3partition.sum4calculates the sum ofcol5values sorted bycol5within eachcol3partition. - In
my_window_5, the rows within thecol3partition are set to undefined in the order ofcol5.sum5calculates the sum ofcol5values in eachcol3partition. my_window_6sets the current row as the current row in thecol3partition, ordered bycol5.sum6calculates the sum ofcol5for the current row in eachcol3partition.my_window_7sets the window for each row in thecol3partition to the rows that precede it by one row in the order ofcol5.sum7calculates the sum ofcol5values for the current row and the preceding row in eachcol3partition.my_window_8in thecol3partition sets the first and last rows in order ofcol5.sum9calculates the sum ofcol5for the first to last row in eachcol3partition.my_window_9sets the rows within thecol3partition in order ofcol4to the first and last year.sum10calculates the sum ofcol5from the first year to the last year within eachcol3partition.
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