PostgreSQL中如何计算各学校学生数量的90分位数？

表结构说明 #

现有schools和students两张表，为一对多关系（每个学生仅属于一所学校，一所学校可拥有多名学生），表结构如下：

school表 #

id
name

student表 #

id
name
school_id

现有实现 #

目前已能通过以下SQL按学生数量分组并排序学校：

select school_id, count(id) as count from students
group by school_id
order by count desc

90分位数计算方案 #

不同数据库的分位数计算函数有所差异，以下是主流数据库的具体实现：

PostgreSQL #

支持连续型（PERCENTILE_CONT）和离散型（PERCENTILE_DISC）两种分位数计算：

-- 连续型90分位数（插值计算）
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY count) AS p90_cont
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) AS school_counts;

-- 离散型90分位数（取实际存在的数值）
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY count) AS p90_disc
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) AS school_counts;

MySQL 8.0+ #

可通过窗口函数实现精确分位数，或用近似函数处理大数据量：

-- 精确90分位数
SELECT count AS p90
FROM (
    SELECT count,
           PERCENT_RANK() OVER (ORDER BY count) AS pr
    FROM (
        SELECT COUNT(id) AS count
        FROM students
        GROUP BY school_id
    ) AS school_counts
) AS ranked_counts
WHERE pr >= 0.9
ORDER BY count
LIMIT 1;

-- 近似90分位数（MySQL 8.0.22及以上版本支持，性能更优）
SELECT APPROX_PERCENTILE(count, 0.9) AS p90_approx
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) AS school_counts;

SQL Server #

同样支持连续和离散两种分位数函数：

-- 连续型90分位数
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY count) OVER () AS p90_cont
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) AS school_counts
ORDER BY p90_cont
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

-- 离散型90分位数
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY count) OVER () AS p90_disc
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) AS school_counts
ORDER BY p90_disc
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

Oracle #

使用内置的分位数函数直接计算：

-- 连续型90分位数
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY count) AS p90_cont
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) school_counts;

-- 离散型90分位数
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY count) AS p90_disc
FROM (
    SELECT COUNT(id) AS count
    FROM students
    GROUP BY school_id
) school_counts;

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