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

WITH a1 AS(
    SELECT exam_record.*,MAX(score)OVER(PARTITION BY exam_id) AS 'max',
           MIN(score)OVER(PARTITION BY exam_id) AS 'min'
    FROM examination_info JOIN exam_record USING(exam_id)
    WHERE difficulty = 'hard'
    AND score IS NOT NULL
    )
SELECT uid,exam_id,ROUND(AVG(avg_new_score)) AS avg_new_score
FROM (SELECT uid,exam_id,CASE WHEN (max = min) THEN score
                              ELSE (score - min)/(max - min)*100 END AS avg_new_score  #不要再SELECT里面*100，因为有可能只有一个分数，一个分数就直接输出，*100的话就是几千分了
      FROM a1) aa
GROUP BY uid,exam_id
ORDER BY exam_id,avg_new_score DESC;