I have been living and breathing T-SQL windowing functions for years. I couldn’t tell you how many times I have presented on the topic, but I expect it is well over 50. I had also used these functions quite often when I worked in consulting, but now I spend more time teaching others instead of solving real queries with the functions. I love my job as editor of Simple-Talk, but I do sometimes miss those days of writing and tuning queries for customers.

I was recently playing with the analytical group of windowing functions, and I wanted to understand how they worked “under the covers.” I ran into a little logic puzzle with` PERCENTILE_CONT`

by trying to write a query that returned the same results using pre-2012 functionality.

Given a list of ranked values, you can use the `PERCENTILE_CONT`

function to find the value at a specific percentile. For example, if you have the grades of 100 students, you can use `PERCENTILE_CONT`

to locate the score in the middle of the list, the median, or at some other percent such as the grade at 90%. This doesn’t mean that the score was 90%; it means that the position of the score was at the 90^{th} percentile. If there is not a value at the *exact *location, `PERCENTILE_CONT`

interpolates the answer.

The definition from Microsoft for `PERCENTILE_CONT`

is:

*“Calculates a percentile based on a continuous distribution of the column value in SQL Server. The result is interpolated and might not be equal to any of the specific values in the column.”*

There is also a function called `PERCENTILE_DISC`

that is similar. Instead of interpolating to find the exact value, it returns one of the actual values in the set.

At first glance, the `PERCENTILE_CONT`

function doesn’t seem so difficult to understand, but I had a hard time writing a query that produced the same results using older functionality. Before I show you the path I travelled to come up with the pre-2012 solution, take a look at how to use `PERCENTILE_CONT`

with an example from AdventureWorks that ranks the count of sales for each month in a given year. I also include `PERCENTILE_DISC`

in this query so that you can see the difference between the two functions.

1 2 3 4 5 6 7 8 9 10 |
SELECT MONTH(SOH.OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY COUNT(*)) OVER () AS Median, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY COUNT(*)) OVER () AS NotTheMedian FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(SOH.OrderDate); |

The `PERCENTILE_DISC`

function returns the value 321, which is close to but not the median. The `PERCENTILE_CONT`

function returns the average of the two values around the median, 321 and 336, for an answer of 328.5. If there were an odd number of items in the list, then the two answers would be the same. The results of `PERCENTILE_CONT`

and `PERCENTILE_DISC`

do not depend on the values in the set, just the position. For example, if you tried to find the median of this list (1,2,3,4,1000) the answer is 3. It doesn’t matter that 1000 would skew the results of the *mean*.

The syntax of `PERCENTILE_CONT`

and `PERCENTILE_DISC`

is different than the other windowing functions because these require the `WITHIN GROUP`

clause. Inside that, you must provide an `ORDER BY`

expression that returns a list of numbers such as scores, heights, sales, etc. You still need an `OVER`

clause, but it supports only the `PARTITION BY`

.

Here’s another example looking for three different percentiles:

1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT MONTH(SOH.OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY COUNT(*)) OVER () AS [25%], PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY COUNT(*)) OVER () AS [50%], PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY COUNT(*)) OVER () AS [75%] FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(SOH.OrderDate); |

This example returns the `PERCENTILE_CONT`

values at 25%, 50%, and 75% respectively. These will be used as examples for testing the code for the pre-2012 solution.

## A Possible Formula

Of course, I started by searching for an explanation of the function, but I ran into a formula that worked for 50% but fell apart when checking any other percentile. Here’s the description from the article:

- Find the row number at the percentile with this formula:
`RN = (1 + (P*(N-1))`

where`P`

is the percentile and`N`

is the number of rows. - Use the
`CEILING`

and`FLOOR`

functions to find the rows above (`CRN`

) and below (`FRN`

) the row number found in step 1. - If
`CRN = RN`

and`FRN = RN`

, use the value found at`RN`

. - Otherwise, add together the results of the values found at those two rows each multiplied by
`P`

.`P * Value at CRN + P * Value at FRN`

Here are the calculations for each of the percentiles in this example:

The formula works only with the 50^{th} percentile and not for the others. The calculated values were not even close for 25% and 75%.

Here’s a batch that you can use to verify the results. Change the value of `@P`

to try different percentiles:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
DECLARE @P DECIMAL(2, 2) = 0.5; --Using Percentile_Cont WITH PC AS ( SELECT MONTH(OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, PERCENTILE_CONT(@P) WITHIN GROUP(ORDER BY COUNT(*)) OVER() AS PercentileCont FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(OrderDate)) SELECT DISTINCT PC.PercentileCont FROM PC; --Works for the median WITH GetRowNumbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*)) AS RowNum, MONTH(SOH.OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, (1 + (@P * (COUNT(*) OVER () - 1))) AS RN, CEILING((1 + (@P * (COUNT(*) OVER () - 1)))) AS CRN, FLOOR((1 + (@P * (COUNT(*) OVER () - 1)))) AS FRN FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(SOH.OrderDate)), FindValues AS (SELECT MIN( CASE WHEN GetRowNumbers.RN = GetRowNumbers.CRN AND GetRowNumbers.RN = GetRowNumbers.FRN THEN GetRowNumbers.OrderCount END ) AS ActualValue, SUM( CASE WHEN GetRowNumbers.RowNum = GetRowNumbers.FRN THEN GetRowNumbers.OrderCount END ) AS FloorValue, SUM( CASE WHEN GetRowNumbers.RowNum = GetRowNumbers.CRN THEN GetRowNumbers.OrderCount END ) AS CeilingValue FROM GetRowNumbers) SELECT COALESCE(FindValues.ActualValue, FindValues.FloorValue * @P + FindValues.CeilingValue * @P) AS CalcAnswer FROM FindValues; |

The first CTE, `GetRowNumbers`

, completes step 1, finding the correct row numbers. The second CTE, `FindValues`

, completes step 2, locating the values. The outer query completes steps 3 and 4, checking for an exact value or applying the formula.

Since the formula was not correct, I had to come up with something else.

## A Logical Next Step

My first thought was that the difference between the two rows should be multiplied by `P`

and added to the bottom value. This didn’t work either, even though it seemed to make sense. Again, this works for the 50^{th} percentile but falls apart for the others. One good thing about this revised formula is that the answers were always between the floor and ceiling values, but in one case my answer was too low and in the other too high.

At this point, I was beginning to question the function itself since my idea made sense, well, to me at least.

Here’s a batch to test my hypothesis:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
DECLARE @P DECIMAL(2, 2) = 0.25; --Using Percentile_Cont WITH PC AS ( SELECT MONTH(OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, PERCENTILE_CONT(@P) WITHIN GROUP(ORDER BY COUNT(*)) OVER() AS PercentileCont FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(OrderDate)) SELECT DISTINCT PC.PercentileCont FROM PC; --My hypothesis WITH GetRowNumbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*)) AS RowNum, MONTH(SOH.OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, (1 + (@P * (COUNT(*) OVER () - 1))) AS RN, CEILING((1 + (@P * (COUNT(*) OVER () - 1)))) AS CRN, FLOOR((1 + (@P * (COUNT(*) OVER () - 1)))) AS FRN FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(SOH.OrderDate)), FindValues AS (SELECT MIN( CASE WHEN GetRowNumbers.RN = GetRowNumbers.CRN AND GetRowNumbers.RN = GetRowNumbers.FRN THEN GetRowNumbers.OrderCount END ) AS ActualValue, SUM( CASE WHEN GetRowNumbers.RowNum = GetRowNumbers.FRN THEN GetRowNumbers.OrderCount END ) AS BottomValue, SUM( CASE WHEN GetRowNumbers.RowNum = GetRowNumbers.CRN THEN GetRowNumbers.OrderCount END ) AS TopValue FROM GetRowNumbers) SELECT COALESCE(FindValues.ActualValue, FindValues.BottomValue + (FindValues.TopValue - FindValues.BottomValue) *@P) AS CalcAnswer FROM FindValues; |

The CTEs are the same as the previous example since steps 1 – 3 work. The difference is in the outer query where the new formula is used.

## The Solution

As I do with many problems, I started with a new query window and took it one step at a time. I knew that I was finding the correct rows for the floor and ceiling values at least. My next step was to look at the percentage of the difference between the two values. Was there a pattern?

I used this calculation:

`(Answer – Floor value)/(Ceiling value – Floor value) `

Here’s a table that shows the results:

Bazinga! Notice that the *Percent of difference* in each case is equal to the fractional part of *RN*. This makes sense! If you view the numbers as being on a line, the value found at precisely the *RN* point is the answer!

The correct value can be found at the floor row (left side of the decimal point of the row number) plus a percentage (right side of the decimal point of the row number) of the way to the ceiling row. Here is the correct formula:

`floor value + (ceiling value – floor value) * (RN modulo FRN)`

By using modulo, the fractional part of `RN`

can be found. Finally, here is the query:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
DECLARE @P DECIMAL(2, 2) = 0.25; --Using Percentile_Cont WITH PC AS ( SELECT MONTH(OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, PERCENTILE_CONT(@P) WITHIN GROUP(ORDER BY COUNT(*)) OVER() AS PercentileCont FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(OrderDate)) SELECT DISTINCT PC.PercentileCont FROM PC; --The answer! WITH GetRowNumbers AS ( SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*)) AS RowNum, MONTH(SOH.OrderDate) AS OrderMonth, COUNT(*) AS OrderCount, (1 + (@P * (COUNT(*) OVER () - 1))) AS RN, CEILING((1 + (@P * (COUNT(*) OVER () - 1)))) AS CRN, FLOOR((1 + (@P * (COUNT(*) OVER () - 1)))) AS FRN FROM Sales.SalesOrderHeader AS SOH WHERE SOH.OrderDate >= '1/1/2012' AND SOH.OrderDate < '1/1/2013' GROUP BY MONTH(SOH.OrderDate)), FindValues AS (SELECT MIN( CASE WHEN GetRowNumbers.RN = GetRowNumbers.CRN AND GetRowNumbers.RN = GetRowNumbers.FRN THEN GetRowNumbers.OrderCount END ) AS ActualValue, SUM( CASE WHEN GetRowNumbers.RowNum = GetRowNumbers.FRN THEN GetRowNumbers.OrderCount END ) AS FloorValue, SUM( CASE WHEN GetRowNumbers.RowNum = GetRowNumbers.CRN THEN GetRowNumbers.OrderCount END ) AS CeilingValue, RN, FRN FROM GetRowNumbers GROUP BY RN , FRn ) SELECT COALESCE(FindValues.ActualValue, FindValues.FloorValue + (FindValues.CeilingValue - FindValues.FloorValue) * (RN % FRN)) AS CalcAnswer FROM FindValues; |

Once again, the only difference is the formula in the outer query. The CTEs are the same.

## Conclusion

Figuring out how `PERCENTILE_CONT`

is calculated is not something that I needed to do, but I do enjoy solving the occasional T-SQL puzzle. The other windowing functions are not difficult to understand, but this one had me scratching my head for a bit. I thought that sharing my thought process might be interesting to others who are getting started with these functions.

The main benefit of using windowing functions is that they make writing queries to solve tricky problems easier, often eliminating anti-patterns such as cursors or triangular joins. The solution does take advantage of the windowing functions introduced with 2005. Trying to accomplish the same thing with SQL Server 2000 or earlier would be even more difficult.

Load comments