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

大意了，我没有闪，错了两次

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