The WINDOW FUNCTION operator implements SQL analytic functions, or window functions, to calculate the results of relevant rows in a window.
Window functions differ from aggregate functions in that, the latter returns only one row from a group of data, while window functions return 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 includes a WINDOW FUNCTION, whose format is generally OVER(...), a WINDOW FUNCTION operator is assigned while generating the execution plan.
Example: An execution plan that includes a WINDOW FUNCTION operator
obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)
obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)
Q1:
obclient>EXPLAIN SELECT MAX(c1) OVER(PARTITION BY c1 ORDER BY c2) FROM t1\G;
*************************** 1. row ***************************
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 an 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 in the execution plan display of query Q1 shows in detail the output information of the WINDOW FUNCTION operator.
Field |
Description |
|---|---|
| output | The output expression of the operator. |
| filter | The filter conditions of the operator. In this example, the condition is set to nil because no filter is configured for the WINDOW FUNCTION operator. |
| win_expr | Indicates 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 | Indicates how groups are partitioned in the window. For example, query Q1 is partitioned by column c1, so the value is t1.c1. |
| order_by | Indicates how rows are ordered 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": * range: In the default "range" mode, the upper and lower boundaries of the window are calculated based on logical offsets. * rows: In the "rows" mode, the upper and lower boundaries of the window are calculated based on physical offsets. 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: * UNBOUNDED: No boundary is specified and the maximum value is used (default). * CURRENT ROW: Starts from the current row. A numeric value indicates the number of rows to move from the current. * PRECEDING: sets the boundary in the preceding rows. * FOLLOWING: sets the boundary in the following rows. For example, the upper boundary in query Q1 is set to unbounded preceding. |
| lower | Sets the lower boundary of the window. The attribute settings are the same as those of the upper boundary. For example, the lower boundary in query Q1 is set to the current row. |