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

一、知识点总结与拓展

二、题目解读与解题步骤拆分

1、题目解读

题目：请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。

题目中隐藏的坑

需求字段

• uid
• exam_id
• avg_new_score 最终归一结果

2、步骤拆分

1）用户作答高难度试卷的得分

• 关联两张表，取difficulty='hard' 的数据

2）在每份试卷作答记录内执行min-max归一化

• 归一化公示:min(score),max(score)
• (score-min(score))/(max(score)-min(score))

3）如果某个试卷作答记录中只有一个得分，那么无需使用公式

• 当min(score)=max(score)那就说明只有一个得分

4）归一化后缩放到[0,100]区间

• 归一化结果*100

5）归一化后分数平均值取整数

6）按照试卷ID升序、归一化分数降序输出。

三、步骤代码

1）关联两张表，取difficulty='hard' 的数据,找出每类试卷的最小值和最大值

SELECT uid,a.exam_id,score,
    MIN(score) OVER(PARTITION BY exam_id) min_x, #求每类试卷的得分最小值
    MAX(score)OVER(PARTITION BY exam_id) max_x #求每类试卷的得分最大值
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id=b.exam_id
WHERE difficulty='hard' #难度为'hard'
AND score IS NOT NULL; #分数不为空

2）在每份试卷作答记录内执行min-max归一化

• (score-min(score))/(max(score)-min(score))
• 如果某个试卷作答记录中只有一个得分，那么无需使用公式，当min(score)=max(score)那就说明只有一个得分
• 区间为[0-100]，归一化结果*100

SELECT exam_id,uid,score,
	IF(min_x=max_x,score,(score-min_x)*100/(max_x-min_x))max_min #如果某试卷只有一个得分，不用公式
FROM (
		SELECT uid,a.exam_id,score,
		MIN(score) OVER(PARTITION BY exam_id) min_x, #求每类试卷的得分最小值
		MAX(score)OVER(PARTITION BY exam_id) max_x #求每类试卷的得分最大值
		FROM exam_record a
		LEFT JOIN examination_info b ON a.exam_id=b.exam_id
		WHERE difficulty='hard' #难度为'hard'
		AND score IS NOT NULL #分数不为空
	)t1

•  ROUND(SUM(max_min)/COUNT(max_min),0) avg_new_score

ORDER BY exam_id,avg_new_score DESC

SELECT uid,exam_id,
ROUND(SUM(max_min)/COUNT(max_min),0) avg_new_score
FROM (
	SELECT exam_id,uid,score,
	IF(min_x=max_x,score,(score-min_x)*100/(max_x-min_x))max_min/*如果某试卷只有一个得分*/
	FROM (
		SELECT uid,a.exam_id,score,
		MIN(score) OVER(PARTITION BY exam_id) min_x, #求每类试卷的得分最小值
		MAX(score)OVER(PARTITION BY exam_id) max_x #求每类试卷的得分最大值
		FROM exam_record a
		LEFT JOIN examination_info b ON a.exam_id=b.exam_id
		WHERE difficulty='hard' #难度为'hard'
		AND score IS NOT NULL #分数不为空
		)t1
		
	)t2

GROUP BY exam_id,uid
ORDER BY exam_id,avg_new_score DESC;#按照试卷ID升序，评价分降序排序