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

select uid,exam_id,round(sum(normal_score)/count(normal_score),0) 'avg'
from(select uid,exam_id,if(min_score=max_score,score,(score-min_score)*100/(max_score-min_score)) 'normal_score'
from (select uid,exam_id,submit_time,score,min(score)over(partition by exam_id) 'min_score',max(score)over(partition by exam_id) 'max_score'
from exam_record
where exam_id in 
(
    select exam_id
    from examination_info
    where difficulty = 'hard'
) and submit_time is not null
) t1)t2
group by uid,exam_id
order by exam_id asc,avg desc