Geek asks and answers

T-SQL: calculating Nth percent value from a column

I have a data column, some of which are NULL, from which I want to extract a single 90th percentile value:

ColA
-----
NULL
100
200
300
NULL
400
500
600
700
800
900
1000

For the above, I'm looking for a technique that returns 900 when searching for the 90th percentile, 800 for the 80th percentile, etc. A similar function would be AVG (ColA), which returns 550 for the above data, or MIN (ColA), which returns 100, etc.

Any suggestions?

+6
source share
3 answers

If you want to get the exact value of the 90th percentile, except for NULL, I would suggest doing the calculation directly. The next version calculates the line number and the number of lines and selects the appropriate value:

select max(case when rownum*1.0/numrows <= 0.9 then colA end) as percentile_90th
from (select colA,
             row_number() over (order by colA) as rownum,
             count(*) over (partition by NULL) as numrows
      from t
      where colA is not null
     ) t

SELECT, WHERE, 50- , 17- , .

+9
WITH
  percentiles AS
(
  SELECT
    NTILE(100) OVER (ORDER BY ColA) AS percentile,
    *
  FROM
    data
)
SELECT
  *
FROM
  percentiles
WHERE
  percentile = 90


. 100 , . , 100 , .

+4

SQL Server 2012, PERCENTILE_DISC PERCENTILE_CONT PERCENTILE_DISC PERCENTILE_CONT. () , , - , , DISTINCT 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
;

.

0

More articles:


All Articles