如何用SQL高效查询各课程中学生最大分数差对应的user_id

一、实现目标查询 #

要找出每门课程中学生课前课后的最大分数差及对应用户ID，你需要先关联同一用户同一课程的pre和post测试数据，计算分数差后再筛选每门课的最大值。以下是高效的SQL实现：

WITH user_course_scores AS (
    SELECT
        user_id,
        course_id,
        -- 计算pre测试分数，确保浮点运算避免精度丢失
        MAX(CASE WHEN quiz_type = 'pre' THEN (#_questions_correct * 100.0) / #_questions END) AS pre_score,
        MAX(CASE WHEN quiz_type = 'post' THEN (#_questions_correct * 100.0) / #_questions END) AS post_score
    FROM results
    WHERE quiz_type IN ('pre', 'post')
    GROUP BY user_id, course_id
    -- 过滤未完成两次测试的用户
    HAVING pre_score IS NOT NULL AND post_score IS NOT NULL
),
course_top_diff AS (
    SELECT
        course_id,
        MAX(post_score - pre_score) AS max_score_diff
    FROM user_course_scores
    GROUP BY course_id
)
SELECT
    ucs.user_id,
    ucs.course_id,
    ROUND(ucs.pre_score, 2) AS pre_score,
    ROUND(ucs.post_score, 2) AS post_score,
    ROUND(cmd.max_score_diff, 2) AS max_score_diff
FROM user_course_scores ucs
JOIN course_top_diff cmd
    ON ucs.course_id = cmd.course_id
    AND (ucs.post_score - ucs.pre_score) = cmd.max_score_diff;

逻辑说明：

1. user_course_scores CTE：通过条件聚合，一次性计算每个用户每门课的pre和post测试分数，同时过滤掉只参加了单次测试的无效数据。
2. course_top_diff CTE：统计每门课的最大分数差值。
3. 最终关联查询：将两个CTE关联，找出每门课中分数差达到最大值的用户信息。

二、性能优化建议 #

针对500门课程的数据规模，可通过以下方式提升查询效率：

• 创建覆盖索引：建立联合索引 (course_id, quiz_type, user_id, #_questions_correct, #_questions)。该索引直接覆盖查询所需的所有字段，避免回表查询，大幅提升聚合阶段的性能。
• 优化分数计算：用 (#_questions_correct * 100.0) / #_questions 替代原写法，确保浮点运算，避免整数除法导致的分数精度丢失。
• 提前过滤数据：始终通过 WHERE quiz_type IN ('pre', 'post') 过滤无关测试类型，减少处理的数据量。
• 避免重复计算：在CTE中预先计算好分数值，后续查询直接复用，减少重复运算。

三、现有代码的问题 #

你当前的SQL仅查询了pre测试的分数，未关联post测试数据，也未计算分数差值和筛选最大值，无法满足“找出每门课最大分数差及对应用户”的需求。

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