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

明确题意：

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

问题分解：

统计每类试卷2020~2021各年上半年完成数及排名拼接到一行的结果，生成子表 t_exam_complete_20_21_lead:
- 统计每类试卷20/21年上半年完成数及排名，生成子表 t_exam_complete_20_21:
  - 统计20/21年上半年每类试卷被做完的次数，生成子表 t_exam_complete_tag_rank:
    - 提取20/21年上半年每条完成了的试卷的tag和完成年，生成子表 t_exam_complete_tag_month:
      - 关联试卷作答表和试卷信息表：exam_record JOIN examination_info USING(exam_id)
      - 筛选做完试卷的年份和月份：WHERE YEAR(submit_time) in (2020, 2021) and month(submit_time)<7
      - 选取tag和年份：SELECT tag, YEAR(submit_time) as start_year
    - 按tag和年份分组：GROUP BY tag, start_year
    - 对做完试卷数计数：count(1) as exam_cnt
  - 对做完试卷数排名，按做完年份分区按做完数降序生成不连续排名：
    - RANK() over(PARTITION BY start_year ORDER BY exam_cnt DESC) as exam_cnt_rank
- 生成每个tag的20/21年统计值：
  - 做完数据数：
    - 20年，即第一个：exam_cnt as exam_cnt_20
    - 同tag按年份排序下一个，即21年：LEAD(exam_cnt) over(PARTITION BY tag ORDER BY start_year) as exam_cnt_21
  - 做完数排名：
    - 20年，即第一个：exam_cnt_rank as exam_cnt_rank_20
    - 同tag按年份排序下一个，即21年：LEAD(exam_cnt_rank) over(PARTITION BY tag ORDER BY start_year) as exam_cnt_rank_21
筛选21年有值的记录：WHERE exam_cnt_21 IS NOT NULL
计算增长率，并格式化：concat(round(100 * exam_cnt_21 / exam_cnt_20 - 100, 1), "%") as growth_rate
计算排名变化，注意RANK()返回无符号类型，排名变化可能为负，直接减会出错：
- CAST(exam_cnt_rank_21 as SIGNED) - CAST(exam_cnt_rank_20 as SIGNED) as rank_delta

细节问题：

表头重命名：as
按增长率和21年排名降序排序：ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC

完整代码：

SELECT tag, exam_cnt_20, exam_cnt_21,
    concat(round(100 * exam_cnt_21 / exam_cnt_20 - 100, 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
FROM (
    SELECT tag, exam_cnt as exam_cnt_20,
        LEAD(exam_cnt) over(PARTITION BY tag ORDER BY start_year) as exam_cnt_21,
        exam_cnt_rank as exam_cnt_rank_20,
        LEAD(exam_cnt_rank) over(PARTITION BY tag ORDER BY start_year) as exam_cnt_rank_21
    FROM (
        SELECT tag, start_year, exam_cnt,
            RANK() over(PARTITION BY start_year ORDER BY exam_cnt DESC) as exam_cnt_rank
        FROM (
            SELECT tag, start_year, count(1) as exam_cnt
            FROM (
                SELECT tag, YEAR(submit_time) as start_year
                FROM exam_record JOIN examination_info USING(exam_id)
                WHERE YEAR(submit_time) in (2020, 2021) and month(submit_time)<7
            ) as t_exam_complete_tag_month -- 提取20/21年上半年每条完成了的试卷的tag和完成年
            GROUP BY tag, start_year
        ) as t_exam_complete_tag_rank -- 20/21年上半年每类试卷被做完的次数
    ) as t_exam_complete_20_21 -- 每类试卷2020/2021完成数及排名统计
) as t_exam_complete_20_21_lead -- 每类试卷2020~2021完成数及排名拼接
WHERE exam_cnt_21 IS NOT NULL
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC;