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

select *,cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) as rank_delta
from(
select tag,exam_cnt_20,exam_cnt_21,concat(round((exam_cnt_21 - exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate,
rank()over(order by exam_cnt_20 desc) as exam_cnt_rank_20,
rank()over(order by exam_cnt_21 desc) as exam_cnt_rank_21
from(
SELECT #第一步：找到所有tag在2020，2021上半年的完成数
    tag,
    SUM(IF(DATE_FORMAT(submit_time, '%Y-%m') BETWEEN '2020-01' AND '2020-06', 1, 0)) exam_cnt_20,
    SUM(IF(DATE_FORMAT(submit_time, '%Y-%m') BETWEEN '2021-01' AND '2021-06', 1, 0)) exam_cnt_21
  FROM exam_record
  LEFT JOIN examination_info ei USING(exam_id)
  GROUP BY tag) as t1) as t2
where exam_cnt_20 != 0 and exam_cnt_21 != 0
order by growth_rate desc,exam_cnt_rank_21 desc

先找到2020和2021上半年的数据，然后去计算完成数，增长率和年度排名，筛选两年都有记录的tag，计算rankdelta，这里要注意cast的类型转换，不然负数的计算无法进行，必须把rank得到的unsigned类型变成signed类型。