SQL32 试卷完成数同比2020年的增长率及排名变化

题目主要信息：

计算以下信息

2021 年上半年各类试卷的做完次数相比 2020 年同期的增长率（百分比格式，保留1位小数）
2021 年上半年各类试卷的做完次数相比 2020 年同期做完次数排名变化（次数相同则排名并列）

按增长率和 21 年排名降序输出

问题拆解：

总体思路为

统计每类试卷在 2020 年和 2021 年的做完次数
根据做完次数计算每类试卷排名
基于上述数据统计每类试卷的变化率

为了方便拆分计算逻辑，我们先用 WITH AS 语句执行 1、2 步骤

通过 MONTH() 函数获取 DATETIME 类型中的月份信息，在 WHERE 语句中对其进行筛选，保留上半年数据（MONTH < 7）。知识点：MONTH，WHERE
通过判断 submit_time 是否为空来筛选有效的提交记录。知识点：IS NOT NULL
为了获取每类试卷的统计值，我们通过 GROUP BY exam_id 对其进行聚合统计。需要统计的值为 2020 年、2021 年的有效填答数。有效填答我们已经通过上面两条实现了筛选，因此这里只需要分别统计两个年份。具体实现方式为：通过 YEAR() 函数获取年份，并通过 SUM(IF(YEAR=2021, 1, 0)) 来统计某个年份的填答数量。知识点：SUM, IF, GROUP BY, YEAR
根据统计出的填答数信息，获取每类试卷的排序。操作方式为 RANK() OVER(ORDER BY cnt_20 DESC)。RANK() 和 ROW_NUMBER() 的主要区别为，RANK 会给并列排名的元素以相同的位次，而 ROW_NUMBER 不会。ORDER BY cnt_20 DESC 表示按照 cnt_20 字段降序输出。知识点：RANK, ROW_NUMBER, ORDER BY, DESC

WITH total_submit AS (
    SELECT
        *,
        RANK() OVER(ORDER BY result.cnt_20 DESC) AS rk_20,
        RANK() OVER(ORDER BY result.cnt_21 DESC) AS rk_21
    FROM
    (
        SELECT
            MAX(tag) AS tag,
            SUM(IF(YEAR(exam_record.submit_time)=2020, 1, 0)) AS cnt_20,
            SUM(IF(YEAR(exam_record.submit_time)=2021, 1, 0)) AS cnt_21
        FROM
            exam_record
        LEFT JOIN
            examination_info
        ON exam_record.exam_id = examination_info.exam_id
        WHERE
            exam_record.submit_time IS NOT NULL
            AND MONTH(submit_time) < 7
        GROUP BY
            exam_record.exam_id
    ) AS result
)

WITH AS 执行完毕后，我们获得了一个名为 total_submit 的临时表，可以在之后的运算中直接引用。按照题意，我们还需要计算 增长率 和 排名变化数。

增长率可以通过 (cnt_21-cnt_20) / cnt_20 来计算，此时我们获得的是一个 0~1 的实数，还需要将其转换为百分比。我们可以通过将其乘 100 并拼接上百分号的方式实现。具体操作为 CONCAT(ROUND(x * 100, 1), '%')。知识点：CONCAT, ROUND
排名变化数可以直接通过减法操作计算。此处需要注意的是，由于 RANK 返回值是 UNSIGNED 类型，如果减法计算的结果是负值，会触发溢出报错，需要用 CAST 将其转换为有符号整数格式。知识点：CAST

代码：

WITH total_submit AS (
    SELECT
        *,
        RANK() OVER(ORDER BY result.cnt_20 DESC) AS rk_20,
        RANK() OVER(ORDER BY result.cnt_21 DESC) AS rk_21
    FROM
    (
        SELECT
            MAX(tag) AS tag,
            SUM(IF(YEAR(exam_record.submit_time)=2020, 1, 0)) AS cnt_20,
            SUM(IF(YEAR(exam_record.submit_time)=2021, 1, 0)) AS cnt_21
        FROM
            exam_record
        LEFT JOIN
            examination_info
        ON exam_record.exam_id = examination_info.exam_id
        WHERE
            exam_record.submit_time IS NOT NULL
            AND MONTH(submit_time) < 7
        GROUP BY
            exam_record.exam_id
    ) AS result
)
SELECT
    tag, cnt_20, cnt_21,
    CONCAT(ROUND((cnt_21-cnt_20) / cnt_20 * 100, 1), '%'),
    rk_20, rk_21, CAST(rk_21 AS signed)-CAST(rk_20 AS signed)
FROM
    total_submit
WHERE
    cnt_20 > 0 AND cnt_21 > 0
ORDER BY
    (cnt_21-cnt_20) / cnt_20 DESC,
    rk_21 DESC

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

SQL32 试卷完成数同比2020年的增长率及排名变化

题目主要信息：

问题拆解：

代码：