SQL Server PERCENTILES Function

Updated 2023-10-31 14:51:16.883000

Description

Use the table-valued function PERCENTILES to calculate any number of percentiles from a set of values.

Syntax

SELECT * FROM [westclintech].[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.

@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 Type

table

colName	colDatatype	colDesc
P	float	the percentile as supplied in @P.
V	float	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 25 ^th , 50 ^th , 75 ^th and 100 ^th 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.

P	V
0.25	7.25
0.5	9
0.75	14.5
1	20

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.

P	V
0	41
0.01	65
0.02	69
0.03	72
0.04	74
0.05	75
0.06	77
0.07	78
0.08	79
0.09	80
0.1	81
0.11	82
0.12	82
0.13	83
0.14	84
0.15	84
0.16	85
0.17	86
0.18	86
0.19	87
0.2	87
0.21	88
0.22	88
0.23	89
0.24	89
0.25	90
0.26	90
0.27	91
0.28	91
0.29	92
0.3	92
0.31	93
0.32	93
0.33	93
0.34	94
0.35	94
0.36	95
0.37	95
0.38	95
0.39	96
0.4	96
0.41	96
0.42	97
0.43	97
0.44	98
0.45	98
0.46	98
0.47	99
0.48	99
0.49	99
0.5	100
0.51	100
0.52	101
0.53	101
0.54	101
0.55	102
0.56	102
0.57	103
0.58	103
0.59	103
0.6	104
0.61	104
0.62	104
0.63	105
0.64	105
0.65	106
0.66	106
0.67	106
0.68	107
0.69	107
0.7	108
0.71	108
0.72	109
0.73	109
0.74	109
0.75	110
0.76	110
0.77	111
0.78	111
0.79	112
0.8	113
0.81	113
0.82	114
0.83	114
0.84	115
0.85	115
0.86	116
0.87	117
0.88	117
0.89	118
0.9	119
0.91	120
0.92	121
0.93	122
0.94	123
0.95	124
0.96	126
0.97	128
0.98	131
0.99	135
1	163

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.

P	V
0	NULL
0.01	-8.599
0.02	-2.298
0.03	2.203
0.04	5.7
0.05	8.2
0.06	10.7
0.07	12.7
0.08	14.6
0.09	16
0.1	17.2
0.11	18.7
0.12	20
0.13	21.3
0.14	22.4
0.15	23.5
0.16	24.616
0.17	25.7
0.18	26.7
0.19	27.6
0.2	28.4
0.21	29.3
0.22	30.2
0.23	30.923
0.24	31.8
0.25	32.6
0.26	33.3
0.27	34.1
0.28	34.9
0.29	35.7
0.3	36.5
0.31	37.2
0.32	37.9
0.33	38.7
0.34	39.334
0.35	40
0.36	40.8
0.37	41.4
0.38	42
0.39	42.6
0.4	43.3
0.41	44
0.42	44.7
0.43	45.4
0.44	46.144
0.45	46.8
0.46	47.446
0.47	48
0.48	48.6
0.49	49.2
0.5	49.8
0.51	50.4
0.52	51.1
0.53	51.7
0.54	52.3
0.55	52.8
0.56	53.4
0.57	53.9
0.58	54.6
0.59	55.2
0.6	55.9599999999999
0.61	56.6
0.62	57.3
0.63	58.063
0.64	58.7
0.65	59.5
0.66	60.3
0.67	60.8
0.68	61.5
0.69	62.1
0.7	62.7
0.71	63.6
0.72	64.3
0.73	65
0.74	65.8
0.75	66.7
0.76	67.6
0.77	68.5
0.78	69.3780000000001
0.79	70.2
0.8	71
0.81	71.9
0.82	73
0.83	74.1
0.84	75.3
0.85	76.1
0.86	77.2860000000001
0.87	78.4870000000001
0.88	79.8879999999999
0.89	81.1
0.9	82.7
0.91	84.091
0.92	85.592
0.93	87.2
0.94	89
0.95	91.5
0.96	94.2
0.97	97.8
0.98	102.298
0.99	108.4
1	NULL

In this example we calculate the 64^th 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.

P	V
0.64	39.2

Resources

SQL Server PERCENTILES Function

Description

Syntax

Arguments

@xQuery

@P

@Pmeth

Return Type

Remarks

Examples

See Also