题目复盘——聚合窗口函数1
SQL33 对试卷得分做min-max归一化
问题分析
(1)高难度试卷——exam_record left join examination_info on…where difficulty=‘hard’
(2)得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]——
聚合窗口函数找每个exam_id试卷内的最大最小值:
select uid,exam_id, score,min(score)over(partition by exam_id) min_score,max(score)over(partition by exam_id) max_score from exam_record
归一化缩放到[0,100]:((score-min_score)/(max_score-min_score))*100;
(3)输出用户ID试卷ID,归一化后分数平均值——
按照uid,exam_id分组:group by uid,exam_id;
平均值:avg(((score-min_score)/(max_score-min_score))*100);
保留整数:round(avg(((score-min_score)/(max_score-min_score))*100),0);
(4)如果某试卷作答记录只有一个得分则无需使用公式——
只有一个得分意味着:max(score)=min(score)分母为0不能用公式;
if语句判断:if(exam_min_score=exam_max_score,score,((score-min_score)/(max_score-min_score))*100)
(5)排序——order by uid asc,avg_new_score desc;这里题目描述的不太严谨,题目说按照归一化分数降序让我一开始以为是平均之前的分数,但正确答案和表格示例都显示应该是平均分的降序;
答案重写
SELECT uid,exam_id,
round(avg(
if(exam_min_score=exam_max_score,score,((score-min_score)/(max_score-min_score))*100)
),