Named Windows
An analytic function's OVER
clause can reference a named window, which encapsulates one or more window clauses: a window partition (PARTITION BY
) clause and (optionally) a window order (ORDER BY
) clause. Named windows can be useful when you write queries that invoke multiple analytic functions with similar OVER
clause syntax—for example, they use the same partition clauses.
A query names a window as follows:
WINDOW window‑name AS ( window-partition-clause [window-order-clause] );
The same query can name and reference multiple windows. All window names must be unique within the same query.
Examples
The following query invokes two analytic functions, RANK
and DENSE_RANK
. Because the two functions use the same partition and order clauses, the query names a window w
that specifies both clauses. The two functions reference this window as follows:
=> SELECT employee_region region, employee_key, annual_salary, RANK() OVER w Rank, DENSE_RANK() OVER w "Dense Rank" FROM employee_dimension WINDOW w AS (PARTITION BY employee_region ORDER BY annual_salary); region | employee_key | annual_salary | Rank | Dense Rank ----------------------------------+--------------+---------------+------+------------ West | 5248 | 1200 | 1 | 1 West | 6880 | 1204 | 2 | 2 West | 5700 | 1214 | 3 | 3 West | 9857 | 1218 | 4 | 4 West | 6014 | 1218 | 4 | 4 West | 9221 | 1220 | 6 | 5 West | 7646 | 1222 | 7 | 6 West | 6621 | 1222 | 7 | 6 West | 6488 | 1224 | 9 | 7 West | 7659 | 1226 | 10 | 8 West | 7432 | 1226 | 10 | 8 West | 9905 | 1226 | 10 | 8 West | 9021 | 1228 | 13 | 9 West | 7855 | 1228 | 13 | 9 West | 7119 | 1230 | 15 | 10 ...
If the named window omits an order clause, the query's OVER
clauses can specify their own order clauses. For example, you can modify the previous query so each function uses a different order clause. The named window is defined so it includes only a partition clause:
=> SELECT employee_region region, employee_key, annual_salary, RANK() OVER (w ORDER BY annual_salary DESC) Rank, DENSE_RANK() OVER (w ORDER BY annual_salary ASC) "Dense Rank" FROM employee_dimension WINDOW w AS (PARTITION BY employee_region); region | employee_key | annual_salary | Rank | Dense Rank ----------------------------------+--------------+---------------+------+------------ West | 5248 | 1200 | 2795 | 1 West | 6880 | 1204 | 2794 | 2 West | 5700 | 1214 | 2793 | 3 West | 6014 | 1218 | 2791 | 4 West | 9857 | 1218 | 2791 | 4 West | 9221 | 1220 | 2790 | 5 West | 6621 | 1222 | 2788 | 6 West | 7646 | 1222 | 2788 | 6 West | 6488 | 1224 | 2787 | 7 West | 7432 | 1226 | 2784 | 8 West | 9905 | 1226 | 2784 | 8 West | 7659 | 1226 | 2784 | 8 West | 7855 | 1228 | 2782 | 9 West | 9021 | 1228 | 2782 | 9 West | 7119 | 1230 | 2781 | 10 ...
Similarly, an OVER
clause specifies a named window can also specify a window frame clause, provided the named window includes an order clause. This can be useful inasmuch as you cannot define a named windows to include a window frame clause.
For example, the following query defines a window that encapsulates partitioning and order clauses. The OVER
clause invokes this window and also includes a window frame clause:
=> SELECT deptno, sal, empno, COUNT(*) OVER (w ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS count FROM emp WINDOW w AS (PARTITION BY deptno ORDER BY sal); deptno | sal | empno | count --------+-----+-------+------- 10 | 101 | 1 | 1 10 | 104 | 4 | 2 20 | 100 | 11 | 1 20 | 109 | 8 | 2 20 | 109 | 6 | 3 20 | 109 | 7 | 3 20 | 110 | 10 | 3 20 | 110 | 9 | 3 30 | 102 | 2 | 1 30 | 103 | 3 | 2 30 | 105 | 5 | 3 (11 rows)
Recursive Window References
A WINDOW
clause can reference another window that is already named. For example, because named window w1
is defined before w2
, the WINDOW
clause that defines w2
can reference w1
:
=> SELECT RANK() OVER(w1 ORDER BY sal DESC), RANK() OVER w2 FROM EMP WINDOW w1 AS (PARTITION BY deptno), w2 AS (w1 ORDER BY sal);
Restrictions
- An
OVER
clause can reference only one named window. - Each
WINDOW
clause within the same query must have a unique name.