题解 | #试卷完成数同比2020年的增长率及排名变化#

select tag
,sum(case when start_year =2020 then exam_cnt else 0 end) as exam_cnt_20
,sum(case when start_year =2021 then exam_cnt else 0 end) as exam_cnt_21
,concat(round((sum(case when start_year =2021 then exam_cnt else 0 end)-sum(case when start_year =2020 then exam_cnt else 0 end))
/sum(case when start_year =2020 then exam_cnt else 0 end)*100,1),'%') as growth_rate
,sum(case when start_year =2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20
,sum(case when start_year =2021 then exam_cnt_rank else 0 end) as exam_cnt_rank_21
,sum(case when start_year =2021 then exam_cnt_rank else 0 end) - sum(case when start_year =2020 then exam_cnt_rank else 0 end) as rank_delta 
from (-- 关键是这里求出完成次数级排名 要使用rank美式排名
    select tag,year(start_time) as start_year,
    count(submit_time) as exam_cnt,
    rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank
    from exam_record t join examination_info t1 using(exam_id)
    where month(start_time)<=6
    group by tag,year(start_time)
) t
group by tag
having exam_cnt_20 >0 and exam_cnt_21>0
order by growth_rate desc,exam_cnt_rank_21 desc

先求出完成次数级排名 要使用rank美式排名

然后在这个基础之上，进行大量的case when的操作，新建列。