什么是窗口函数呢?
窗口函数就是在不改变原表结构的前提下,新建一列。
解题思路分析:
如何判断只有一个分数?
- 当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;