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

这道题和之前的某道题很相似，也是用多重嵌套子查询。

我觉得关键是在于对于归一化的公式的理解和表示。

select
    uid,
    exam_id,
    round(sum(max_min) / count(max_min), 0) as avg_new_score
from
    (
        select
            exam_id,
            uid,
            score,
            (
                case
                    when min_x = max_x then score
                    else ((score - min_x) * 100 / (max_x - min_x))
                end
            ) as max_min
        from
            (
                select
                    uid,
                    e_r.exam_id,
                    score,
                    min(score) over (
                        partition by
                            exam_id
                    ) as min_x,
                    max(score) over (
                        partition by
                            exam_id
                    ) as max_x
                from
                    exam_record as e_r
                    left join examination_info e_i using (exam_id)
                where
                    difficulty = 'hard'
                    and score is not null
            ) as t1
    ) as t2
group by
    uid,
    exam_id
order by
    exam_id,
    avg_new_score desc