The WINDOW FUNCTION operator implements SQL analytic functions, also known as window functions, to calculate the results of relevant rows in a window.
A window function differs from an aggregate function in that the latter returns only one row from a group of data, while a window function returns multiple rows from a group. Each row in the group is the result of a window-based logical calculation. Therefore, in the execution of an SQL statement that contains a WINDOW FUNCTION, whose format is generally OVER(...), a WINDOW FUNCTION operator is assigned while an execution plan is generated.
Example: an execution plan that contains 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 ORDER BY or PARTITION BY is specified in a window function, a SORT operator is assigned at the subsequent layer to return the sorting result to the WINDOW FUNCTION operator.
In the preceding example, the Outputs & filters section for query Q1 shows in detail the output information of the WINDOW FUNCTION operator.
| Field | Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the WINDOW FUNCTION operator. |
| win_expr | The aggregate function to be used in the window. For example, query Q1 obtains the maximum value of column c1, so the function is T_FUN_MAX(t1.c1). |
| partition_by | Specifies how groups are partitioned in the window. For example, query Q1 is partitioned by column c1, so the value is t1.c1. |
| order_by | Specifies how rows are sorted in the window. For example, query Q1 requires ordering by column c2, so the value is t1.c2. |
| window_type | The type of the window, which could be either RANGE or ROWS:
For example, the default RANGE mode is selected in query Q1 as no window type is specified. |
| upper | Sets the upper boundary of the window. Valid values:
For example, the upper boundary of query Q1 can be set to a combination of UNBOUNDED and PRECEDING, indicating that no upper boundary is specified. |
| lower | The lower boundary of the window. The attribute settings are the same as those of the upper parameter. For example, the lower boundary in query Q1 is set to the current row. |
