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

select t2.uid,t2.exam_id ,
round(avg(new_score),0) as avg_new_score from (
select t1.uid,t1.exam_id,
if(max_score=min_score,t1.score,(t1.score-min_score)*100/(max_score-min_score)) as new_score from (
select e1.uid,e1.exam_id,e1.score,
     min(e1.score) over(partition by e1.exam_id ) as min_score,
     max(e1.score) over(partition by e1.exam_id ) as max_score 
     from exam_record as e1 left join 
examination_info as e2 
on e1.exam_id=e2.exam_id 
where e1.submit_time is not  null and 
e1.exam_id in (
select e2.exam_id from examination_info as e2
    where e2.difficulty='hard' )
    ) t1 
    ) t2 group by t2.uid,t2.exam_id 
    order by t2.exam_id asc,avg_new_score DESC