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

select 
    uid,
    exam_id,
    round(sum(max_min)/count(uid),0) as avg_new_score
from(
    select
        uid,
        exam_id,
        IF(min_x=max_x,score,(score-min_x)*100/(max_x-min_x))max_min
    from(
        #1.用户作答高难度试卷的得分情况
        select 
            uid,
            a.exam_id,
            score,
            max(score) over (partition by exam_id) as max_x,
            min(score) over (partition by exam_id) as min_x
        from
            exam_record as a
        left join 
            examination_info as b
        on a.exam_id = b.exam_id
        where 
            score is not null
            and difficulty = 'hard')as c)as d
group by uid,exam_id
order by exam_id,avg_new_score desc

这道题卡半天的原因，忘了用max(score) over (partition by exam_id) as max_x