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

with zb as
(with cb1 as (select uid,er.exam_id,score,max(score) over(partition by exam_id) max_s,
min(score) over(partition by exam_id) min_s
from exam_record er
left join  examination_info ei on er.exam_id = ei.exam_id
where score is not null and difficulty = 'hard')
select uid,exam_id,if(max_s=min_s,score,(score-min_s)/(max_s-min_s)*100) a1
from cb1)
select uid,exam_id,round(sum(a1)/count(exam_id)) avg_new_score
from zb
group by exam_id,uid
order by exam_id,avg_new_score desc