The WINDOW FUNCTION operator is used to implement analytical functions (also known as window functions) in SQL, calculating results for related rows within a window.
Unlike aggregate functions, which can return only one row per group, window functions can return multiple rows per group. Each row within the group is the result of logical calculations based on the window. Therefore, when executing SQL containing WINDOW FUNCTIONS (typically in the OVER(...) format), a WINDOW FUNCTION operator is allocated during plan generation.
Example: Execution plan containing a WINDOW FUNCTION operator
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT MAX(c1) OVER(PARTITION BY c1 ORDER BY c2) FROM t1;
Query Plan:
========================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------------
|0 |WINDOW FUNCTION| |3 |45 |
|1 | SORT | |3 |44 |
|2 | TABLE SCAN |T1 |3 |37 |
========================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_MAX(T1.C1)]), filter(nil),
win_expr(T_FUN_MAX(T1.C1)), partition_by([T1.C1]), order_by([T1.C2, ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(CURRENT ROW)
1 - output([T1.C1], [T1.C2]), filter(nil), sort_keys([T1.C1, ASC], [T1.C2, ASC])
2 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
When a ORDER BY or PARTITION BY clause is specified in the window function, a SORT operator is allocated at the lower level to return the sorted results for use by the window function operator.
In the preceding example, the outputs & filters section of the execution plan for query Q1 shows the output information of the WINDOW FUNCTION operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. In the example, no filter is set for the WINDOW FUNCTION operator, so the value is nil. |
| win_expr | The aggregate function used in the window. For example, query Q1 calculates the maximum value of column c1, so the value is T_FUN_MAX(t1.c1). |
| partition_by | The grouping method in the window. For example, query Q1 groups data by column c1, so the value is t1.c1. |
| order_by | The sorting method in the window. For example, query Q1 sorts data by column c2, so the value is t1.c2. |
| window_type | The window types, including RANGE and ROWS:
For example, query Q1 does not specify a window type, so the default RANGE is used. |
| upper | Specifies the upper boundary of the window:
For example, the upper boundary of query Q1 is UNBOUNDED PRECEDING, which means that there is no preceding boundary. |
| lower | Specifies the lower boundary of the window. The boundary properties are the same as those of upper.For example, the lower boundary of query Q1 is the current row. |