一、知识点总结与拓展

 本题所涉及到的知识点不多，困难点在于题目读起来比较拗口，以及一些关键信息藏在“解释”里。牛客的题，好多线索都喜欢放在“解释”里。
主要考察聚类窗口函数，和聚类窗口函数的用法和GROUP BY 函数类似。
  • MIN()OVER() :不改变表结构的前提下，计算出最小值
  • MAX()OVER():不改变表结构的前提下，计算出最大值
  • COUNT()OVER():不改变表结构的前提下，计数
  • SUM()OVER():不改变表结构的前提下，求和
  • AVG()OVER():不改变表结构的前提下，求平均值
在我的理解窗口函数的关键词是“不改变表格结构”，查出的数据，单独放一列。

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

1、题目解读

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

题目中隐藏的坑

坑1：某个试卷作答记录中只有一个得分，那么无需使用公式
坑2：归一化后分数平均值取整数（取自解释中的这句话“后两个得分都是用户1001作答的，因此用户1001对试卷9001的新得分为(95+100)/2≈98（只保留整数部分）”），因而，需要对同一个用户的归一化分数取平均值。
所以，做题一定要看“解释”的内容

需求字段

  • 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
3）归一化后分数平均值取整数

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

6）按照试卷ID升序、归一化分数降序输出。 
ORDER BY exam_id,avg_new_score DESC
7）完整代码 

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升序，评价分降序排序