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

with t1 as(
select uid,eo.exam_id,score,min(score)over(partition by exam_id) minx,
max(score)over(partition by exam_id) maxx
from examination_info eo
left join exam_record ed
on eo.exam_id=ed.exam_id
where difficulty='hard' and score is not null
)  
select uid,exam_id,round(avg(max_min),0) as avg_new_score
from(
select exam_id,uid,score,if(minx=maxx,score,(score-minx)/(maxx-minx)*100) max_min
from t1
) t2
group by uid,exam_id 
order by exam_id,avg_new_score desc

1. 先筛选出hard试卷和score非空的数据，并提取出uid，试卷id，score，并利用聚合窗口函数 计算试卷的最小最大值
2. 归一化
1. 如果最大和最小值相等，说明就只有一种分数 无须归一化
2. 归一化 别忘了*100
3. 最后使用avg函数 按照uid和试卷id分组