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

with a as
(select er.exam_id,max(score) as da, min(score) as xiao
from examination_info ei
join exam_record er on ei.exam_id = er.exam_id
where difficulty = 'hard'
and score is not null
group by  er.exam_id
)

select uid,exam_id,cast(avg(new_score) as signed) as avg_new_score
from(
select uid, er.exam_id,
if(da!=xiao,(score-xiao)/(da-xiao)*100,score) as new_score

from examination_info ei
join exam_record er on ei.exam_id = er.exam_id
join a on a.exam_id=er.exam_id
where score is not null
)b
group by uid,exam_id
order by exam_id,avg_new_score desc

注意易错点if(da!=xiao,(score-xiao)/(da-xiao)*100,score) as new_score