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

select 
uid,exam_id,
-- case when round(sum(guiyi)/count(case when guiyi<>0 then guiyi else null end),0) is not null 
-- then round(sum(guiyi)/count(case when guiyi<>'0' then guiyi else null end),0)
-- else 0 end as avg_new_score
round(avg(guiyi),0) avg_new_score
from(
	select 
	uid,exam_id,
	case when cnt<>1 then (score-min_score)/(max_score-min_score)*100 
	else score end as guiyi
	-- case when format((score-min_score)/(max_score-min_score)*score,0)<>0 then format((score-min_score)/(max_score-min_score)*score,0) else score end avg_new_score
	from (
		select 
		a.uid,a.exam_id,a.score,
		min(score) over (partition by exam_id) min_score,
		max(score) over (partition by exam_id) max_score,
		count(score) over (partition by exam_id) cnt
		from exam_record a left join examination_info b
		on a.exam_id =b.exam_id 
		where b.difficulty='hard'
		and score is not null
		-- order by exam_id,uid
	) d
	-- order by exam_id,uid
	-- where (score-min_score)/(max_score-min_score)*score<>0
)e
group by uid,exam_id
order by exam_id asc,avg_new_score desc