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

SELECT t1.tag, t1.exam_cnt AS exam_cnt_20, t2.exam_cnt AS exam_cnt_21, 
IFNULL(CONCAT(ROUND((t2.exam_cnt - t1.exam_cnt) / t1.exam_cnt * 100, 1), '%'), 0) AS growth_rate,
t1.exam_cnt_rank AS exam_cnt_rank_20, 
t2.exam_cnt_rank AS exam_cnt_rank_21, 
CAST(t2.exam_cnt_rank AS SIGNED) - CAST(t1.exam_cnt_rank AS SIGNED) AS rank_delta
FROM(
    SELECT tag, DATE_FORMAT(start_time,"%Y") AS start_year, COUNT(score) AS exam_cnt, 
       RANK() OVER(PARTITION BY DATE_FORMAT(start_time,"%Y") ORDER BY COUNT(score) DESC) AS exam_cnt_rank
    FROM exam_record AS er LEFT JOIN examination_info AS ei ON er.exam_id = ei.exam_id
    WHERE submit_time IS NOT NULL AND score IS NOT NULL AND DATE_FORMAT(start_time,"%m") <= 6
    GROUP BY tag, DATE_FORMAT(start_time,"%Y")
) AS t1 INNER JOIN (
    SELECT tag, DATE_FORMAT(start_time,"%Y") AS start_year, COUNT(score) AS exam_cnt, 
       RANK() OVER(PARTITION BY DATE_FORMAT(start_time,"%Y") ORDER BY COUNT(score) DESC) AS exam_cnt_rank
    FROM exam_record AS er LEFT JOIN examination_info AS ei ON er.exam_id = ei.exam_id
    WHERE submit_time IS NOT NULL AND score IS NOT NULL AND DATE_FORMAT(start_time,"%m") <= 6
    GROUP BY tag, DATE_FORMAT(start_time,"%Y")
) AS t2 ON t1.start_year = t2.start_year-1 AND t1.tag = t2.tag
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC

为什么要计算delta_rank转换数据类型？

在 SQL 中，如果你执行一个操作，比如 t2.exam_cnt_rank - t1.exam_cnt_rank，并且这两个字段都是 UNSIGNED 类型，那么结果也会被视为 UNSIGNED。UNSIGNED 类型是不允许负值的，它的值范围从 0 开始。这意味着，如果减法的结果理论上是负数（例如，t1 的排名高于 t2），实际上不会得到一个负数，而是会产生一个非常大的正数，因为 UNSIGNED 类型会在计算结果为负时发生下溢，从而回绕到 UNSIGNED 类型所能表示的最大值。