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.