For this month's T-SQL Tuesday, Steve Jones asks us to talk about problems we've solved using window functions.
When I first used window functions back in SQL Server 2005, I was in awe. I had always used inefficient self-joins to calculate things like running totals, and these really didn't scale well with size-of-data. I quickly realized you could also use them for ranks and moving averages without those cumbersome self-joins, elaborate sub-queries, or #temp tables. Those all have their place, but window functions can make them feel old-school and dirty.
If you're brand new to window functions, I recommend spending some time with this tutorial from Koen Verbeeck. If you're already familiar, read on.
Using mostly Stack Exchange Data Explorer, I'll show a few examples of problems I've solved in the past using window functions.
RANK() is a pretty simple one. In this example, let's say we want the top 5 Stack Overflow users by reputation:
SELECT TOP (5) Id, DisplayName, Reputation, rk = RANK() OVER (ORDER BY Reputation DESC) FROM dbo.Users ORDER BY Reputation DESC;
The results of this query:
The scores are all unique, but
RANK() would have shown the same value if there happened to be ties. Which may or may be intended or expected.
SELECT TOP (20) Title, CreationDate, Score, [row_number] = ROW_NUMBER() OVER (ORDER BY Score DESC), [rank] = RANK() OVER (ORDER BY Score DESC), [dense_rank] = DENSE_RANK() OVER (ORDER BY Score DESC) FROM Posts WHERE PostTypeId = 1 -- Question AND CreationDate >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1) ORDER BY Score DESC;
The results of this query show that
ROW_NUMBER() produces contiguous results, while
DENSE_RANK() produce slightly different results: the former leaves gaps when there are ties, and the latter leaves no gaps:
Top N per group
When grouping or joining it can be tedious to get just the first or just the newest row for anything. Let's take a simple example where I want to know the date and title of the last question posted to Stack Overflow by me or by Jon Skeet:
WITH src AS ( SELECT Id, Title, CreationDate, OwnerUserId, rn = ROW_NUMBER() OVER ( PARTITION BY OwnerUserId ORDER BY CreationDate DESC ) FROM Posts WHERE PostTypeId = 1 -- Question AND OwnerUserId IN (61305, 22656) -- me, Jon Skeet ) SELECT Id, Title, CreationDate, OwnerUserId FROM src WHERE rn = 1;
The window function lets me find the newest question grouped ("partitioned") by
OwnerUserId. Here are the results:
You can put more complex expressions in the
OVER() clause, too. For example, if I wanted to find the first user created in each month this year, I could write this query:
WITH UsersThisYear AS ( SELECT Id, DisplayName, CreationDate, rn = ROW_NUMBER() OVER ( PARTITION BY DATEFROMPARTS (YEAR(CreationDate), MONTH(CreationDate), 1) ORDER BY CreationDate ) FROM Users WHERE CreationDate >= DATEFROMPARTS(YEAR(GETDATE()),1,1) ) SELECT Id, DisplayName, CreationDate FROM UsersThisYear WHERE rn = 1 ORDER BY CreationDate;
I've solved a lot of other people's problems with
ROW_NUMBER(), too. Here are 197 answers by me that, for one reason or another, contain
WHERE rn = 1.
Another luxury of modern versions is the ability to use window frames in the
OVER() clause. We can use this technique to calculate aggregates over a large number of rows while still only reading those rows once. For example, if I wanted to see how many answers I've posted for each month in 2023, with a running total, I can write this query:
WITH AaronPosts AS ( SELECT [Month] = MONTH(CreationDate), PostCount = COUNT(*) FROM Posts WHERE CreationDate >= '20230101' AND CreationDate < '20240101' AND OwnerUserId = 61305 AND PostTypeId = 2 -- answer GROUP BY MONTH(CreationDate) ) SELECT [Month], CountForMonth = PostCount, RunningCount = SUM(PostCount) OVER ( ORDER BY [Month] ROWS UNBOUNDED PRECEDING ) FROM AaronPosts ORDER BY [Month];
You can see a few of the cumbersome ways we used to have to do this in this answer (along with some links to posts about performing grouped running totals, and why you want to avoid the
SQL Server 2022 enhancements
There is one window function enhancement that I am really looking forward to using: named windows. Itzik gave this a much thorough treatment in T-SQL Windowing Improvements in SQL Server 2022, but here's a quick example of how it can simplify a query.
Let's change the second query above so that it has a slightly more complex
SELECT TOP (20) Id, PostTypeId, Score, rn = ROW_NUMBER() OVER (PARTITION BY PostTypeID ORDER BY Score DESC), rk = RANK() OVER (PARTITION BY PostTypeID ORDER BY Score DESC), dr = DENSE_RANK() OVER (PARTITION BY PostTypeID ORDER BY Score DESC) FROM Posts WHERE CreationDate >= DATEADD(DAY, -14, GETDATE()) ORDER BY Score DESC;
Note that the same over clause is repeated multiple times, which makes the query harder to maintain, particularly if there may be changes later. In SQL Server 2022, you can alias a defined window, e.g.:
SELECT TOP (20) Id, PostTypeId, Score, rn = ROW_NUMBER() OVER PScore, rk = RANK() OVER PScore, dr = DENSE_RANK() OVER PScore FROM Posts WHERE CreationDate >= DATEADD(DAY, -14, GETDATE()) WINDOW PScore AS (PARTITION BY PostTypeID ORDER BY Score DESC) ORDER BY Score DESC;
You can also consolidate just part of the
OVER() clause, for example a common use case is to take the row number ascending and descending over the same partition. You can simplify this:
SELECT ...cols..., rnA = ROW_NUMBER() OVER (PARTITION BY PostTypeID ORDER BY Score), rnB = ROW_NUMBER() OVER (PARTITION BY PostTypeID ORDER BY Score DESC) FROM Posts WHERE ...
SELECT ...cols..., rnA = ROW_NUMBER() OVER (PScore ORDER BY Score), rnB = ROW_NUMBER() OVER (PScore ORDER BY Score DESC) FROM Posts WHERE ... WINDOW PScore AS (PARTITION BY PostTypeID) ...
Which can be really helpful with more elaborate partition clauses.
I can't demonstrate these on Stack Exchange Data Explorer directly, since its underlying version is still SQL Server 2019. But you can try them out on your own systems or using db<>fiddle. These enhancements are also available in Azure SQL Database / Azure SQL Managed Instance.
Window functions can solve all kinds of problems in SQL Server, and this post just barely scratches the surface. They can also be used for gaps and islands, grouped medians, and even more elaborate things. I am interested in reading all of the other T-SQL Tuesday #168 contributions.