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

with cb2 as
(with cb1 as
(select tag,sta_y,count(sub_y) wc
from
(select tag,date_format(submit_time,'%Y') sub_y,date_format(start_time,'%Y') sta_y
from exam_record er,examination_info ei
where er.exam_id = ei.exam_id and date_format(start_time,'%m') <= 6
and date_format(start_time,'%Y') in (2020,2021)) cb /*最初的表(前半年数据)*/
group by tag,sta_y) /*统计完成情况*/
select *,rank() over(partition by sta_y order by wc desc) e_rank
from cb1
where wc != 0) /*将各年份完成情况排名*/
select cb21.tag,cb20.wc exam_cnt_20,cb21.wc exam_cnt_21,
concat(round(((cb21.wc-cb20.wc)/cb20.wc)*100,1),'%') growth_rate,
cb20.e_rank exam_cnt_rank_20,cb21.e_rank exam_cnt_rank_21,
cast(cb21.e_rank as SIGNED) - cast(cb20.e_rank as SIGNED) rank_delta
from 
(select * 
from cb2
where sta_y = 2020) cb20/* 2020年的数据*/  join 
(select * 
from cb2
where sta_y = 2021) cb21/* 2020年的数据*/ on cb20.tag = cb21.tag
order by growth_rate desc,exam_cnt_rank_21 desc