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

SELECT
    uid,exam_id,
    ROUND(AVG(new_score)) avg_new_score
FROM(
    SELECT
        uid,
        exam_id,
        COALESCE(100*(score-(MIN(score) OVER w))/(MAX(score) OVER w-MIN(score) OVER w),score) new_score
    FROM
        exam_record
    INNER JOIN
        examination_info USING(exam_id)
    WHERE
        difficulty = 'hard' AND submit_time IS NOT NULL
    WINDOW w AS (PARTITION BY exam_id)
)t
GROUP BY
    uid,exam_id
ORDER BY
    exam_id, avg_new_score DESC