I have a column of data, some of which are NULL values, from which I wish to extract the single 90th percentile value:
ColA
-----
NULL
100
200
300
NULL
400
500
600
700
800
900
1000
For the above, I am looking for a technique which returns the value 900 when searching for the 90th percentile, 800 for the 80th percentile, etc. An analogous function would be AVG(ColA) which returns 550 for the above data, or MIN(ColA) which returns 100, etc.
Any suggestions?
Starting with SQL Server 2012, there are now PERCENTILE_DISC and PERCENTILE_CONT inverse distribution functions. These are (so far) only available as window functions, not as aggregate functions, so you would have to remove redundant results because of the lacking grouping, e.g. by using DISTINCT or TOP 1:
WITH t AS (
SELECT *
FROM (
VALUES(NULL),(100),(200),(300),
(NULL),(400),(500),(600),(700),
(800),(900),(1000)
) t(ColA)
)
SELECT DISTINCT percentile_disc(0.9) WITHIN GROUP (ORDER BY ColA) OVER()
FROM t
;