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

这是我第一次写解题思路，除了方便自己复习以为，希望能够大家一些思路。如有不对的地方，欢迎大家指出了~

这个题要求计算出2021年试卷完成数同比2020年的增长率和排名变化。

计算增长率或者排名变化的前提是知道各年各类试卷的完成情况，所以要先计算出各个上半年各类试卷的完成情况，后续所有计算都是基于此部分。

如何筛选出上半年呢？采用的是：date_format(submit_time,'%Y%m') between '202X01' and '202X06'

所以上半年试卷完成数可以采用：case...when...then...else...end的语句，如果在时间范围内，则是计算score的个数，若不是则为空，count（）几种用法的区别：

count(1)和count(*)表示计算所有行数，包括null；

count（变量名）不考虑空值；

count(null) = 0；

当count()的括号中有表达式时，赋予null的数据不统计。所以代码如下：

count(case when date_format(submit_time,'%Y%m') between '202101' and '202106' then score end)

各类试卷：就是将表exam_record和examination_info用‘exam_id’连接起来，然后group by tag。

增长率的计算公式为：（2021年上半年试卷完成数-2020年上半年试卷完成数）/2020年上半年试卷完成数

所以，计算出各类的完成情况后，就可以计算出增长率：

ifnull(concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%'),0) as growth_rate

排名变化：因为我们已经计算出了各年各类试卷的完成数，所以可以根据exam_cnt_rank_20和exam_cnt_rank_21的值进行排序。

所采用的排名函数为：rank() over(order by exam_cnt_21 desc) as exam_cnt_rank_21

截至目前，所有要计算的指标都已经计算完毕，但还要计算一个排名变化情况。也就是exam_cnt_rank_21-exam_cnt_rank_20，但是，如果直接计算结果可能不对，因为排名可能会出现负数，所以核心是使用 cast(targetCol as signed) 将所有涉及到的unsigned字段先转化为signed类型后，再进行运算。signed表示这个变量是有符号的,可以存储整数和负数。

最后，筛选出完成试卷的，即完成数非0，再按照growth_rate 和exam_cnt_rank_21降序排列。

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,
ifnull(concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%'),0) 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,
count(case when date_format(submit_time,'%Y%m') between '202101' and '202106' then score end) as exam_cnt_21,
count(case when date_format(submit_time,'%Y%m') between '202001' and '202006' then score end) as exam_cnt_20
from exam_record
left join examination_info using(exam_id)
group by tag
) t1
) t2
WHERE exam_cnt_20 != 0 AND exam_cnt_21 != 0
order by growth_rate desc,exam_cnt_rank_21 desc;