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

#需要按tag进行分类，筛选出2020和2021各上半年的数据，表格整合，最后进行筛选
#这里要注意出现计算为负数的情况会报错
select
tag
,exam_cnt_20
,exam_cnt_21
,concat(round(((exam_cnt_21-exam_cnt_20)/exam_cnt_20)*100,1),'%') growth_rate
,exam_cnt_rank_20
,exam_cnt_rank_21
,case when exam_cnt_rank_21 >= exam_cnt_rank_20 then round((exam_cnt_rank_21-exam_cnt_rank_20)) 
      else concat('-',round((exam_cnt_rank_20-exam_cnt_rank_21))) end rank_delta
#,cast(round(exam_cnt_21-exam_cnt_20) as signed) 结果转换为负数
from
(
    select
    tag
    ,start_year
    ,lag(exam_cnt)over(partition by tag order by start_year) exam_cnt_20
    ,exam_cnt exam_cnt_21
    ,lag(exam_cnt_rank)over(partition by tag order by start_year) exam_cnt_rank_20
    ,exam_cnt_rank exam_cnt_rank_21
    from
    (
        select
        tag
        ,start_year
        ,exam_cnt
        ,rank()over(partition by start_year order by exam_cnt desc) exam_cnt_rank
        from
        (
            #求今年上半年和去年上半年的数据进行列表合并
            select
            tag
            ,substring(submit_time,1,4) start_year
            ,count(submit_time) exam_cnt
            from exam_record er left join examination_info ei
            on er.exam_id = ei.exam_id
            where substring(cast(submit_time as date),1,7) >= '2020-01'
            and substring(cast(submit_time as date),1,7) < '2020-07'
            group by 1,2

            union all

            select
            tag
            ,substring(submit_time,1,4) start_year
            ,count(submit_time)
            from exam_record er left join examination_info ei
            on er.exam_id = ei.exam_id
            where substring(cast(submit_time as date),1,7) >= '2021-01'
            and substring(cast(submit_time as date),1,7) < '2021-07'
            group by 1,2
        ) table1
    ) table2
) table3
where start_year = 2021
and exam_cnt_20 is not null
order by growth_rate desc 
,rank_delta desc