如何在SQL中计算聚合格式数据的90th percentile

核心逻辑说明 #

你存储的是按分数聚合的计数数据，不需要展开为逐学生的明细行即可计算分位值。离散90分位的计算规则为：找到最小的实际存在的分数，使得小于等于该分数的累计学生人数 ≥ 总学生数 * 0.9，和PERCENTILE_DISC的离散分位计算逻辑完全一致。
针对你给出的样例数据验证：

• 总学生数171，90分位对应的累计人数阈值为171 * 0.9 = 153.9
• 从低到高累加人数：累计到98分时总人数为127，未达阈值；累计到99分时总人数为159，超过阈值，因此最终结果为99，和你的预期一致。

通用高效SQL实现（支持所有带窗口函数的数据库） #

该写法不需要拆分聚合数据，性能最优，无数据库版本依赖：

WITH score_agg AS (
    -- 替换为你的实际聚合表名即可
    SELECT Score, count_of_student FROM your_agg_table
),
total_calc AS (
    SELECT SUM(count_of_student) AS total_student FROM score_agg
),
cumulative_calc AS (
    SELECT
        Score,
        SUM(count_of_student) OVER (ORDER BY Score ASC) AS cum_student,
        total_student
    FROM score_agg, total_calc
)
SELECT MIN(Score) AS p90_score
FROM cumulative_calc
WHERE cum_student >= total_student * 0.9;

运行上述代码在样例数据上会直接返回结果99。

原生分位函数适配说明 #

PERCENTILE_DISC和PERCENTILE_CONT原生面向明细行设计，无法直接传入聚合计数数据计算：

• PERCENTILE_CONT为连续分位函数，会对相邻分位点做线性插值，返回值可能是实际不存在的分数，不符合你的需求
• 直接对聚合表的Score字段调用PERCENTILE_DISC，会将每个分数视为1个独立样本，完全忽略学生人数字段，计算结果完全错误

如果一定要使用原生PERCENTILE_DISC，需要先将聚合数据展开为每个学生一行的明细数据，该写法仅作原理演示，数据量大时性能极差，不推荐生产使用：

-- 性能差，仅作原理参考（以PostgreSQL语法为例，其他数据库展开逻辑类似）
WITH detail_data AS (
    SELECT Score
    FROM your_agg_table,
    GENERATE_SERIES(1, count_of_student) AS stu_id
)
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY Score) AS p90_score
FROM detail_data;

内容的提问来源于stack exchange，提问作者nav_jan