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

select
    uid,
    exam_id,
    round(avg(case when counts = 1 then score 
        when new_score > 100 then 100
        else new_score end
        )) as avg_new_score
from(
    select
        *,
        (score - mins)/(maxs - mins)*100 as new_score
    from (
        select
            uid,
            exam_id,
            score,
            max(score) over(partition by exam_id) as maxs,
            min(score) over(partition by exam_id) as mins,
            count(score) over(partition by exam_id) as counts
        from exam_record
        left join examination_info using (exam_id)
        where difficulty = 'hard' and score is not null
    ) as t
) as tt
group by uid,exam_id
order by exam_id,avg_new_score desc

主要就是窗口聚合的应用