Window functions are a class of functions that use the OVER clause. These functions
return a value on a single row, but the value is based on a group of rows. A simple
example is SUM(). Say we wanted to return each zip code with the sum of the
population in the state. With window functions, this is easy:
SUM(totpop) OVER (PARTITION BY zc.stab) as stpop
FROM ZipCensus zc;
The PARTITION BY clause says “do the sum for all rows with the same value of
stab.” The result is that all zip codes in a given state have the same value for stpop.
A particularly interesting window function is ROW_NUMBER(). This assigns a
sequential value, starting with 1, to rows within each group.
SUM(totpop) OVER (PARTITION BY zc.stab) as stpop,
ROW_NUMBER() OVER (PARTITION BY zc.stab
ORDER BY totpop DESC
) as ZipPopRank
FROM ZipCensus zc
This query adds an additional ranking column to each row in the result set. The
value is 1 for the zip code with the highest population in each state, 2 for the
second highest, and so on.
SQL offers two other similar functions for ranking: RANK() and DENSE_RANK().
All three functions assign the first row a number of “1”. ROW_NUMBER() ignores
duplicates, just giving each row a different number. RANK() assigns duplicate
numbers when rows have the same value, but then skips the next numbers, so the
results have gaps. DENSE_RANK() is like rank except the resulting numbers have no