题解 | #对试卷得分做min-max归一化#

-- 按uid, exam_id分组，计算平均值
SELECT uid, exam_id,
    ROUND(AVG(min_max_score), 0) AS avg_new_score
FROM (
    -- 在每类试卷作答记录内执行min-max归一化
    SELECT uid, exam_id, score,
        IF(min_score = max_score, score, 
           (score - min_score) * 100 / (max_score - min_score)) AS min_max_score
    FROM (
        -- 关联两张表，取difficulty='hard'的数据，找出每类试卷的最小值和最大值
        SELECT uid, exam_id, score,
            MAX(score)OVER(PARTITION BY exam_id) AS max_score, 
            MIN(score)OVER(PARTITION BY exam_id) AS min_score 
        FROM exam_record 
            LEFT JOIN 
            examination_info
            USING(exam_id)
        WHERE difficulty = 'hard'
            AND score IS NOT NULL
    ) sel_table
) min_max_table
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC