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

with t as (
select 
  uid,
  exam_id,
  if(max(score) over(partition by exam_id) = min(score) over(partition by exam_id),
     score,
     (score-min(score) over(partition by exam_id))/(max(score) over(partition by exam_id)-min(score) over(partition by exam_id))
     ) as new_score
from exam_record 
where score is not null
and exam_id in(
  select 
      distinct exam_id 
  from examination_info 
  where difficulty = 'hard' 
  ) 
)

select 
  uid,
  exam_id,
  round(avg(new_score)*100,0) as avg_new_score
from t
group by uid,exam_id
order by exam_id,avg_new_score desc