In MySQL, the WINDOW clause is an optional clause that we can use to create a named window. The named window can then be referred to from a window function.
Many SQL developers define their window functions directly in the OVER clause. But that’s not the only way to do it. We can also define them in a WINDOW clause, and then refer to them in the OVER clause.
When we define the window function in a WINDOW clause, we name it. When we do this, we can refer to that name from the OVER clause. This eliminates the need to include the definition directly inside the OVER clause.
Named windows can be useful if we need to use multiple OVER clauses in our query that have the same definition. This saves us from having to duplicate the code used to create the window function specification.
Syntax
The syntax for creating named windows goes like this:
WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...Where window_spec goes like this:
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]So window_spec is the same code that we’d use if we were to define the window function directly inside the OVER clause.
We can see that the syntax allows for creating multiple named windows within a single query. To do this, we simply separate them with a comma.
The WINDOW clause is placed between the positions of the HAVING and ORDER BY clauses in the query.
Example
Suppose we have the following query:
SELECT
District,
Name,
Population,
SUM(Population) OVER(PARTITION BY District)
FROM City
WHERE CountryCode = 'AUS'
ORDER BY District, Name, "City Population";Here, the last column uses a window function to calculate its values. We’ve used the OVER clause with the PARTITION BY sub clause to define the window function. So it’s defined as SUM(Population) OVER(PARTITION BY District).
Let’s change the above query to use a named window:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf AS (PARTITION BY District)
ORDER BY District, Name, "City Population";This time we’ve added a WINDOW clause, and we’ve moved the PARTITION BY District part down to that clause. We’ve replaced it with the name that we’ve given the window function (in this case we’ve named the window wf).
Code Reuse
Named windows can be useful for reusing code so that our queries become more concise. In particular, if our query contains multiple columns with the same window function definition, we can define it once within a named window, then refer to that named window multiple times in the query.
Consider the following query:
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 | +--------------+------------+------+------------+ 8 rows in set (0.00 sec)
This query has the same window function repeated across three columns. This is a perfect opportunity to use the WINDOW clause.
Here’s how we can use the WINDOW clause to obtain the same result:
SELECT
ProductPrice,
ROW_NUMBER() OVER wf AS "Row Number",
RANK() OVER wf AS "Rank",
DENSE_RANK() OVER wf AS "Dense Rank"n
FROM Products
WINDOW wf 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 | +--------------+------------+------+------------+ 8 rows in set (0.00 sec)
So we can see that we got the same result, but with less code (and without having to repeat the same window function specification three times).
Note that in this example, I didn’t enclose the window’s name in parentheses. For example, I used OVER wf instead of OVER(wf). I could have used parentheses – the parentheses are optional in this case. However, the parentheses can be useful if we want to provide extra clauses in order to modify the named window.
Modifying a Named Window
We can modify a named window by providing extra clauses. 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(wf) AS "Default",
SUM(Population) OVER(wf ORDER BY Name ASC) AS "Ascending",
SUM(Population) OVER(wf ORDER BY Name DESC) AS "Descending"
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf AS (PARTITION BY District)
ORDER BY District, Name, "City Population";Result:
+-----------------+---------------+------------+---------+-----------+------------+ | District | Name | Population | Default | Ascending | Descending | +-----------------+---------------+------------+---------+-----------+------------+ | Capital Region | Canberra | 322723 | 322723 | 322723 | 322723 | | New South Wales | Central Coast | 227657 | 3993949 | 227657 | 3993949 | | New South Wales | Newcastle | 270324 | 3993949 | 497981 | 3766292 | | New South Wales | Sydney | 3276207 | 3993949 | 3774188 | 3495968 | | New South Wales | Wollongong | 219761 | 3993949 | 3993949 | 219761 | | Queensland | Brisbane | 1291117 | 1805236 | 1291117 | 1805236 | | Queensland | Cairns | 92273 | 1805236 | 1383390 | 514119 | | Queensland | Gold Coast | 311932 | 1805236 | 1695322 | 421846 | | Queensland | Townsville | 109914 | 1805236 | 1805236 | 109914 | | South Australia | Adelaide | 978100 | 978100 | 978100 | 978100 | | Tasmania | Hobart | 126118 | 126118 | 126118 | 126118 | | Victoria | Geelong | 125382 | 2990711 | 125382 | 2990711 | | Victoria | Melbourne | 2865329 | 2990711 | 2990711 | 2865329 | | West Australia | Perth | 1096829 | 1096829 | 1096829 | 1096829 | +-----------------+---------------+------------+---------+-----------+------------+ 14 rows in set (0.00 sec)
Note that 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.
Here’s what happens if we try to modify a property that already exists in the named window:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf ORDER BY Name)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf AS (ORDER BY District);Result:
ERROR 3583 (HY000): Window '<unnamed window>' cannot inherit 'wf' since both contain an ORDER BY clause.
In this case I tried to modify the named window with an ORDER BY clause. The problem is that the named window already includes an ORDER BY clause in its definition.
If we try to double up on the PARTITION BY clause, we get a different error:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf PARTITION BY Name)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf AS (PARTITION BY District);Result:
ERROR 3581 (HY000): A window which depends on another cannot define partitioning.
In this case the named window is defined as PARTITION BY District. When I called that named window, I tried to modify it with another PARTITION BY clause. The error tells me that I can’t do that.
Referring to Another Named Window
Named windows can refer to other named windows.
Example:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf3)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (PARTITION BY District), wf2 AS (wf1), wf3 AS (wf2)
ORDER BY District, Name, "City Population";Result:
+-----------------+---------------+------------+---------------------------+ | District | Name | Population | SUM(Population) OVER(wf3) | +-----------------+---------------+------------+---------------------------+ | Capital Region | Canberra | 322723 | 322723 | | New South Wales | Central Coast | 227657 | 3993949 | | New South Wales | Newcastle | 270324 | 3993949 | | New South Wales | Sydney | 3276207 | 3993949 | | New South Wales | Wollongong | 219761 | 3993949 | | Queensland | Brisbane | 1291117 | 1805236 | | Queensland | Cairns | 92273 | 1805236 | | Queensland | Gold Coast | 311932 | 1805236 | | Queensland | Townsville | 109914 | 1805236 | | South Australia | Adelaide | 978100 | 978100 | | Tasmania | Hobart | 126118 | 126118 | | Victoria | Geelong | 125382 | 2990711 | | Victoria | Melbourne | 2865329 | 2990711 | | West Australia | Perth | 1096829 | 1096829 | +-----------------+---------------+------------+---------------------------+ 14 rows in set (0.01 sec)
As seen here, we can create a chain of named windows. These can contain forward or backward references, but not cycles.
Here’s another, valid, example:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf3)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (wf2), wf2 AS (), wf3 AS (wf1)
ORDER BY District, Name, "City Population";Result:
+-----------------+---------------+------------+---------------------------+ | District | Name | Population | SUM(Population) OVER(wf3) | +-----------------+---------------+------------+---------------------------+ | Capital Region | Canberra | 322723 | 11313666 | | New South Wales | Central Coast | 227657 | 11313666 | | New South Wales | Newcastle | 270324 | 11313666 | | New South Wales | Sydney | 3276207 | 11313666 | | New South Wales | Wollongong | 219761 | 11313666 | | Queensland | Brisbane | 1291117 | 11313666 | | Queensland | Cairns | 92273 | 11313666 | | Queensland | Gold Coast | 311932 | 11313666 | | Queensland | Townsville | 109914 | 11313666 | | South Australia | Adelaide | 978100 | 11313666 | | Tasmania | Hobart | 126118 | 11313666 | | Victoria | Geelong | 125382 | 11313666 | | Victoria | Melbourne | 2865329 | 11313666 | | West Australia | Perth | 1096829 | 11313666 | +-----------------+---------------+------------+---------------------------+ 14 rows in set (0.00 sec)
However as mentioned, we can’t create a cycle. Here’s what happens if we try to create a cycle:
SELECT
District,
Name,
Population,
SUM(Population) OVER(wf3)
FROM City
WHERE CountryCode = 'AUS'
WINDOW wf1 AS (PARTITION BY District), wf2 AS (wf3), wf3 AS (wf2)
ORDER BY District, Name, "City Population";Result:
ERROR 3580 (HY000): There is a circularity in the window dependency graph.
In this case wf2 references wf3, but wf3 references wf2. This creates a circular reference, which is not allowed, and so we get the error.