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

/*按年份把各类tag的作答完成情况情况和排名计算出来*/
WITH t AS(
    select tag
        ,year(submit_time) AS start_year
        ,count(submit_time) AS exam_cnt
        ,rank() over(partition by year(submit_time) order by count(submit_time) desc) AS exam_cnt_rank
    from exam_record er
    inner join examination_info ei on er.exam_id = ei.exam_id
    where submit_time is not null and (submit_time between '2020-01-01' and '2020-06-30' or submit_time between '2021-01-01' and '2021-06-30')
    group by tag
            ,year(submit_time)
)

select tag
        ,sum(if(start_year=2020,exam_cnt,0)) AS exam_cnt_20
        ,sum(if(start_year=2021,exam_cnt,0)) AS exam_cnt_21
        ,CONCAT(round((sum(if(start_year=2021,exam_cnt,0)) - sum(if(start_year=2020,exam_cnt,0)))/sum(if(start_year=2020,exam_cnt,0)) * 100,1),'%') AS growth_rate
        ,sum(if(start_year=2020,exam_cnt_rank,0)) AS exam_cnt_rank_20
        ,sum(if(start_year=2021,exam_cnt_rank,0)) AS exam_cnt_rank_21
        ,sum(if(start_year=2021,exam_cnt_rank,0)) - sum(if(start_year=2020,exam_cnt_rank,0)) AS rank_delta
from t
where tag in
(
    select tag
    from t
    group by tag
    having count(start_year) >= 2
)
group by tag
order by growth_rate desc
        ,exam_cnt_rank_21 desc

1、表t的字段会有各个类别试卷在每一年的作答完成数量，以及在当年的排名

2、从t中筛选出两年都有作答的tag，having count(tag) > 2

3、按照tag对表t做group by，因此得到的都是统计数据，没法得到每一行的明细数据。用sum(if( ) )来读取出每一年的明细，

if(start_year=2020,exam_cnt,0)：类似于一个隐形字段，如果start_year=2020，那么隐形字段取对应行的exam_cnt,否则取0，然后将同个tag内的隐形字段求和，sum(if(start_year=2020,exam_cnt,0))就能得到明细数据。