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

select uid,exam_id,round(avg(combinednum),0) as combinednum from (
SELECT uid,exam_id,if(MIN(score)OVER(PARTITION BY exam_id) = MAX(score)OVER(PARTITION BY exam_id),score, (score-MIN(score)OVER(PARTITION BY exam_id))*100/(MAX(score)OVER(PARTITION BY exam_id)-MIN(score)OVER(PARTITION BY exam_id))) as combinednum,
        score,
        MIN(score)OVER(PARTITION BY exam_id), #求每类试卷的得分最小值
        MAX(score)OVER(PARTITION BY exam_id) max_x #求每类试卷的得分最大值
        FROM exam_record 
        JOIN examination_info  using(exam_id)
        WHERE difficulty='hard' #难度为'hard'
        AND score IS NOT NULL #分数不为空
) newform
group by
uid,exam_id
order by exam_id,combinednum desc

用窗口函数over分割examid找出最大最小值，记住如果最大最小皆为自己，归一化分母为0会报错，代码如上