题解 | #对试卷得分做min-max归一化#
对试卷得分做min-max归一化
http://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6
问题:请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间,并输出用户ID、试卷ID、归一化后分数平均值;最后按照试卷ID升序、归一化分数降序输出。(注:得分区间默认为[0,100],如果某个试卷作答记录中只有一个得分,那么无需使用公式,归一化并缩放后分数仍为原分数)
这道题的核心问题在与这句话:“如果某个试卷作答记录中只有一个得分,那么无需使用公式”,所以需要使用一个if语句来进行条件判定的功能
问题拆解:1.先根据exam_id分组找出每一组的score最大值m1和最小值m2
2.将步骤1生成的新表格和原始表格exam_record链接,提取exam_id和uid,然后根据这两个字段分组,按照题目给的公式简单计算一下
3.步骤2的外层需要嵌套if函数,例如果exam_id 9001中的uid 1001的用户只有一条数据score为100,不嵌套if函数则分母max(100)-min(100)为0,生成null值,实际上只有一条记录时就不用再进行公式计算了。
4.最后加上group by 和order by即可。
以上。
SELECT uid, er.exam_id, ROUND(AVG(IF(m1=m2, m1,
100*(score-m2)/(m1-m2)))) avg_new_score
FROM exam_record er
JOIN
(SELECT exam_id, max(score) m1, min(score) m2 FROM exam_record
WHERE exam_id IN
(SELECT exam_id FROM examination_info
WHERE difficulty ='hard')
GROUP BY exam_id) r1
ON er.exam_id=r1.exam_id
WHERE score IS NOT NULL
GROUP BY er.exam_id, uid
ORDER BY er.exam_id, avg_new_score DESC