sql - 如何计算 SQL Server 中的第 90 个百分位数

Question

我需要计算这样的值列表的第 90 个百分位数:

0.0099
0.0129
0.0031
0.0219
0.2632
0.0124
0.0493
0.05
0.0433

我将如何进行计算？我知道答案是 0.0713,9 .有什么建议？

DECLARE @Temp TABLE(DATA float)

INSERT INTO @Temp VALUES(0.0099)
INSERT INTO @Temp VALUES(0.0129)
INSERT INTO @Temp VALUES(0.0031)
INSERT INTO @Temp VALUES(0.0219)
INSERT INTO @Temp VALUES(0.2632)
INSERT INTO @Temp VALUES(0.0124)
INSERT INTO @Temp VALUES(0.0493)
INSERT INTO @Temp VALUES(0.05) 
INSERT INTO @Temp VALUES(0.0433)

SELECT DATA
FROM @Temp
ORDER BY DATA ASC

--90th percentile 
SELECT ((
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 90 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA
                ) AS A
        ORDER BY DATA DESC) + 
        (
        SELECT TOP 1 DATA
        FROM   (
                SELECT  TOP 10 PERCENT DATA
                FROM    @Temp
                WHERE   DATA IS NOT NULL
                ORDER BY DATA DESC
                ) AS A
        ORDER BY DATA ASC)) / 2.0

Answer 1

Source

A percentile (or a centile) is a measure used in statistics indicating the value below which a given percentage of observations in a group of observations fall. For example, the 20th percentile is the value (or score) below which 20 percent of the observations may be found.

There is no standard definition of percentile

最近排名方法:

SELECT DATA
FROM (
    SELECT 
        DATA, 
        COUNT(1) OVER (PARTITION BY NULL) As N, 
        ROW_NUMBER() OVER (ORDER BY DATA) AS i
    FROM @Temp) t
WHERE
    i = ROUND(N * 90.00 / 100.00, 0, 0)

最近秩之间的线性插值方法:

DECLARE @P real = 90.00

SELECT MAX(tt.Pv)
FROM (
    SELECT 
        (CASE 
            WHEN i = k THEN DATA
            WHEN k = 0 AND P = MIN(CASE WHEN P > @P THEN P END) OVER (PARTITION BY NULL) THEN 
                DATA + N * (@P - P) / 100 * (MIN(CASE WHEN P > @P THEN DATA END) OVER (PARTITION BY NULL) - DATA)
            ELSE 0
        END) AS Pv
    FROM (
        SELECT 
            *,
            100.00 / N * (i - 1.00 / 2.00) AS P,
            CASE
                WHEN @P < 100.00 / N * (1 - 1.00 / 2.00) THEN 1 
                WHEN @P > 100.00 / N * (N - 1.00 / 2.00) THEN N
                WHEN @P = 100.00 / N * (i - 1.00 / 2.00) THEN i
                WHEN @P > 100.00 / N * (i - 1.00 / 2.00) THEN i
                ELSE 0
            END AS k
        FROM (
            SELECT 
                DATA, 
                COUNT(*) OVER (PARTITION BY NULL) As N, 
                ROW_NUMBER() OVER (ORDER BY DATA) AS i
            FROM @Temp) ti) t
    ) tt;