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

select 
    uid,
    exam_id,
    round(sum(avg_score)/count(exam_id),0) avg_new_score
from(
    select
        uid,
        exam_id,
        if(max_score=min_score,score,(score-min_score)/(max_score-min_score)*100) avg_score
    from (
        select
            uid,
            er.exam_id,
            score,
            max(score) over(partition by exam_id) max_score,
            min(score) over(partition by exam_id) min_score
        from exam_record er 
        inner join examination_info ei on er.exam_id= ei.exam_id
        where difficulty = 'hard'
        and score is not null
    )tmp
)a
group by uid,exam_id
order by exam_id ,avg_new_score desc