题解 | #试卷完成数同比2020年的增长率及排名变化#
试卷完成数同比2020年的增长率及排名变化
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
思路:分别统计20,21年上半年的各类试卷的做完次数,和排名。合并后,计算增长率和排名变化
- 统计20,21年上半年信息,建立子表
- 联结:using
- 各类试卷分组:group by tag,year
- 完成次数:count()
- 排名:dense_rank() over( order by 完成次数 desc) , 排名不需要tag分组
- 条件:前六个月:where month()<7
- 根据上面子表,计算增长率,和排名变化
- 分别取20/21年的值:
- exam_cnt as exam_cnt_20
- lead(exam_cnt) over(partition by tag order by start_year) as exam_cnt_21
- ranking as exam_cnt_20
- lead(ranking) over(partition by tag order by start_year) as exam_cnt_21
- 增长率:(2021完成数-2020完成数)/2020完成数
- 百分比:concat()
- 小数:round()
- 排名变化 rank2020-rank_2021
- 分别取20/21年的值:
- 排序
with t1 as( select tag, date_format(start_time,"%Y") as start_year, count(score) as exam_cnt, rank() over(partition by date_format(start_time,"%Y") order by count(score) desc) as ranking -- 注意排名不需要分组partition by tag from exam_record left join examination_info using(exam_id) where month(start_time) <7 and year(start_time) = 2020 or year(start_time) = 2021 group by tag,start_year ) select tag, exam_cnt_20, exam_cnt_21, concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),"%") as growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta from( select tag, sum(case when start_year = '2020' then exam_cnt end) as exam_cnt_20, sum(case when start_year = '2021' then exam_cnt end) as exam_cnt_21, sum(case when start_year = '2020' then ranking end) as exam_cnt_rank_20, sum(case when start_year = '2021' then ranking end) as exam_cnt_rank_21 from t1 group by tag ) t2 where exam_cnt_21 !=0 and exam_cnt_20 !=0 order by growth_rate desc, exam_cnt_rank_21 desc