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

（一）、非聚类窗口函数代码

select 
uid,exam_id
,round(sum(max_min)/count(max_min),0) as avg_new_score
from
(
    select
    exam_id
    ,uid
    ,if(score_max=score_min,score,((score-score_min)/(score_max-score_min))*100) as max_min 
        from
        (select 
        *
        from
            (select 
            exam_id
            ,uid 
            ,score
            from exam_record 
            where exam_id in 
                (select exam_id
                from examination_info
                where difficulty='hard')
            and score is not null 
            ) as t1

        left join

            (
            select 
            exam_id
            ,min(score) as score_min
            ,max(score) as score_max
            from exam_record 
            where exam_id in 
                (select exam_id
                from examination_info
                where difficulty='hard')
            and score is not null 
            group by exam_id
            ) as t2  using(exam_id)
        ) as tt1
    ) as ttt1

group by exam_id,uid
order by exam_id,avg_new_score desc;

（二）、解释

1、思路

高难度和有分数的试卷求出分数、（这套试卷的）最高分数、（这套试卷的）最低分数——将求出的分数、最高、最低分代入公式归化——将归化好的数值进行求平均

2、代码解释

11行-41行

score,score_min,score_max无法放到同一个查询里，因为最大值和最小值需要exam_id 的聚合运算，score不需要，所以需要两个查询来查询，然后再进行连接

9行

如果某个试卷作答记录中只有一个得分，那么无需使用公式

当min(score)=max(score)那就说明只有一个得分

3行

求平均值的一种方法，求和/数数