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

-- 1、拿到用户作答高难度试卷的分数、最大值和最小值，作为临时表
/*
select uid,exam_id,score,
    max(score) over(partition by exam_id) as max_sc,
    min(score) over(partition by exam_id) as min_sc
from examination_info join exam_record using(exam_id)
where difficulty='hard'
*/
-- 2、进行标准化的计算
select uid,exam_id,
    round(avg(if(max_sc=min_sc,score,(score-min_sc)/(max_sc-min_sc)*100)),0) avg_new_score
from
    (select uid,exam_id,score,
        max(score) over(partition by exam_id) as max_sc,
        min(score) over(partition by exam_id) as min_sc
    from examination_info join exam_record using(exam_id)
    where difficulty='hard') a
where score is not null
group by exam_id, uid
order by exam_id asc, avg_new_score desc