Window Functions in T-SQL

2020-08-10 · mssql, tsql, queries

I've lost count of the cursors I've deleted from reports because someone needed a running total or the "latest row per customer". Those queries used to mean correlated subqueries, self-joins or a loop. Window functions do them in one pass with an OVER clause. ROW_NUMBER and RANK have been around since 2005, and the full set (LAG, LEAD, FIRST_VALUE, frames) arrived in 2012, so there's little excuse for the cursor anymore.

These are the patterns I use most, in the order I'd teach them, on Sales.SalesOrderHeader from AdventureWorks2017. Everything needs SQL Server 2012 or later (LAG/LEAD and ROWS BETWEEN don't exist in 2008 R2), any edition. Any table with a key, a grouping column, a date and an amount will do.

1. The OVER clause

A window function computes a value for each row using a set of related rows, the window, without collapsing them into one row the way GROUP BY does. The OVER clause has three optional parts:

function() OVER (
    PARTITION BY <columns>       -- split rows into independent groups
    ORDER BY <columns>           -- order within the group; required for ranking and offset functions
    ROWS BETWEEN ... AND ...     -- the frame: which rows around the current one to include
)

Leaving out PARTITION BY makes the whole result set one window. Leaving out the frame with an ORDER BY gives the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which matters for performance later.

2. Ranking with ROW_NUMBER, RANK and DENSE_RANK

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
       ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn,
       RANK()       OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC) AS dense_rnk
FROM Sales.SalesOrderHeader;

ROW_NUMBER always gives unique numbers, ties broken arbitrarily unless the ORDER BY is unique. RANK gives ties the same number and skips the following numbers (1, 1, 3); DENSE_RANK does not skip (1, 1, 2). NTILE(4) divides each partition into quartiles.

3. Latest row per group

By far the one I use most. Wrap ROW_NUMBER in a CTE and filter:

WITH ranked AS (
    SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
           ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC, SalesOrderID DESC) AS rn
    FROM Sales.SalesOrderHeader
)
SELECT CustomerID, SalesOrderID, OrderDate, TotalDue
FROM ranked
WHERE rn = 1;

Always add the key as a tiebreaker in the ORDER BY. I skipped it once, and a report showed a different "latest" order from one run to the next when two orders shared a date. An index on (CustomerID, OrderDate DESC, SalesOrderID DESC) lets this run without a sort.

4. Running totals and moving averages

SELECT CustomerID, OrderDate, TotalDue,
       SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID
                           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3,
       SUM(TotalDue) OVER (PARTITION BY CustomerID) AS customer_total
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, OrderDate;

Three different windows in one query: a running total, a three-row moving average, and a partition total with no ORDER BY (so every row in the partition is included). Always write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly for running totals. The default RANGE frame produces the same numbers when the ordering is unique, but it uses an on-disk worktable in tempdb and is noticeably slower; ROWS uses an in-memory one.

5. LAG and LEAD

SELECT CustomerID, SalesOrderID, OrderDate,
       LAG(OrderDate)  OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID) AS prev_order,
       DATEDIFF(DAY, LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID), OrderDate) AS days_since_prev,
       LEAD(TotalDue, 1, 0) OVER (PARTITION BY CustomerID ORDER BY OrderDate, SalesOrderID) AS next_amount
FROM Sales.SalesOrderHeader;

LAG(column, offset, default): the offset defaults to 1 and the default to NULL. This replaces the self-join on rn = rn - 1 that people wrote before 2012, and it is one pass over the data instead of two.

6. FIRST_VALUE and LAST_VALUE

SELECT CustomerID, SalesOrderID, OrderDate, TotalDue,
       FIRST_VALUE(TotalDue) OVER (PARTITION BY CustomerID ORDER BY OrderDate
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amount,
       LAST_VALUE(TotalDue)  OVER (PARTITION BY CustomerID ORDER BY OrderDate
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount
FROM Sales.SalesOrderHeader;

LAST_VALUE is the classic trap: with the default frame ending at CURRENT ROW, it returns the current row's value. You must extend the frame to UNBOUNDED FOLLOWING to get the actual last value of the partition.

Sanity checks

I always check a running total against a plain aggregate for one customer:

SELECT SUM(TotalDue) FROM Sales.SalesOrderHeader WHERE CustomerID = 29825;
-- should equal running_total on the last row for that customer in the section 4 query

And compare performance of ROWS versus the default frame with SET STATISTICS IO ON: the RANGE version shows a Worktable with a large number of logical reads, the ROWS version shows zero.

Errors and slow queries

Msg 4112: The function 'ROW_NUMBER' must have an OVER clause with ORDER BY. Ranking and offset functions require ORDER BY; aggregates do not.
Msg 4113: The function 'LAG' is not valid in the WHERE clause. Window functions are evaluated after WHERE, GROUP BY and HAVING. To filter on them, put the query in a CTE or derived table and filter outside, as in section 3.
Msg 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses. Same rule, different clause.
Slow window queries with a Sort operator per window. Each distinct PARTITION BY/ORDER BY combination can require its own sort. Try to use the same partitioning and ordering across functions in one query, and index to support it.

If you learn only three: ROW_NUMBER for latest-per-group, SUM ... OVER with an explicit ROWS frame for running totals, and LAG instead of self-joins.