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

select uid,exam_id,
round(avg(new_score)) avg_new_score
from(
	select *,if(count(score) over(partition by exam_id) =1,score,
			(score-min(score) over(partition by exam_id))/
			(max(score) over(partition by exam_id)-
			min(score) over(partition by exam_id)) * 100) as new_score
	from(
		select uid,exam_record.exam_id,score
		from exam_record inner join examination_info
			on exam_record.exam_id = examination_info.exam_id
		where difficulty = "hard" and score is not null	#用户作答高难度试卷进行过滤
		order by exam_record.exam_id,uid
		) as 数据处理
	) as 每份试卷作答记录内执行minmax归一化
group by uid,exam_id
order by exam_id,avg_new_score desc;