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

题目

2021年上半年各类试卷的做完次数相比2020年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化； 按增长率和21年排名降序输出。

思路

1、计算2021年上半年各类试卷的做完次数 2、计算2020年上半年各类试卷的做完次数 3、进行比较计算

注意

计算排名变化，注意RANK()返回无符号类型，排名变化可能为负，直接减会出错： CAST(exam_cnt_rank_21 as SIGNED) - CAST(exam_cnt_rank_20 as SIGNED) as rank_delta

select tag,exam_cnt_20,exam_cnt_21,concat(round((100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20),1),"%") as growth_rate,
        exam_cnt_rank_20,exam_cnt_rank_21,
        CAST(exam_cnt_rank_21 AS SIGNED)-CAST(exam_cnt_rank_20 AS SIGNED) as rank_delta
        -- exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta
from
    (select tag,count(submit_time) exam_cnt_20,
             rank() over(order by count(submit_time) desc) 	exam_cnt_rank_20
    from examination_info join exam_record using(exam_id)
    where DATE_FORMAT(submit_time,'%Y%m') between 202001 and 202006
    group by tag) tag_20
join
    (select tag,count(submit_time) exam_cnt_21,
              rank() over(order by count(submit_time) desc) exam_cnt_rank_21
    from examination_info join exam_record using(exam_id)
    where DATE_FORMAT(submit_time,'%Y%m') between 202101 and 202106
    group by tag) tag_21
using(tag)
order by growth_rate desc,exam_cnt_rank_21 desc