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

SELECT uid,exam_id,ROUND(AVG(avg_score)) avg_new_score
FROM (
SELECT uid,exam_id,
IF(max_sc=min_sc,score,(score-min_sc)/(max_sc-min_sc)*100) AS avg_score 
FROM (
SELECT uid,exam_id,score,
MAX(score) over(PARTITION BY exam_id) max_sc,
MIN(score) over(PARTITION BY exam_id) min_sc
FROM exam_record JOIN examination_info USING(exam_id)
WHERE difficulty='hard' AND score IS NOT NULL
)t
)t1
GROUP BY uid,exam_id
ORDER BY exam_id,avg_new_score DESC;

要考虑分数只有一个的情况，所以需要添加一个判断条件。