题解 | #试卷完成数同比2020年的增长率及排名变化#
试卷完成数同比2020年的增长率及排名变化
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
# 把21年和22年的先都放成示例的样子
# 临时表0，把各类试卷的20年和21年上半年的完成数提取出来
WITH temp_0 AS(
SELECT tag,
SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN 202001 AND 202006 THEN 1 ELSE 0 END) exam_cnt_20,
SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN 202101 AND 202106 THEN 1 ELSE 0 END) exam_cnt_21
FROM examination_info a
JOIN exam_record b USING(exam_id)
GROUP BY tag
),
# 在0的基础上，分别对20年和21年的完成数排序，直接rank
temp_1 AS(
SELECT tag, exam_cnt_20, exam_cnt_21,
RANK() OVER(ORDER BY exam_cnt_20 DESC) exam_cnt_20_rank,
RANK() OVER(ORDER BY exam_cnt_21 DESC) exam_cnt_21_rank
FROM temp_0
)
# 主查询，分别使用数量和排序进行两个计算字段
# 百分比时要注意，mysql我尝试了format，cast都不行，最终采用该方式
# 还有排名相减，要改成有符号的，不然报错"BIGINT UNSIGNED value is out of range in '(`temp_1`.`exam_cnt_21_rank` - `temp_1`.`exam_cnt_20_rank`)'"
# 再排除掉某一年没有完成数的试卷
#最后排序
SELECT tag, exam_cnt_20, exam_cnt_21,
CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) / exam_cnt_20 * 100, 1), '%') growth_rate,
exam_cnt_20_rank, exam_cnt_21_rank,
(CAST(exam_cnt_21_rank AS SIGNED) - CAST(exam_cnt_20_rank AS SIGNED)) rank_delta
FROM temp_1
WHERE exam_cnt_20 > 0 AND exam_cnt_21 > 0
ORDER BY growth_rate DESC, exam_cnt_21_rank DESC
踩的坑：
1.没看清题，以为是两年，原来是两个半年
2.百分数的构成
3.排名数字相减，在MySQL中，BIGINT UNSIGNED 类型的值必须是非负的，范围从 0 到 2^64-1。
问题原因是在进行减法操作 exam_cnt_21_rank - exam_cnt_20_rank 时，如果 exam_cnt_20_rank 大于 exam_cnt_21_rank，结果将是负数，这超出了 BIGINT UNSIGNED 类型的允许范围。
解决方案：
- 类型转换:在进行减法运算之前，您可以将列转换为有符号类型，比如 SIGNED。这样即使结果是负数，也不会超出范围。
- 避免无符号整数类型:如果可能，考虑使用有符号的整数类型（如 BIGINT 而不是 BIGINT UNSIGNED），特别是在您预计结果可能为负的计算中。
- 逻辑检查:在执行减法之前，您可以通过逻辑检查来避免负结果，特别是当处理无符号类型时。
