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

#两种情况吧，只有一条数据的不需要标准化
# 并且需要先归一化，再计算均值
select
    tmp2.uid
    ,tmp2.exam_id
    ,round(avg(tmp2.s),0) as gs
from(# 计算归一化后的数值
    select
        tmp1.*
        ,if(tmp1.cnt=1, tmp1.score,(tmp1.score-tmp1.min_s)/(tmp1.max_s-tmp1.min_s)*100) as s
    from(#准备数据和字段
        select
            er.*
            ,count(er.score) over(partition by er.exam_id) as cnt
            ,max(er.score) over(partition by er.exam_id) as max_s #统计最大成绩
            ,min(er.score) over(partition by er.exam_id) as min_s # 统计最小成绩
        from examination_info ei join exam_record er
            on ei.exam_id=er.exam_id 
         where ei.difficulty='hard' and er.score is not null
    )tmp1
)tmp2
group by tmp2.uid
        ,tmp2.exam_id
order by tmp2.exam_id
        ,gs desc