题解 | #对试卷得分做min-max归一化#
对试卷得分做min-max归一化
https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6
现在使用拆解嵌套组合的方式几乎没有做不出来的题了，问题是要花多少🤧
select uid,exam_id,round(avg(new_score),0) avg_new_score from
(select uid,t3.exam_id exam_id,if(jizhi != 0,(score-min)/jizhi*100,score) new_score from
(select uid,exam_id,score from exam_record
where score is not null) t3
join
(select exam_id,min(score) as min,max(score)-min(score) as jizhi from
(select uid,exam_record.exam_id exam_id,score,dense_rank()over(partition by exam_record.exam_id order by score desc) ranking from exam_record left join examination_info on exam_record.exam_id = examination_info.exam_id
where score is not null and difficulty = 'hard') t1
group by exam_id) t2
on t3.exam_id = t2.exam_id) t4
group by uid,exam_id
order by exam_id,avg_new_score desc
投递腾讯等公司8个岗位 >