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

with t as(
select uid,exam_id,case when da!=xiao then (score-xiao)/(da-xiao)*100 else score end as dt from (
select uid,exam_id,score,max(score) over(partition by exam_id) as da,min(score) over(partition by exam_id) as xiao from exam_record left join examination_info using(exam_id) where score is not null and difficulty='hard' order by 2) tmp)

select uid,exam_id,round(avg(dt)) as avg_new_score from t group by  1,2 order by 2,3 desc