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

步骤：
①通过聚合窗口函数max,min算出各类试卷的最高、最低分，形成表max_min；
②通过对max_min子查询得到每次作答的归一化分数（用case when区分只答题1次和答题多次情况），形成表bb；
③通过表bb与表e_i的链接筛出hard难度的部分，然后再用聚合函数avg算出uid、exam_id聚类后的均分。

#步骤③
select uid, exam_id, round(avg(a),0) avg_new_score
from
#步骤②
(select uid, exam_id, 
case when max_score=min_score then score
else 100*(score-min_score)/(max_score-min_score) end as a
from
#步骤①
(select *, 
max(score)over(partition by exam_id) max_score,
min(score)over(partition by exam_id) min_score
from exam_record
where score is not null) as max_min
) as bb
left join examination_info using(exam_id)
where difficulty='hard'
group by uid, exam_id
order by exam_id, avg_new_score desc