继续练牛客SQL进阶题。试卷完成数同比2020年的增长率及排名变化_牛客题霸_牛客网 (nowcoder.com)
题目：SQL141 试卷完成数同比2020年的增长率及排名变化
题干信息
现有试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
| id | exam_id | tag | difficulty | duration | release_time |
|---|---|---|---|---|---|
| 1 | 9001 | SQL | hard | 60 | 2021-01-01 10:00:00 |
| 2 | 9002 | C++ | hard | 80 | 2021-01-01 10:00:00 |
| 3 | 9003 | 算法 | hard | 80 | 2021-01-01 10:00:00 |
| 4 | 9004 | PYTHON | medium | 70 | 2021-01-01 10:00:00 |
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
| id | uid | exam_id | start_time | submit_time | score |
|---|---|---|---|---|---|
| 1 | 1001 | 9001 | 2020-08-02 10:01:01 | 2020-08-02 10:31:01 | 89 |
| 2 | 1002 | 9001 | 2020-04-01 18:01:01 | 2020-04-01 18:59:02 | 90 |
| 3 | 1001 | 9001 | 2020-04-01 09:01:01 | 2020-04-01 09:21:59 | 80 |
| 5 | 1002 | 9001 | 2021-03-02 19:01:01 | 2021-03-02 19:32:00 | 20 |
| 8 | 1003 | 9001 | 2021-05-02 12:01:01 | 2021-05-02 12:31:01 | 98 |
| 13 | 1003 | 9001 | 2020-01-02 10:01:01 | 2020-01-02 10:31:01 | 89 |
| 9 | 1001 | 9002 | 2020-02-02 12:01:01 | 2020-02-02 12:20:01 | 99 |
| 10 | 1002 | 9002 | 2021-02-02 12:01:01 | 2020-02-02 12:43:01 | 81 |
| 11 | 1001 | 9002 | 2020-01-02 19:01:01 | 2020-01-02 19:59:01 | 69 |
| 16 | 1002 | 9002 | 2020-02-02 12:01:01 | ||
| 17 | 1002 | 9002 | 2020-03-02 12:11:01 | ||
| 18 | 1001 | 9002 | 2021-05-05 18:01:01 | ||
| 4 | 1002 | 9003 | 2021-01-20 10:01:01 | 2021-01-20 10:10:01 | 81 |
| 6 | 1001 | 9003 | 2021-04-02 19:01:01 | 2021-04-02 19:40:01 | 89 |
| 15 | 1002 | 9003 | 2021-01-01 18:01:01 | 2021-01-01 18:59:02 | 90 |
| 7 | 1004 | 9004 | 2020-05-02 12:01:01 | 2020-05-02 12:20:01 | 99 |
| 12 | 1001 | 9004 | 2021-09-02 12:11:01 | ||
| 14 | 1002 | 9004 | 2020-01-01 12:11:01 | 2020-01-01 12:31:01 | 83 |
请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。
由示例数据结果输出如下：
| tag | exam_cnt_20 | exam_cnt_21 | growth_rate | exam_cnt_rank_20 | exam_cnt_rank_21 | rank_delta |
|---|---|---|---|---|---|---|
| SQL | 3 | 2 | -33.3% | 1 | 2 | 1 |
解释：2020年上半年有3个tag有作答完成的记录，分别是C++、SQL、PYTHON，它们被做完的次数分别是3、3、2，做完次数排名为1、1（并列）、3；
2021年上半年有2个tag有作答完成的记录，分别是算法、SQL，它们被做完的次数分别是3、2，做完次数排名为1、2；具体如下：
| tag | start_year | exam_cnt | exam_cnt_rank |
|---|---|---|---|
| C++ | 2020 | 3 | 1 |
| SQL | 2020 | 3 | 1 |
| PYTHON | 2020 | 2 | 3 |
| 算法 | 2021 | 3 | 1 |
| SQL | 2021 | 2 | 2 |
因此能输出同比结果的tag只有SQL，从2020到2021年，做完次数3=>2，减少33.3%（保留1位小数）；排名1=>2，后退1名。
解析
题目需要注意的首先是目标时间，题干有说明需要查询出数据的时间范围为20年的上半年以及21年的上半年的提交的试卷数据，也就是没有得分的数据都要去掉。
这里提一下同期增长率的计算方法，20年是3，21年是2，那么增长率计算共识为：
然后就是在编写SQL代码的时候可能遇到的异常问题： mysql 当两个字段想减时，如果其中一个或两个字段的类型的unsigned无签名类型，如果想减的值小于0则会报错（BIGINT UNSIGNED value is out of range）
解决办法就是使用cast()函数将unsigned类型转换为signed类型。如：
select cast(1 as signed)-2;
答题
首先需要求解出各类试卷在20、21年上半年的试卷的提交情况
select
tbb.tag,
year(tba.start_time) as start_year,
count(tba.submit_time) as exam_cnt,
rank() over(partition by year(tba.start_time) order by count(tba.submit_time) desc) as exam_cnt_rank
from
(select
exam_id, start_time, submit_time, score
from exam_record where start_time between "2020-01-01" and "2020-06-30" or start_time between "2021-01-01" and "2021-06-30"
)as tba inner join examination_info as tbb
on tba.exam_id=tbb.exam_id and tba.score is not null
group by tba.exam_id, start_year
;
可以看到只有SQL是在20、21年都有提交的情况。所以最终求解应该是只有SQL一行数据。
这里因为需要对上述的结果进行内连接，所以这里用with ... as ..创建临时表
with temp as (
select
tbb.tag,
year(tba.start_time) as start_year,
count(tba.submit_time) as exam_cnt,
rank() over(partition by year(tba.start_time) order by count(tba.submit_time) desc) as exam_cnt_rank
from
(select
exam_id, start_time, submit_time, score
from exam_record where start_time between "2020-01-01" and "2020-06-30" or start_time between "2021-01-01" and "2021-06-30"
)as tba inner join examination_info as tbb
on tba.exam_id=tbb.exam_id and tba.score is not null
group by tba.exam_id, start_year)
select
tbc.tag, tbc.exam_cnt as exam_cnt_20, tbd.exam_cnt as exam_cnt_21,
concat(round(((tbd.exam_cnt-tbc.exam_cnt)/tbc.exam_cnt) * 100, 1), "%") as growth_rate,
tbc.exam_cnt_rank as exam_cnt_rank_20m, tbd.exam_cnt_rank as exam_cnt_rank_21,
cast(tbd.exam_cnt_rank as signed)-cast(tbc.exam_cnt_rank as signed) as rank_delta
from
(select * from temp where start_year=2020) as tbc
inner join
(select * from temp where start_year=2021) as tbd
on tbc.tag=tbd.tag
order by growth_rate desc, exam_cnt_rank_21 desc
;
最终结果：
