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

select uid,exam_id
,ifnull(round(avg(100*(score-min_score)/(max_score-min_score)),0),0) as avg_new_score
from 
(
    select *
    ,max(score) over(partition by exam_id) as max_score
    ,min(score) over(partition by exam_id) as min_score
    from exam_record
    where exam_id in 
    (
    select exam_id
    from examination_info
    where difficulty="hard"
    )
) t
group by exam_id,uid
order by exam_id,avg_new_score desc