SQL33 对试卷得分做min-max归一化

题目主要信息：

• 将用户作答 高难度 试卷的得分在每份试卷作答记录内执行 min-max 归一化 后缩放到 [0,100] 区间，并输出用户 ID、试卷 ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数 降序输出。
• 得分区间默认为 [0,100]，如果某个试卷作答记录中只有一个得分，归一化并缩放后分数仍为原分数。
• min-max 归一化：

问题拆解：

总体思路

1. 获取每个 exam_id 的最高分和最低分
2. 根据最高分和最低分对所有分数进行归一化
3. 排序，输出

具体实现

• 根据 exam_id 对数据进行聚合统计，通过 MAX、MIN 函数获取每个 exam_id 的最高、最低分。知识点：GROUP BY, MIN, MAX
• 将每个试卷的最高、最低分通过 LEFT JOIN 追加到 exam_record 行尾，并根据 uid, exam_id 进行聚合。通过 “最高分 和 最低分 是否相等” 判断每个聚合结果中的 exam_id 是否满足 作答记录中只有一个得分，如果是，直接返回该分数，否则，按照公式进行 min-max 归一化，返回归一化后的平均分数。知识点：AVG, CASE WHEN, LEFT JOIN
• 输出时按照 exam_id 升序、score 降序输出。排序的语法为 ORDER BY，升序 ASC，降序 DESC。输出平均分时需要使用 ROUND 方法保留整数。 知识点：ORDER BY, ASC, DESC, ROUND

代码：

SELECT
    *
FROM
(
    SELECT
        exam_record.uid,
        exam_record.exam_id,
        CASE
            WHEN _max = _min THEN ROUND(AVG(exam_record.score))
            ELSE ROUND(AVG((exam_record.score - _min) * 100 / (_max - _min)))
        END AS score
    FROM
        exam_record
    LEFT JOIN
    (
        SELECT
            exam_id,
            MIN(score) AS _min,
            MAX(score) AS _max
        FROM
            exam_record
        WHERE
            start_time IS NOT NULL
            AND submit_time IS NOT NULL
        GROUP BY
            exam_id
    ) AS norm_info
    ON exam_record.exam_id = norm_info.exam_id
    LEFT JOIN
        examination_info
    ON exam_record.exam_id = examination_info.exam_id
    WHERE
        exam_record.score IS NOT NULL
        AND examination_info.difficulty = "hard"
    GROUP BY
        exam_record.uid,
        exam_record.exam_id
) AS result_without_order
ORDER BY
    exam_id ASC,
    score DESC