5. SQL142 对试卷得分做min-max归一化

最新推荐文章于 2024-03-25 23:45:00 发布

小白的做题记录

最新推荐文章于 2024-03-25 23:45:00 发布

阅读量255

点赞数

文章标签： sql

本文链接：https://blog.csdn.net/m0_72711141/article/details/126992718

版权

什么是窗口函数呢？

窗口函数就是在不改变原表结构的前提下，新建一列。

解题思路分析：

如何判断只有一个分数？

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

select uid, exam_id,
round(sum(max_min)/count(max_min),0) avg_new_score
from
(select uid, exam_id, score,
if (min_t=max_t,score,(score-min_t)*100/(max_t-min_t)) max_min
from
(select uid, er.exam_id, score,
max(score) over(partition by er.exam_id) max_t,
min(score) over(partition by er.exam_id) min_t
from exam_record er
left join examination_info using(exam_id)
where difficulty='hard' 
and score is not null)t1)t2
group by uid, exam_id
order by exam_id, avg_new_score desc;