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

#先求出最大值和最小值创建新表，再对新表中的数据进行计算和筛选。
select
uid
,exam_id
,round(avg(avg_new_score),0)
from
(
    select
    uid
    ,exam_id
    ,case when maxx = minx then score else
    (score-minx)/(maxx-minx)*100 end avg_new_score
    from
    (
        select
        uid
        ,er.exam_id
        ,score
        ,min(score)over(partition by er.exam_id) minx
        ,max(score)over(partition by er.exam_id) maxx
        from exam_record er left join examination_info ei
        on er.exam_id = ei.exam_id 
        where score is not null
        and difficulty = 'hard'  #筛选出高难度题目
    ) table1
) table2
group by exam_id,uid
order by 2,3 desc;