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

WITH t1 AS
(
SELECT a.exam_id,
        uid,
        score,
        MIN(score)OVER(PARTITION BY a.exam_id) AS min_s,
        MAX(score)OVER(PARTITION BY a.exam_id) AS max_s
FROM exam_record a 
LEFT JOIN examination_info b
ON a.exam_id = b.exam_id
WHERE b.difficulty = 'hard'
AND score IS NOT NULL
)
SELECT uid,
        exam_id,
        ROUND(IFNULL(AVG(100*(score - min_s)/(max_s - min_s)),AVG(score))) AS avg_new_score
FROM t1
GROUP BY uid,exam_id
ORDER BY 2 ,3 DESC

需要注意的点只有IFNULL中的score需要加一层聚合避免GROUP BY报错