When we create a window function in SQL, we have the option of defining it directly in the OVER clause or in a named window. When we define it in a named window, we can refer to that window name in our OVER clause, which saves us from having to define it directly in the OVER clause.

Example

Here’s an example to demonstrate:

SELECT 
    District, 
    Name, 
    Population AS "City Population", 
    SUM(Population) OVER(win) AS "District Population" 
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW win AS (PARTITION BY District);

Result:

+-----------------+---------------+-----------------+---------------------+
| District        | Name          | City Population | District Population |
+-----------------+---------------+-----------------+---------------------+
| Capital Region  | Canberra      |          322723 |              322723 |
| New South Wales | Sydney        |         3276207 |             3993949 |
| New South Wales | Newcastle     |          270324 |             3993949 |
| New South Wales | Central Coast |          227657 |             3993949 |
| New South Wales | Wollongong    |          219761 |             3993949 |
| Queensland      | Brisbane      |         1291117 |             1805236 |
| Queensland      | Gold Coast    |          311932 |             1805236 |
| Queensland      | Townsville    |          109914 |             1805236 |
| Queensland      | Cairns        |           92273 |             1805236 |
| South Australia | Adelaide      |          978100 |              978100 |
| Tasmania        | Hobart        |          126118 |              126118 |
| Victoria        | Melbourne     |         2865329 |             2990711 |
| Victoria        | Geelong       |          125382 |             2990711 |
| West Australia  | Perth         |         1096829 |             1096829 |
+-----------------+---------------+-----------------+---------------------+

Here, my OVER clause contains a reference to a named window called win. That window is defined later in the query with the WINDOW clause. In this case, I provided PARTITION BY District as the window’s definition.

Code Reuse

Using named windows can be useful if we have multiple columns that use the same window function definition. This saves us from having to create duplicate code when defining each window function.

Example:

SELECT
  ProductPrice,
  ROW_NUMBER() OVER win AS "Row Number",
  RANK()       OVER win AS "Rank",
  DENSE_RANK() OVER win AS "Dense Rank"
FROM Products
WINDOW win AS (ORDER BY ProductPrice);

Result:

+--------------+------------+------+------------+
| ProductPrice | Row Number | Rank | Dense Rank |
+--------------+------------+------+------------+
|         9.99 |          1 |    1 |          1 |
|        11.99 |          2 |    2 |          2 |
|        14.75 |          3 |    3 |          3 |
|        25.99 |          4 |    4 |          4 |
|        25.99 |          5 |    4 |          4 |
|        33.49 |          6 |    6 |          5 |
|        55.99 |          7 |    7 |          6 |
|       245.00 |          8 |    8 |          7 |
+--------------+------------+------+------------+

In this case, we used the named window multiple times without having to repeat its definition multiple times in our SELECT list.

Adding to a Named Window’s Definition

We can combine a named window with additional clauses if required:

SELECT 
    District, 
    Name, 
    Population,
    SUM(Population) OVER(win) AS "Default",
    SUM(Population) OVER(win ORDER BY Name ASC) AS "Ascending",
    SUM(Population) OVER(win ORDER BY Name DESC) AS "Descending"
FROM City 
WHERE CountryCode = 'AUS' 
WINDOW win AS (PARTITION BY District);

Result:

+-----------------+---------------+------------+---------+-----------+------------+
| District        | Name          | Population | Default | Ascending | Descending |
+-----------------+---------------+------------+---------+-----------+------------+
| Capital Region  | Canberra      |     322723 |  322723 |    322723 |     322723 |
| New South Wales | Wollongong    |     219761 | 3993949 |   3993949 |     219761 |
| New South Wales | Sydney        |    3276207 | 3993949 |   3774188 |    3495968 |
| New South Wales | Newcastle     |     270324 | 3993949 |    497981 |    3766292 |
| New South Wales | Central Coast |     227657 | 3993949 |    227657 |    3993949 |
| Queensland      | Townsville    |     109914 | 1805236 |   1805236 |     109914 |
| Queensland      | Gold Coast    |     311932 | 1805236 |   1695322 |     421846 |
| Queensland      | Cairns        |      92273 | 1805236 |   1383390 |     514119 |
| Queensland      | Brisbane      |    1291117 | 1805236 |   1291117 |    1805236 |
| South Australia | Adelaide      |     978100 |  978100 |    978100 |     978100 |
| Tasmania        | Hobart        |     126118 |  126118 |    126118 |     126118 |
| Victoria        | Melbourne     |    2865329 | 2990711 |   2990711 |    2865329 |
| Victoria        | Geelong       |     125382 | 2990711 |    125382 |    2990711 |
| West Australia  | Perth         |    1096829 | 1096829 |   1096829 |    1096829 |
+-----------------+---------------+------------+---------+-----------+------------+

In most (if not all) RDBMSs, we can only add properties to a named window. We can’t modify any existing properties. In the above example, we added the ORDER BY clause. Given the ORDER BY clause wasn’t included in the named window’s definition, we were able to run it without error.