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

思路：

1.取高难度试卷,最高分最低分 两表关联difficulty ='hard'，max(score) over(partiton by exam_id)

2.归一化得分，乘100（只有一条记录则为得分）if(max=min,score,(score-min)/(max-min))

3.归一化分数平均值 round(avg,0)

4.试卷id升序，归一化分数降序 exam_id asc avg_new_score desc

解题步骤：

1.取高难度试卷,最高分最低分 两表关联difficulty ='hard'，max(score) over(partiton by exam_id)

select a.uid,a.exam_id,a.score,
min(score) over(partition by a.exam_id) mini,
max(score) over(partition by a.exam_id) maxi
from exam_record a
left join examination_info b
on a.exam_id=b.exam_id
where b.difficulty ='hard'
and a.score is not null

2.归一化得分，乘100（只有一条记录则为得分）if(max=min,score,(score-min)/(max-min))

select uid,exam_id,score,
if(maxi=mini,score,(score-mini)/(maxi-mini)*100) as avg_new_score 
from (
select a.uid,a.exam_id,a.score,
min(score) over(partition by a.exam_id) mini,
max(score) over(partition by a.exam_id) maxi
from exam_record a
left join examination_info b
on a.exam_id=b.exam_id
where b.difficulty ='hard'
and a.score is not null)t1

3.归一化分数平均值 round(avg,0)

4.试卷id升序，归一化分数降序 exam_id asc avg_new_score desc

select uid,exam_id,round(avg(avg_new_score),0)avg_new_score
from (
select uid,exam_id,score,
if(maxi=mini,score,(score-mini)/(maxi-mini)*100) as avg_new_score 
from (
select a.uid,a.exam_id,a.score,
min(score) over(partition by a.exam_id) mini,
max(score) over(partition by a.exam_id) maxi
from exam_record a
left join examination_info b
on a.exam_id=b.exam_id
where b.difficulty ='hard'
and a.score is not null)t1
)t2
group by uid,exam_id
order by exam_id asc,avg_new_score desc