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

select 
min_max.uid,
min_max.exam_id,
round(avg(min_max.score),0) as avg_new_score
from
(
select 
er.uid,er.exam_id,
if(count(1) over(partition by er.exam_id)<>1, #若一个试卷只有一个分数，则直接输出该分数
((er.score-min(er.score) over(partition by er.exam_id))/
(max(er.score) over(partition by er.exam_id)-
min(er.score) over(partition by er.exam_id))) *100,er.score) as score
from examination_info ei 
left join exam_record er
on ei.exam_id=er.exam_id
where ei.difficulty='hard' and er.score is not null
) as min_max
group by min_max.exam_id,min_max.uid
order by min_max.exam_id,avg_new_score desc