How to Use SQL:2011 Window Functions
Added in SQL:2011 are five window functions that evaluate an expression in a row R2 that is somewhere in the window frame of the current row R1. The functions are LAG, LEAD, NTH_VALUE, FIRST_VALUE, and LAST_VALUE.
These functions enable you to pull information from specified rows that are within the window frame of the current row.
Look back with the LAG function
The LAG function enables you to retrieve information from the current row in the window you’re examining as well as information from another row that you specify that precedes the current row.
Suppose, for example, that you have a table that records the total sales for each day for the current year. One thing you might want to know is how today’s sales compare to yesterday’s. You could do this with the LAG function, as follows:
SELECT TotalSale AS TodaySale, LAG (TotalSale) OVER (ORDER BY SaleDate) AS PrevDaySale FROM DailyTotals;
For each row in DailyTotals, this query would return a row listing that row’s total sales figure and the previous day’s total sales figure. The default offset is 1, which is why the previous day’s result is returned rather than any other.
To compare the current day’s sales to those of a week prior, you could use the following:
SELECT TotalSale AS TodaySale, LAG (TotalSale, 7) OVER (ORDER BY SaleDate) AS PrevDaySale FROM DailyTotals;
The first seven rows in a window frame will not have a predecessor that is a week older. The default response to this situation is to return a null result for PrevDaySale. If you would prefer some other result to a null result, for example zero, you can specify what you want returned in this situation instead of the default null value, for example, 0 (zero), as shown here:
SELECT TotalSale AS TodaySale, LAG (TotalSale, 7, 0) OVER (ORDER BY SaleDate) AS PrevDaySale FROM DailyTotals;
The default behavior is to count rows that have a lag extent, which in this case is TotalSale, which contains a null value. If you want to skip over such rows and count only rows that have an actual value in the lag extent, you can do so by adding the keywords IGNORE NULLS as shown in the following variant of the example:
SELECT TotalSale AS TodaySale, LAG (TotalSale, 7, 0) IGNORE NULLS OVER (ORDER BY SaleDate) AS PrevDaySale FROM DailyTotals;
Look ahead with the LEAD function
The LEAD window function operates exactly the same way the LAG function operates except that, instead of looking back to a preceding row, it looks ahead to a row following the current row in the window frame. An example might be:
SELECT TotalSale AS TodaySale, LEAD (TotalSale, 7, 0) IGNORE NULLS OVER (ORDER BY SaleDate) AS NextDaySale FROM DailyTotals;
Look to a specified row with the NTH_VALUE function
The NTH_VALUE function is similar to the LAG and LEAD functions, except that instead of evaluating an expression in a row preceding or following the current row, it evaluates an expression in a row that is at a specified offset from the first or the last row in the window frame.
Here’s an example:
SELECT TotalSale AS ChosenSale, NTH_VALUE (TotalSale, 2) FROM FIRST IGNORE NULLS OVER (ORDER BY SaleDate) ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING ) AS EarlierSale FROM DailyTotals;
In this example, EarlierSale is evaluated as follows:
The window frame associated with the current row is formed. It includes the ten preceding and the ten following rows.
TotalSale is evaluated in each row of the window frame.
IGNORE NULLS is specified, so any rows containing a null value for TotalSale are skipped.
Starting from the first value remaining after the exclusion of rows containing a null value for TotalSale, move forward by two rows (forward because FROM FIRST was specified).
The value of EarlierSale is the value of TotalSale from the specified row.
If you don’t want to skip rows that have a null value for TotalSale, specify RESPECT NULLS rather than IGNORE NULLS. The NTH_VALUE function works similarly if you specify FROM LAST instead of FROM FIRST, except instead of counting forward from the first record in the window frame, you count backward from the last record in the window frame.
The number specifying the number of rows to count is still positive, even though you’re counting backward rather than forward.
Look to a very specific value with FIRST_VALUE and LAST_VALUE
The FIRST_VALUE and LAST_VALUE functions are special cases of the NTH_VALUE function. FIRST_VALUE is equivalent to NTH_VALUE where FROM FIRST is specified and the offset is 0 (zero). LAST_VALUE is equivalent to NTH_VALUE where FROM LAST is specified and the offset is 0. With both of these, you can choose to either ignore or respect nulls.