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

# 高难度试卷
with
t1 as(
    select
        uid,
        exam_id,
        score,
        min(score)over(partition by exam_id) as min_score,
        max(score)over(partition by exam_id) as max_score
    from
        exam_record left join examination_info using(exam_id)
    where
        difficulty='hard' and score is not null
),
t2 as(
    select
        uid,
        exam_id,
        score,
        if(min_score=max_score,score,(score-min_score)*100/(max_score-min_score)) as max_min
    from
        t1
)

select
    uid,
    exam_id,
    round(sum(max_min)/count(max_min),0) as avg_new_score
from
    t2
group by
    exam_id,
    uid
order by
    exam_id,
    avg_new_score desc