题解 | #对试卷得分做min-max归一化——窗口函数使用

/*查询各用户下各高难度试卷的max-min归一化得分值（窗口函数）*/
# 其中窗口函数用来查询各高难度试卷下的最高分和最低分
with t1 as
(select uid, exam_id, case
when max_score-min_score=0 then score
when max_score-min_score>0 then (score-min_score)/(max_score-min_score)*100 
else null
end as ge_score
from (
        select uid, er.exam_id, submit_time, score, max(score)over(partition by er.exam_id) max_score, min(score)over(partition by er.exam_id) min_score        # 窗口函数
    from exam_record er
    join examination_info ei
    on er.exam_id=ei.exam_id
    where difficulty = 'hard' and submit_time is not null
) k1)

select uid, exam_id, round(avg(ge_score)) avg_new_score
from t1
group by uid, exam_id
order by exam_id, avg_new_score desc;

【切记】with as建立临时表时，一定要加小括号把查询包裹起来，另外这部分不要加分号结尾