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

select
    uid,
    exam_id,
    round(avg(归一化分数), 0) avg_new_score
from
    (
        select
            uid,
            er.exam_id exam_id,
            if (
                最小值 != 最大值,
                (score - 最小值) / (最大值 - 最小值) * 100,
                score
            ) 归一化分数
        from
            test.examination_info ei
            join test.exam_record er on ei.exam_id = er.exam_id
            join (
                select
                    ei.exam_id,
                    min(score) 最小值,
                    max(score) 最大值
                from
                    test.examination_info ei
                    join test.exam_record er on ei.exam_id = er.exam_id
                where
                    score is not null
                    and difficulty = 'hard'
                group by
                    er.exam_id
            ) a on a.exam_id = ei.exam_id
        where
            score is not null
            and difficulty = 'hard'
    ) b
group by
    b.uid,
    b.exam_id
order by
    exam_id asc,
    avg_new_score desc