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

# 筛选高难度
# 根据科目得到每个分数的归一化结果
# 根据uid对分数求平均
# 按照试卷ID升序、归一化分数降序输出


select uid,exam_id,
round(avg(sta_score)) as avg_new_score
from
(select uid,exam_id,
(case when exam_count=1 then score else sta_score end) sta_score
from
(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,
(score-min(score)over(partition by exam_id))/(max(score)over(partition by exam_id)-min(score)over(partition by exam_id))*100 as sta_score,
count(score)over(partition by exam_id) as exam_count
from exam_record join examination_info using(exam_id)
where difficulty='hard' and score is not null)a)sta_table
group by uid,exam_id
order by exam_id,avg_new_score desc