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

# 请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。

select 
    uid,
    exam_id,
    if(avg(max_s)=avg(min_s),round(avg(score),0),
    round(avg(100*(score-min_s)/(max_s-min_s)), 0)
    ) as avg_new_score
from
(
select 
    uid,
    exam_id,
    score,
    max(score) over(partition by exam_id) as max_s,
    min(score) over(partition by exam_id) as min_s
from exam_record
where exam_id in(
    select exam_id from examination_info where difficulty = 'hard'
) and score is not null
)t1
group by uid, exam_id
order by exam_id, avg_new_score desc