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

with cte_get_deviation as (
    select uid,
    exam_id,
    score,
    if(max_score=min_score,score,(score-min_score)*100/(max_score-min_score)) as max_min
	# 计算归一化的分数时需要注意最大分数差为0或者只有一个分数的情况，使用原分数
    from (
        select uid,
        exam_id,
        score,
        min(score) over(partition by exam_id) min_score,
        max(score) over(partition by exam_id) max_score
		# 根据试卷分类获取最大分数和最小分数
        from examination_info
        left join exam_record using(exam_id)
        where difficulty = 'hard' and submit_time is not null
    ) t1
) 

# 获取每份试卷最大和最小分数
select uid,exam_id,
round(sum(max_min)/count(score),0) as avg_new_score
from cte_get_deviation
group by uid,exam_id
order by exam_id,avg_new_score desc