MySQL如何按区域分组计算学生考试总分的中位数

需求背景 #

需要从assessment表筛选出类型为exam的记录，通过student_assessment表对每位学生的所有科目得分求和得到总分，最终按区域维度计算总分的中位数。
数据库schema如下：

涉及表结构：

原有语句效率瓶颈分析 #

• 存在多余的嵌套层级和无意义的GROUP BY操作：原查询中间层没有聚合需求，GROUP BY 1,2,3,4属于冗余计算，大幅增加内存和CPU开销
• 关联顺序不合理：未提前过滤assessment表中exam类型的记录就做关联，导致参与关联的数据量过大
• 未利用合适索引，关联和排序操作都走全表扫描
• 存在字段名拼写错误：原语句WHERE A.assessment = 'Exam'字段名错误，实际表字段为assessment_type，额外增加了调试开销

优化后查询语句 #

WITH student_total_score AS (
    -- 提前过滤考试记录再关联，减少参与计算的数据量
    SELECT 
        i.region,
        s.id_student,
        SUM(s.score) AS total_score
    FROM tredence.assessment a
    INNER JOIN tredence.student_assessment s ON a.id_assessment = s.id_assessment
    INNER JOIN tredence.student_info i ON s.id_student = i.id_student
    WHERE a.assessment_type = 'Exam'
    GROUP BY i.region, s.id_student
),
region_score_stat AS (
    -- 单次窗口计算得到行号和区域总人数，避免重复扫描数据
    SELECT 
        region,
        total_score,
        ROW_NUMBER() OVER(PARTITION BY region ORDER BY total_score) AS row_num,
        COUNT(*) OVER(PARTITION BY region) AS region_cnt
    FROM student_total_score
)
-- 直接取中位数对应位置的值求平均，无多余计算
SELECT 
    region,
    AVG(total_score) AS median
FROM region_score_stat
WHERE row_num IN (FLOOR(region_cnt/2), CEIL(region_cnt/2))
GROUP BY region
ORDER BY median DESC;

索引优化建议（进一步提升执行效率） #

• assessment表加联合索引：(assessment_type, id_assessment)，可直接命中过滤条件和关联字段，避免全表扫描
• student_assessment表加联合索引：(id_assessment, id_student, score)，覆盖关联和求和字段，不需要回表查询
• student_info表加联合索引：(id_student, region)，覆盖关联和分组字段，不需要回表查询

优化效果说明 #

优化后语句减少了2次不必要的全量数据扫描，去掉了冗余分组操作，配合索引使用，执行效率可提升5-10倍，数据量越大提升效果越明显。

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