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

创建子表t,记录每个归一化的成绩

最大值：max(score)over(partition by exam_id)
最小值：min(score)over(partition by exam_id)
归一化之后的新值：if(最大=最小，原值，归一化的值)

条件：成绩不为空 and 难度困难，where...and

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' 
  ) 
)

从t表中选择要的值

平均值：avg()
保留小数：round()
分组：group by uid,exam_id
排序:order by exam_id,avg_new_score desc

无序列表内容

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

合并

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