PERCENTILES
Updated 2023-10-31 14:51:16.883000
Syntax
SELECT * FROM [westclintech].[wct].[PERCENTILES](
<@xQuery, nvarchar(max),>
,<@P, nvarchar(max),>
,<@Pmeth, nvarchar(4000),>)
Description
Use the table-valued function PERCENTILES to calculate any number of percentiles from a set of values.
Arguments
@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.
@xQuery
A SQL statement which returns a resultant table consisting of a single column of numeric data sorted in ascending order.
Return Type
table
{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "ad0e3073-e180-4672-8f39-390b95484b52", "colName": "P", "colDatatype": "float", "colDesc": "the percentile as supplied in @P."}, {"id": "cd8c05dd-e96f-4f53-a99e-ff9058757cc7", "colName": "V", "colDatatype": "float", "colDesc": "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.
{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"P":"0.25","V":"7.25"},{"P":"0.5","V":"9"},{"P":"0.75","V":"14.5"},{"P":"1","V":"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.
{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"P":"0","V":"41"},{"P":"0.01","V":"65"},{"P":"0.02","V":"69"},{"P":"0.03","V":"72"},{"P":"0.04","V":"74"},{"P":"0.05","V":"75"},{"P":"0.06","V":"77"},{"P":"0.07","V":"78"},{"P":"0.08","V":"79"},{"P":"0.09","V":"80"},{"P":"0.1","V":"81"},{"P":"0.11","V":"82"},{"P":"0.12","V":"82"},{"P":"0.13","V":"83"},{"P":"0.14","V":"84"},{"P":"0.15","V":"84"},{"P":"0.16","V":"85"},{"P":"0.17","V":"86"},{"P":"0.18","V":"86"},{"P":"0.19","V":"87"},{"P":"0.2","V":"87"},{"P":"0.21","V":"88"},{"P":"0.22","V":"88"},{"P":"0.23","V":"89"},{"P":"0.24","V":"89"},{"P":"0.25","V":"90"},{"P":"0.26","V":"90"},{"P":"0.27","V":"91"},{"P":"0.28","V":"91"},{"P":"0.29","V":"92"},{"P":"0.3","V":"92"},{"P":"0.31","V":"93"},{"P":"0.32","V":"93"},{"P":"0.33","V":"93"},{"P":"0.34","V":"94"},{"P":"0.35","V":"94"},{"P":"0.36","V":"95"},{"P":"0.37","V":"95"},{"P":"0.38","V":"95"},{"P":"0.39","V":"96"},{"P":"0.4","V":"96"},{"P":"0.41","V":"96"},{"P":"0.42","V":"97"},{"P":"0.43","V":"97"},{"P":"0.44","V":"98"},{"P":"0.45","V":"98"},{"P":"0.46","V":"98"},{"P":"0.47","V":"99"},{"P":"0.48","V":"99"},{"P":"0.49","V":"99"},{"P":"0.5","V":"100"},{"P":"0.51","V":"100"},{"P":"0.52","V":"101"},{"P":"0.53","V":"101"},{"P":"0.54","V":"101"},{"P":"0.55","V":"102"},{"P":"0.56","V":"102"},{"P":"0.57","V":"103"},{"P":"0.58","V":"103"},{"P":"0.59","V":"103"},{"P":"0.6","V":"104"},{"P":"0.61","V":"104"},{"P":"0.62","V":"104"},{"P":"0.63","V":"105"},{"P":"0.64","V":"105"},{"P":"0.65","V":"106"},{"P":"0.66","V":"106"},{"P":"0.67","V":"106"},{"P":"0.68","V":"107"},{"P":"0.69","V":"107"},{"P":"0.7","V":"108"},{"P":"0.71","V":"108"},{"P":"0.72","V":"109"},{"P":"0.73","V":"109"},{"P":"0.74","V":"109"},{"P":"0.75","V":"110"},{"P":"0.76","V":"110"},{"P":"0.77","V":"111"},{"P":"0.78","V":"111"},{"P":"0.79","V":"112"},{"P":"0.8","V":"113"},{"P":"0.81","V":"113"},{"P":"0.82","V":"114"},{"P":"0.83","V":"114"},{"P":"0.84","V":"115"},{"P":"0.85","V":"115"},{"P":"0.86","V":"116"},{"P":"0.87","V":"117"},{"P":"0.88","V":"117"},{"P":"0.89","V":"118"},{"P":"0.9","V":"119"},{"P":"0.91","V":"120"},{"P":"0.92","V":"121"},{"P":"0.93","V":"122"},{"P":"0.94","V":"123"},{"P":"0.95","V":"124"},{"P":"0.96","V":"126"},{"P":"0.97","V":"128"},{"P":"0.98","V":"131"},{"P":"0.99","V":"135"},{"P":"1","V":"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.
{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V"}],"rows":[{"P":"0","V":"NULL"},{"P":"0.01","V":"-8.599"},{"P":"0.02","V":"-2.298"},{"P":"0.03","V":"2.203"},{"P":"0.04","V":"5.7"},{"P":"0.05","V":"8.2"},{"P":"0.06","V":"10.7"},{"P":"0.07","V":"12.7"},{"P":"0.08","V":"14.6"},{"P":"0.09","V":"16"},{"P":"0.1","V":"17.2"},{"P":"0.11","V":"18.7"},{"P":"0.12","V":"20"},{"P":"0.13","V":"21.3"},{"P":"0.14","V":"22.4"},{"P":"0.15","V":"23.5"},{"P":"0.16","V":"24.616"},{"P":"0.17","V":"25.7"},{"P":"0.18","V":"26.7"},{"P":"0.19","V":"27.6"},{"P":"0.2","V":"28.4"},{"P":"0.21","V":"29.3"},{"P":"0.22","V":"30.2"},{"P":"0.23","V":"30.923"},{"P":"0.24","V":"31.8"},{"P":"0.25","V":"32.6"},{"P":"0.26","V":"33.3"},{"P":"0.27","V":"34.1"},{"P":"0.28","V":"34.9"},{"P":"0.29","V":"35.7"},{"P":"0.3","V":"36.5"},{"P":"0.31","V":"37.2"},{"P":"0.32","V":"37.9"},{"P":"0.33","V":"38.7"},{"P":"0.34","V":"39.334"},{"P":"0.35","V":"40"},{"P":"0.36","V":"40.8"},{"P":"0.37","V":"41.4"},{"P":"0.38","V":"42"},{"P":"0.39","V":"42.6"},{"P":"0.4","V":"43.3"},{"P":"0.41","V":"44"},{"P":"0.42","V":"44.7"},{"P":"0.43","V":"45.4"},{"P":"0.44","V":"46.144"},{"P":"0.45","V":"46.8"},{"P":"0.46","V":"47.446"},{"P":"0.47","V":"48"},{"P":"0.48","V":"48.6"},{"P":"0.49","V":"49.2"},{"P":"0.5","V":"49.8"},{"P":"0.51","V":"50.4"},{"P":"0.52","V":"51.1"},{"P":"0.53","V":"51.7"},{"P":"0.54","V":"52.3"},{"P":"0.55","V":"52.8"},{"P":"0.56","V":"53.4"},{"P":"0.57","V":"53.9"},{"P":"0.58","V":"54.6"},{"P":"0.59","V":"55.2"},{"P":"0.6","V":"55.9599999999999"},{"P":"0.61","V":"56.6"},{"P":"0.62","V":"57.3"},{"P":"0.63","V":"58.063"},{"P":"0.64","V":"58.7"},{"P":"0.65","V":"59.5"},{"P":"0.66","V":"60.3"},{"P":"0.67","V":"60.8"},{"P":"0.68","V":"61.5"},{"P":"0.69","V":"62.1"},{"P":"0.7","V":"62.7"},{"P":"0.71","V":"63.6"},{"P":"0.72","V":"64.3"},{"P":"0.73","V":"65"},{"P":"0.74","V":"65.8"},{"P":"0.75","V":"66.7"},{"P":"0.76","V":"67.6"},{"P":"0.77","V":"68.5"},{"P":"0.78","V":"69.3780000000001"},{"P":"0.79","V":"70.2"},{"P":"0.8","V":"71"},{"P":"0.81","V":"71.9"},{"P":"0.82","V":"73"},{"P":"0.83","V":"74.1"},{"P":"0.84","V":"75.3"},{"P":"0.85","V":"76.1"},{"P":"0.86","V":"77.2860000000001"},{"P":"0.87","V":"78.4870000000001"},{"P":"0.88","V":"79.8879999999999"},{"P":"0.89","V":"81.1"},{"P":"0.9","V":"82.7"},{"P":"0.91","V":"84.091"},{"P":"0.92","V":"85.592"},{"P":"0.93","V":"87.2"},{"P":"0.94","V":"89"},{"P":"0.95","V":"91.5"},{"P":"0.96","V":"94.2"},{"P":"0.97","V":"97.8"},{"P":"0.98","V":"102.298"},{"P":"0.99","V":"108.4"},{"P":"1","V":"NULL"}]}
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.
{"columns":[{"field":"P","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"V","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"P":"0.64","V":"39.2"}]}
See Also
PERCENTILE - Aggregate function to calculate a percentile using the 'INC' method
PERCENTILE_EXC - Calculate percentile.
PERCENTILE_Q - Calculate percentile.
PERCENTRANK - Aggregate function to calculate a percent rank using the 'INC' method