The WINDOW clause is an optional clause that we can use in our SQL queries to create a named window. The named window can then be used as part of a window function.
When creating a window function, a SQL developer will often define it directly in the OVER clause. But that’s not the only way to do it. We can alternatively use the WINDOW clause to define it in a named window, and then refer to that named window in the OVER clause.
Named windows can be handy if we need to use multiple OVER clauses in our query that have the same definition. This saves us from having to replicate the code used to create the window function specification.
Syntax
The syntax for creating named windows typically goes something like this:
WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...Where window_spec might look something like this:
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]The exact syntax may depend on the DBMS, but the above clauses are pretty typical for window functions across DBMSs.
Basically, window_spec is the same code that we’d use if we were to define the window function directly inside the OVER clause. So if we’ve already defined the window function in the OVER clause, we can easily transfer it to a named window, simply by cutting and pasting it to a WINDOW clause.
We can create multiple named windows within a single WINDOW clause by separating each named window with a comma.
The WINDOW clause is typically placed between the positions of the HAVING and ORDER BY clauses in the query.
Example
Consider the following query:
SELECT
District,
Name,
Population,
SUM(Population) OVER(PARTITION BY District) AS "District Population"
FROM City
WHERE CountryCode = 'AUS';Result:
+-----------------+---------------+------------+---------------------+ | District | Name | 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, the last column uses a window function to calculate its values. We’ve defined the window function directly in the OVER clause. We’ve done this with the PARTITION BY clause. Specifically, we’ve defined the window function as SUM(Population) OVER(PARTITION BY District).
Let’s move it to a named window:
SELECT
District,
Name,
Population,
SUM(Population) OVER(win) AS "District Population"
FROM City
WHERE CountryCode = 'AUS'
WINDOW win AS (PARTITION BY District);Result:
+-----------------+---------------+------------+---------------------+ | District | Name | 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 | +-----------------+---------------+------------+---------------------+
So in this query we simply added a WINDOW clause and moved the PARTITION BY District part down to that clause. And in the OVER clause, we replaced the PARTITION BY clause with the name that we gave the named window (in this case win).
Code Reuse
Named windows can be very handy when we have multiple window functions with the same definition. In such cases, named windows can save us from having to replicate our code across multiple OVER clauses. Instead, we can provide the definition once in the named window, then refer to that named window from multiple OVER clauses.
The following query is a good candidate for a named window:
SELECT
ProductPrice,
ROW_NUMBER() OVER (ORDER BY ProductPrice) AS "Row Number",
RANK() OVER (ORDER BY ProductPrice) AS "Rank",
DENSE_RANK() OVER (ORDER BY ProductPrice) AS "Dense Rank"
FROM Products;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 | +--------------+------------+------+------------+
Here, we’ve repeated the same definition across three different OVER clauses. While it works fine, we could make our code a lot more concise with a WINDOW clause.
Here’s how we can use the WINDOW clause to obtain the same result:
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 | +--------------+------------+------+------------+
Same result with less code.
Window Chaining
Window chaining is a concept that allows us to use the definition from one window as the base definition for another window. Basically, this means we can provide extra clauses when referring to a named window.
To do this, we need to enclose the window’s name in parentheses, then append the other clause/s within the parentheses.
Example:
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 | +-----------------+---------------+------------+---------+-----------+------------+
So in this example we added an ORDER BY clause to the second and third OVER clauses (this is in addition to referring to the named window).
Referring to Another Named Window
Named windows can also refer to other named windows.
Example:
SELECT
District,
Name,
Population,
SUM(Population) OVER(win3)
FROM City
WHERE CountryCode = 'AUS'
WINDOW win1 AS (PARTITION BY District), win2 AS (win1), win3 AS (win2);Result:
+-----------------+---------------+------------+----------------------------+ | District | Name | Population | SUM(Population) OVER(win3) | +-----------------+---------------+------------+----------------------------+ | 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 | +-----------------+---------------+------------+----------------------------+
As seen here, we can create a chain of named windows. However, when we do this, we need to be careful not to create an endless loop. Most (if not all) DBMSs will return an error if we try to do this.
Conclusion
The WINDOW clause can be used to create named windows in SQL. When we do this, we can refer to the named window multiple times (i.e. with multiple OVER clauses) without having to replicate the definition.
We can define multiple named windows in a single WINDOW clause by separating them with a comma. We can also refer to other named windows from a named window, as long as we don’t create a cycle.
If we find ourselves regularly creating window functions, the WINDOW clause can be a handy addition to our toolkit.