Microsoft SQL Server 2012: How to Write T-SQL Window Functions, Part 2

Last month, I started a series of articles about the profound window functions and their support in SQL Server 2012. I explained the concept of SQL windowing, I described the elements involved in window specifications (partitioning, ordering, and framing), and I discussed the difference between the two window frame unit options ROWS and RANGE. I showed examples using window aggregate functions. This month, I cover two other types of window functions: offset and distribution. If you missed part 1 of this article series, see "SQL Server 2012's Window Functions, Part 1."

As a reminder, you need to use SQL Server Denali CTP3 or later to run the sample code for this series of articles.  You also need to install the sample database TSQL2012. You also need to download the source code file that creates and populates the sample database.

Window Offset Functions

Window offset functions let you return a value from a row that's in a certain offset from the current row (LAG and LEAD) or from the first or last row in the window frame (FIRST_VALUE and LAST_VALUE). Let's start with the LAG and LEAD functions.

Window offset functions LAG and LEAD. The LAG function returns a value from a row in the window partition that, based on the window ordering, is the specified number of rows before the current row. Similarly, the LEAD function returns a value from a row in the window partition that, based on the window ordering, is the specified number of rows after the current row. By default, the LAG and LEAD functions assume an offset of one row if an explicit offset wasn't specified.

You indicate the value you want to return from the row in question as the first argument to LAG and LEAD. If you want to indicate an explicit offset, you indicate it as the second argument to the function. If a row isn't found in the requested offset, the functions return a NULL. If you want to return a different value in case a row isn't found, you can indicate such a value as the third argument to the function.

As an example, the following query returns for each customer order the value of the customer's previous order (LAG), as well as the value of the customer's next order (LEAD):

USE TSQL2012;SELECT custid, orderdate, orderid, val,  LAG(val)  OVER(PARTITION BY custid         ORDER BY orderdate, orderid) AS prevval,  LEAD(val) OVER(PARTITION BY custid         ORDER BY orderdate, orderid) AS nextvalFROM Sales.OrderValues;

Figure 1 shows the output of this query.

custidorderdateorderidvalprevvalnextval-----------------------------------------------12007-08-2510643814.50NULL878.0012007-10-0310692878.00814.50330.0012007-10-1310702330.00878.00845.8012008-01-1510835845.80330.00471.2012008-03-1610952471.20845.80933.5012008-04-0911011933.50471.20NULL22006-09-181030888.80NULL479.7522007-08-0810625479.7588.80320.0022007-11-2810759320.00479.75514.4022008-03-0410926514.40320.00NULL...

Because the calculations are supposed to be performed for each customer independently, the functions partition the window by custid. As for window ordering, it's based on orderdate and orderid as a tiebreaker. Observe that the functions rely on the default offset 1 and return NULL when a row isn't found in the applicable offset.

You can freely mix in the same expression detail elements from the row as well as calls to window functions. For example, the following query computes the difference between the customer's current and previous order values, as well as the difference between the customer's current and next order values:

SELECT custid, orderdate, orderid, val,  val - LAG(val)  OVER(PARTITION BY custid           ORDER BY orderdate, orderid) AS diffprev,  val - LEAD(val) OVER(PARTITION BY custid           ORDER BY orderdate, orderid) AS diffnextFROM Sales.OrderValues;

Figure 2 shows the output of this query.

custidorderdateorderidvaldiffprevdiffnext--------------------------------------------------12007-08-2510643814.50NULL-63.5012007-10-0310692878.0063.50548.0012007-10-1310702330.00-548.00-515.8012008-01-1510835845.80515.80374.6012008-03-1610952471.20-374.60-462.3012008-04-0911011933.50462.30NULL22006-09-181030888.80NULL-390.9522007-08-0810625479.75390.95159.7522007-11-2810759320.00-159.75-194.4022008-03-0410926514.40194.40NULL...

As I mentioned, the default when an explicit offset isn't specified is 1 -- but you can indicate your own value as a second argument to the function. You can also indicate as a third argument what to return instead of a NULL when a row isn't found in the requested offset. Here's an example that specifies 2 as the offset and 0 as the value to return when a row isn't found:

SELECT custid, orderdate, orderid, val,  LAG(val, 2, 0)  OVER(PARTITION BY custid           ORDER BY orderdate, orderid) AS prev2val,  LEAD(val, 2, 0) OVER(PARTITION BY custid           ORDER BY orderdate, orderid) AS next2valFROM Sales.OrderValues;

Figure 3 shows the output of this query.

custidorderdateorderidvalprev2valnext2val------------------------------------------------12007-08-2510643814.500.00330.0012007-10-0310692878.000.00845.8012007-10-1310702330.00814.50471.2012008-01-1510835845.80878.00933.5012008-03-1610952471.20330.000.0012008-04-0911011933.50845.800.0022006-09-181030888.800.00320.0022007-08-0810625479.750.00514.4022007-11-2810759320.0088.800.0022008-03-0410926514.40479.750.00...

Window offset functions FIRST_VALUE and LAST_VALUE. The functions FIRST_VALUE and LAST_VALUE return the requested value from the first and last rows, respectively, from the applicable window frame. In "SQL Server 2012's Window Functions, Part 1," I described the concept of a window frame in detail. Quite often, you just want to return the first and last values from the window partition in general and not necessarily from a more restricted window frame. However, this can be a bit tricky to achieve. First, let me provide a query that correctly returns along with each customer's order the values of the customer's first and last orders using the FIRST_VALUE and LAST_VALUE functions, respectively:

SELECT custid, orderdate, orderid, val,  FIRST_VALUE(val) OVER(PARTITION BY custid            ORDER BY orderdate, orderid            ROWS UNBOUNDED PRECEDING)    AS val_firstorder,  LAST_VALUE(val)  OVER(PARTITION BY custid            ORDER BY orderdate, orderid            ROWS BETWEEN CURRENT ROW                 AND UNBOUNDED FOLLOWING)    AS val_lastorderFROM Sales.OrderValues;

Figure 4 shows the output of this query.

custidorderdateorderidvalval_firstorderval_lastorder-------------------------------------------------------------12007-08-2510643814.50814.50933.5012007-10-0310692878.00814.50933.5012007-10-1310702330.00814.50933.5012008-01-1510835845.80814.50933.5012008-03-1610952471.20814.50933.5012008-04-0911011933.50814.50933.5022006-09-181030888.80 88.80514.4022007-08-0810625479.7588.80514.4022007-11-2810759320.0088.80514.4022008-03-0410926514.4088.80514.40...

You're probably wondering: Why the lengthy window descriptors? More specifically, why the need for an explicit ROWS clause if the request isn't concerned with a more restricted window frame, but rather the partition in general?

This has to do with the fact that the window order clause is in essence only part of the framing specification, and if an explicit window frame isn't specified, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. For the FIRST_VALUE function, relying on the default frame would still yield the correct result because the first row in the default window frame is the first row in the window partition. Still, last month I gave a recommendation to stick to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead of the default RANGE option when possible.

As for the LAST_VALUE function, try to think what it would mean to rely on the default framing option. It means that you will basically always get the value from the current row, because that's the last row in the default window frame. So for LAST_VALUE, you really need to be explicit about the frame specification and indicate the ROWS unit, CURRENT ROW as the lower bound and UNBOUNDED FOLLOWING as the upper bound (assuming you want the value from the last row in the window partition).

Just as I showed with LAG and LEAD, FIRST_VALUE and LAST_VALUE can likewise be mixed in expressions that also involve detail elements from the row. For example, the following query returns the difference between the customer's current and first order values, as well as the difference between the customer's current and last order values:

SELECT custid, orderdate, orderid, val,  val - FIRST_VALUE(val) OVER(PARTITION BY custid              ORDER BY orderdate, orderid              ROWS UNBOUNDED PRECEDING) AS difffirst,  val - LAST_VALUE(val)  OVER(PARTITION BY custid              ORDER BY orderdate, orderid              ROWS BETWEEN CURRENT ROW                   AND UNBOUNDED FOLLOWING) AS difflastFROM Sales.OrderValues;

Figure 5 shows the output of this query.

custidorderdateorderidvaldifffirstdifflast--------------------------------------------------12007-08-2510643814.500.00-119.0012007-10-0310692878.0063.50-55.5012007-10-1310702330.00-484.50-603.5012008-01-1510835845.8031.30-87.7012008-03-1610952471.20-343.30-462.3012008-04-0911011933.50119.000.0022006-09-181030888.800.00-425.6022007-08-0810625479.75390.95-34.6522007-11-2810759320.00231.20-194.4022008-03-0410926514.40425.600.00...

As food for thought, try to think why a grouped version (as opposed to the existing windowed version) of the FIRST_VALUE and LAST_VALUE functions would have made perfect sense, although -- alas -- there's no support for such a version. Also, can you think of a workaround that would achieve the same result?

Window Distribution Functions

Window distribution functions provide statistical computations. SQL Server 2012 implements two window rank distribution functions called PERCENT_RANK and CUME_DIST and two window inverse distribution functions called PERCENTILE_DISC and PERCENTILE_CONT. I'll start with window rank distribution functions.

To explain what the PERCENT_RANK and CUME_DIST functions calculate, I need to first provide a definition of the elements involved in their internal computation. Let rk be the rank of the row (using the same partitioning and ordering as the window function's partitioning and ordering). Let nr be the number of rows in the partition. Let np be the number of rows that precede or peer with the current row.

Then, the PERCENT_RANK computes a percentile rank as (rk - 1) / (nr - 1), and CUME_DIST computes a cumulative distribution as np / nr. As an example, the following query computes the percentile rank and cumulative distribution of student test scores, partitioned by testid and ordered by score:

SELECT testid, studentid, score,  PERCENT_RANK() OVER(PARTITION BY testid          ORDER BY score) AS percentrank,  CUME_DIST()    OVER(PARTITION BY testid          ORDER BY score) AS cumedistFROM Stats.Scores;

Figure 6 shows the output of this query.

testidstudentidscorepercentrankcumedist----------------------------------------------Test ABCStudent E500.0000.111Test ABCStudent C550.1250.333Test ABCStudent D550.1250.333Test ABCStudent H650.3750.444Test ABCStudent I750.5000.556Test ABCStudent F800.6250.778Test ABCStudent B800.6250.778Test ABCStudent A950.8751.000Test ABCStudent G950.8751.000Test XYZStudent E500.0000.100Test XYZStudent C550.1110.300Test XYZStudent D550.1110.300Test XYZStudent H650.3330.400Test XYZStudent I750.4440.500Test XYZStudent B800.5560.700Test XYZStudent F800.5560.700Test XYZStudent G950.7781.000Test XYZStudent J950.7781.000Test XYZStudent A950.7781.000

As an exercise, try to write SQL Server 2008-compatible solutions that compute percentile rank and cumulative distribution.

SQL Server 2012 also implements two window inverse distribution functions called PERCENTILE_DISC and PERCENTILE_CONT. A percentile p, loosely speaking, is the value v from the population, such that p percent of the values are less than v. For example, if 50 percent of the values in the population are less than some value v, then v is the 50th percentile, also known as the median.

The two function variations implement two different distribution models. The PERCENTILE_DISC function implements a discrete distribution model in which the returned value must be one of the values from the population. The PERCENTILE_CONT function implements a continuous distribution model in which the returned value is interpolated from the existing values, assuming continuous distribution.

As a simple example, in case of an even number of values, PERCENTILE_CONT will compute the median as the average of the two middle points. It gets far trickier to understand the interpolation when the requested percentile isn't the median, but fortunately the function has this complexity embedded into it.

For example, the following code computes the median of student test scores per test using both PERCENTILE_DISC and PERCENTILE_CONT:

DECLARE @pct AS FLOAT = 0.5;SELECT testid, score,  PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score)    OVER(PARTITION BY testid) AS percentiledisc,  PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score)    OVER(PARTITION BY testid) AS percentilecontFROM Stats.Scores;

Figure 7 shows the output of this query.

testidscorepercentilediscpercentilecont---------------------------------------------------Test ABC507575Test ABC557575Test ABC557575Test ABC657575Test ABC757575Test ABC807575Test ABC807575Test ABC957575Test ABC957575Test XYZ507577.5Test XYZ557577.5Test XYZ557577.5Test XYZ657577.5Test XYZ757577.5Test XYZ807577.5Test XYZ807577.5Test XYZ957577.5Test XYZ957577.5Test XYZ957577.5

As an exercise, see if you can figure out how to implement a SQL Server 2008-compatible solution for both functions, given any percent as input.

Still More to Come

This article is the second in a series of articles about window functions. Last month, I introduced the concept of windowing. This month, I covered window offset functions and window distribution functions. Next month, I'll cover optimization of window functions.