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

select uid,exam_id,
round(avg(case when count_score >1 then ((score-min_score)/(max_score-min_score))*100 else score end ))
as  avg_new_score from (
    select uid,exam_id,score,
        max(score) over w as max_score,
        min(score) over w as min_score,
        count(score) over w as count_score -- 需要增加一个判断数量的列
    from exam_record inner join examination_info using(exam_id)
    where difficulty = "hard" 
    window w as (partition by exam_id))t
where max_score is not null
group by uid,exam_id having avg_new_score is not null
order by exam_id,avg_new_score desc;

难点：
如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数

这要求必须要记录到每个试卷的分数数量。