PERCENTILES
Updated: 31 July 2015
Use the table-valued function PERCENTILES to calculate any number of percentiles from a set of values.
Syntax
SELECT * FROM [wct].[PERCENTILES](
<@xQuery, nvarchar(max),>
,<@P, nvarchar(max),>
,<@Pmeth, nvarchar(4000),>)
Arguments
@xQuery
A SQL statement which returns a resultant table consisting of a single column of numeric data sorted in ascending order. @xQuery
@P
A comma separated list of values between 0 and 1 from which the percentile values are calculated.
@Pmeth
The percentile calculation method. Use 'INC' to return the equivalent of the Excel PERCENTILE.INC function and 'EXC' for return the equivalent of the PERCENTILE.EXC function.
Return Types
RETURNS TABLE (
[P] [float] NULL,
[V] [float] NULL
)
Table Description
P
the percentile as supplied in @P.
V
the Pth percentile value
Remarks
· Any values less than zero or greater than 1 in @P are ignored.
· If @P IS NULL, then PERCENTILES generates values from 0 to 1 in increments of .01.
· Percentile values are interpolated when the requested percentile lies between 2 values in the ordered resultant table.
· If the results of @xQuery are not in ascending order, then the percentiles returned by the function will be unreliable.
Examples
In this example we will select the 25th, 50th, 75th and 100th percentiles of the ordered list {3,6,7,8,8,10,13,15,16,20} using the 'INC' method.
SELECT
*
FROM
wct.PERCENTILES(
'SELECT x FROM (VALUES (3),(7),(8),(10),(13),(15),(8),(16),(20),(6))n(x) ORDER BY 1'
,'0.25,0.50,0.75,1'
,'INC'
)
This produces the following result.
In this example we use the SeriesFloat function to insert 100,000 rows from a normal distribution having a mean of 100 and a standard deviation of 15 and we return all the percentiles from 0 to 1 in increments of .01 using the 'INC' method.
SELECT
ROUND(SeriesValue,0) as X
INTO
#g
FROM
wct.SeriesFloat(100,15,NULL,100000,'N')
SELECT
*
FROM
wct.PERCENTILES('SELECT X FROM #g ORDER BY 1',NULL,NULL)
ORDER BY
1
DROP TABLE
#g
This produces the following result.
In this example we insert 10,000 rows from a normal distribution with a mean of 50 and a standard deviation of 25 into a table and calculate the percentiles from 0 to 1 in increments of .01 using the 'EXC' method.
SELECT
ROUND(SeriesValue,1) as X
INTO
#g
FROM
wct.SeriesFloat(50,25,NULL,10000,'N')
SELECT
*
FROM
wct.PERCENTILES('SELECT X FROM #g ORDER BY 1',NULL,'EXC')
ORDER BY
1
DROP TABLE
#g
This produces the following result.
In this example we calculate the 64th percentile from the ordered set {15,20,35,40,50} using the 'EXC' method.
SELECT
*
FROM
wct.PERCENTILES(
'SELECT x FROM (VALUES (15),(20),(35),(40),(50))n(x) ORDER BY 1'
,0.64
,'EXC'
)
This produces the following result.
See Also