T-SQL:从列计算第 N 个百分位值 [英] T-SQL: Calculating the Nth Percentile Value from column

问题描述

我有一列数据，其中一些是 NULL 值，我希望从中提取单个 90% 的值值:

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

对于上述内容，我正在寻找一种技术，它在搜索第 90 个百分位数时返回值 900，在搜索第 80 个百分位数时返回 800，等等.一个类似的函数是 AVG(ColA)，它为上述数据返回 550，或返回 100 等的 MIN(ColA)

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.

有什么建议吗?

推荐答案

如果你想准确地得到第 90 个百分位的值，不包括 NULL，我建议直接进行计算.以下版本计算行数和行数，并选择合适的值:

If you want to get exactly the 90th percentile value, excluding NULLs, I would suggest doing the calculation directly. The following version calculates the row number and number of rows, 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 个或您想要的任何值.

I put the condition in the SELECT clause rather than the WHERE clause, so you can easily get the 50th percentile, 17th, or whatever values you want.

这篇关于T-SQL:从列计算第 N 个百分位值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！