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

select uid,exam_id,round(avg(gap2)) avg_new_score
from (
select uid,a.exam_id
,ifnull(100*(score-min(score)over(partition by a.exam_id))/(max(score)over(partition by a.exam_id)-min(score)over(partition by a.exam_id)),score) gap2
from exam_record a left join examination_info b
on a. exam_id = b.exam_id
where difficulty = 'hard'
and score is not null
) c
group by uid,exam_id
order by exam_id,avg_new_score desc;