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

select 
    uid,
    exam_id,
    round(avg(norm_score) ,0)as avg_new_score
from

(
    select
    er.uid,
    ei.exam_id,
    ifnull((score - min(score) over(partition by ei.exam_id)) /
(max(score) over(partition by ei.exam_id)-min(score) over(partition by ei.exam_id))*100 ,score)
    as norm_score
from exam_record er
inner join examination_info ei
on er.exam_id=ei.exam_id

where ei.difficulty='hard' and er.submit_time is not null

) norm_score_t

group by uid,exam_id
order by exam_id, avg_new_score desc;