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

select
t.uid,
t.exam_id,
round(avg(if(ma=mi,t.score,(t.score-mi)/(ma-mi)*100)))avg_new_score
from (
select
er.uid,
er.exam_id,
er.score,
min(score) over(partition by exam_id) as mi,
max(score) over(partition by exam_id) as ma
from examination_info ei
inner join exam_record er
on ei.exam_id=er.exam_id
where ei.difficulty='hard'
and er.score is not null) t
group by t.uid, t.exam_id
order by exam_id,avg_new_score desc ;