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

思路：分别统计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
• 排序

  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