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

SELECT 
  uid,
  exam_id,
  round(sum(max_min)/count(max_min),0) avg_new_score
FROM (
	select exam_id,uid,score,if(min_score=max_score,score,(score - min_score) / (max_score - min_score)*100) max_min
	from (
		SELECT 
    uid,
    exam_id,
	score,
    MIN(score) OVER (PARTITION BY exam_id) AS min_score,
    MAX(score) OVER (PARTITION BY exam_id) AS max_score
  FROM exam_record
  WHERE exam_id IN (
    SELECT exam_id
    FROM examination_info
    WHERE difficulty = 'hard'
  ) and score is not null
	)t1
) t
GROUP BY uid, exam_id
ORDER BY exam_id, avg_new_score DESC;

救命虽然是中等题但是也挺痛苦的

• MIN(score) OVER (PARTITION BY exam_id) AS min_score

小坑，直接用min/max就错了

• 以及题目理解出错，avg_new 是先归一化，再去除0，认真读题

因此用户1001对试卷9001的新得分为(95+100)/2≈98（只保留整数部分）