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

SELECT uid
        ,exam_id
        ,ROUND(AVG(new_score)) AS avg_new_score
FROM (
    SELECT uid
            ,exam_id
            ,IF(max_score = min_score, score, (score-min_score)/(max_score-min_score)*100) as new_score
    FROM(
        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 examination_info
        JOIN exam_record USING(exam_id)
        WHERE score IS NOT NULL AND difficulty = "hard"
    ) data1
) data2
GROUP BY exam_id,uid
ORDER BY exam_id, avg_new_score DESC