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

with temp as (
    select t1.uid,
       t1.exam_id,
       t1.score
    from exam_record t1
    left join examination_info t2
        on t1.exam_id = t2.exam_id
    where t2.difficulty = 'hard'
        and t1.score is not null
), temp2 as (
    select exam_id,
         min(score) as min,
         max(score) as max,
         count(score) as count_score
    from temp
    group by exam_id
), temp3 as (
    select t1.uid,
         t1.exam_id,
         if(count_score = 1, t1.score, (t1.score - min)/(max-min)*100) as avg_new_score
    from temp t1
    left join temp2 t2
        on t1.exam_id = t2.exam_id
) select uid,
         exam_id,
         round(sum(avg_new_score)/count(*)) as avg_new_score
from temp3
group by uid, exam_id
order by exam_id, avg_new_score desc