题解 | #对试卷得分做min-max归一化#
对试卷得分做min-max归一化
https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6
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