题解 | #对试卷得分做min-max归一化#
对试卷得分做min-max归一化
https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6
大意了，我没有闪，错了两次
select
d.uid,d.exam_id,format(sum(calcScore)/count(1),0) as avg_new_score
from
(
select
c.uid,c.exam_id,if(c.maxScore=c.minScore,c.score,((c.score - c.minScore)/(c.maxScore - c.minScore)) * 100) as calcScore
from
(
select
a.uid,a.exam_id,a.score,
min(a.score) over(partition by a.exam_id rows between unbounded preceding and unbounded following) as minScore,
max(a.score) over(partition by a.exam_id rows between unbounded preceding and unbounded following) as maxScore
from exam_record a join examination_info b on a.exam_id = b.exam_id and b.difficulty="hard" and a.score is not null
) c
) d group by d.uid,d.exam_id
order by d.exam_id ,avg_new_score*1 desc
