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 nextval                              FROM Sales.OrderValues;                              

Figure 1 shows the output of this query.

                              custid	orderdate	orderid	val	prevval	nextval                              -------	-----------	-------	-------	-------	--------                              1	2007-08-25	10643	814.50	NULL	878.00                              1	2007-10-03	10692	878.00	814.50	330.00                              1	2007-10-13	10702	330.00	878.00	845.80                              1	2008-01-15	10835	845.80	330.00	471.20                              1	2008-03-16	10952	471.20	845.80	933.50                              1	2008-04-09	11011	933.50	471.20	NULL                              2	2006-09-18	10308	88.80	NULL	479.75                              2	2007-08-08	10625	479.75	88.80	320.00                              2	2007-11-28	10759	320.00	479.75	514.40                              2	2008-03-04	10926	514.40	320.00	NULL                              ...                              

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 diffnext                              FROM Sales.OrderValues;                              

Figure 2 shows the output of this query.

                              custid	orderdate	orderid	val	diffprev	diffnext                              -------	-----------	-------	------	----------	---------                              1	2007-08-25	10643	814.50	NULL		-63.50                              1	2007-10-03	10692	878.00	63.50		548.00                              1	2007-10-13	10702	330.00	-548.00		-515.80                              1	2008-01-15	10835	845.80	515.80		374.60                              1	2008-03-16	10952	471.20	-374.60		-462.30                              1	2008-04-09	11011	933.50	462.30		NULL                              2	2006-09-18	10308	88.80	NULL		-390.95                              2	2007-08-08	10625	479.75	390.95		159.75                              2	2007-11-28	10759	320.00	-159.75		-194.40                              2	2008-03-04	10926	514.40	194.40		NULL                              ...                              

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 next2val                              FROM Sales.OrderValues;                              

Figure 3 shows the output of this query.

                              custid	orderdate	orderid	val	prev2val	next2val                              -------	-----------	-------	------	---------	--------                              1	2007-08-25	10643	814.50	0.00		330.00                              1	2007-10-03	10692	878.00	0.00		845.80                              1	2007-10-13	10702	330.00	814.50		471.20                              1	2008-01-15	10835	845.80	878.00		933.50                              1	2008-03-16	10952	471.20	330.00		0.00                              1	2008-04-09	11011	933.50	845.80		0.00                              2	2006-09-18	10308	88.80	0.00		320.00                              2	2007-08-08	10625	479.75	0.00		514.40                              2	2007-11-28	10759	320.00	88.80		0.00                              2	2008-03-04	10926	514.40	479.75		0.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_lastorder                              FROM Sales.OrderValues;                              

Figure 4 shows the output of this query.

                              custid	orderdate	orderid	val	val_firstorder	val_lastorder                              -------	-----------	-------	-------	---------------	--------------                              1	2007-08-25	10643	814.50	814.50		933.50                              1	2007-10-03	10692	878.00	814.50		933.50                              1	2007-10-13	10702	330.00	814.50		933.50                              1	2008-01-15	10835	845.80	814.50		933.50                              1	2008-03-16	10952	471.20	814.50		933.50                              1	2008-04-09	11011	933.50	814.50		933.50                              2	2006-09-18	10308	88.80	 88.80		514.40                              2	2007-08-08	10625	479.75	88.80		514.40                              2	2007-11-28	10759	320.00	88.80		514.40                              2	2008-03-04	10926	514.40	88.80		514.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 difflast                              FROM Sales.OrderValues;                              

Figure 5 shows the output of this query.

                              custid	orderdate	orderid	val	difffirst	difflast                              -------	-----------	-------	-------	---------	---------                              1	2007-08-25	10643	814.50	0.00		-119.00                              1	2007-10-03	10692	878.00	63.50		-55.50                              1	2007-10-13	10702	330.00	-484.50		-603.50                              1	2008-01-15	10835	845.80	31.30		-87.70                              1	2008-03-16	10952	471.20	-343.30		-462.30                              1	2008-04-09	11011	933.50	119.00		0.00                              2	2006-09-18	10308	88.80	0.00		-425.60                              2	2007-08-08	10625	479.75	390.95		-34.65                              2	2007-11-28	10759	320.00	231.20		-194.40                              2	2008-03-04	10926	514.40	425.60		0.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 cumedist                              FROM Stats.Scores;                              

Figure 6 shows the output of this query.

                              testid		studentid	score	percentrank	cumedist                              ----------	----------	-----	------------	---------                              Test ABC	Student E	50	0.000		0.111                              Test ABC	Student C	55	0.125		0.333                              Test ABC	Student D	55	0.125		0.333                              Test ABC	Student H	65	0.375		0.444                              Test ABC	Student I	75	0.500		0.556                              Test ABC	Student F	80	0.625		0.778                              Test ABC	Student B	80	0.625		0.778                              Test ABC	Student A	95	0.875		1.000                              Test ABC	Student G	95	0.875		1.000                              Test XYZ	Student E	50	0.000		0.100                              Test XYZ	Student C	55	0.111		0.300                              Test XYZ	Student D	55	0.111		0.300                              Test XYZ	Student H	65	0.333		0.400                              Test XYZ	Student I	75	0.444		0.500                              Test XYZ	Student B	80	0.556		0.700                              Test XYZ	Student F	80	0.556		0.700                              Test XYZ	Student G	95	0.778		1.000                              Test XYZ	Student J	95	0.778		1.000                              Test XYZ	Student A	95	0.778		1.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 percentilecont                              FROM Stats.Scores;                              

Figure 7 shows the output of this query.

                              testid		score	percentiledisc	percentilecont                              ----------	-----	--------------	----------------------                              Test ABC	50	75		75                              Test ABC	55	75		75                              Test ABC	55	75		75                              Test ABC	65	75		75                              Test ABC	75	75		75                              Test ABC	80	75		75                              Test ABC	80	75		75                              Test ABC	95	75		75                              Test ABC	95	75		75                              Test XYZ	50	75		77.5                              Test XYZ	55	75		77.5                              Test XYZ	55	75		77.5                              Test XYZ	65	75		77.5                              Test XYZ	75	75		77.5                              Test XYZ	80	75		77.5                              Test XYZ	80	75		77.5                              Test XYZ	95	75		77.5                              Test XYZ	95	75		77.5                              Test XYZ	95	75		77.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.