PostgreSQL按指定比例为不同排名学生分配等级的SQL实现

原表数据 #

需求说明 #

按学生rank升序排序后，按以下比例分配grade：

• grade 1：占总学生数的10%（排名前10%的学生）
• grade 2：占总学生数的10%（前10%之后的下一个10%）
• grade 3：占总学生数的30%（前20%之后的下一个30%）
• grade 4：占总学生数的50%（剩余的学生）

PostgreSQL 实现代码 #

WITH student_stats AS (
    -- 计算总学生数及各等级的分界位置
    SELECT 
        COUNT(*) AS total_students,
        CEIL(COUNT(*) * 0.1) AS grade1_cutoff,
        CEIL(COUNT(*) * 0.2) AS grade2_cutoff,
        CEIL(COUNT(*) * 0.5) AS grade3_cutoff
    FROM students
),
ranked_students AS (
    -- 按rank升序生成连续排名，同rank学生归为同一梯队
    SELECT 
        id,
        rank,
        grade,
        DENSE_RANK() OVER (ORDER BY rank ASC) AS dense_rank_num
    FROM students
)
UPDATE students s
SET grade = 
    CASE
        WHEN rs.dense_rank_num <= ss.grade1_cutoff THEN 1
        WHEN rs.dense_rank_num <= ss.grade2_cutoff THEN 2
        WHEN rs.dense_rank_num <= ss.grade3_cutoff THEN 3
        ELSE 4
    END
FROM ranked_students rs
JOIN student_stats ss ON TRUE
WHERE s.id = rs.id;

代码说明 #

1. student_stats CTE：统计总学生数，同时计算各grade对应的排名分界点，用CEIL向上取整避免因总人数无法被10整除导致的分配遗漏。
2. ranked_students CTE：通过DENSE_RANK()函数按rank升序生成排名，确保相同rank的学生处于同一梯队，不会被拆分到不同grade中。
3. UPDATE语句：根据分界点匹配对应的grade值，完成批量更新。

若要查看更新后的结果，执行以下查询：

SELECT id, rank, grade FROM students ORDER BY rank, id;

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