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

select uid
        ,t1.exam_id
        ,round(avg(if(max_score = min_score,score,(score - min_score)/(max_score-min_score)*100)),0) AS avg_new_score
from exam_record er
inner join(
    select er.exam_id
        ,min(score) AS min_score
        ,max(score) AS max_score
    from exam_record er
    inner join examination_info ei on er.exam_id = ei.exam_id
    where difficulty = 'hard'and score is not null
    group by exam_id
) t1 /*高难度试卷的最高分和最低分*/
on er.exam_id = t1.exam_id
where score is not null
group by uid
        ,t1.exam_id
order by t1.exam_id asc
        ,avg_new_score desc

1、在归一化时记得判断试卷是否只有一个成绩，如果是的话，不用进行归一化，还是原来的成绩

2、group by uid，t1.exam_id是因为如果一个uid在一个exam_id上有不止一个成绩的话，avg_new_score是取成绩归一化之后的平均分