题解 | #SQL141 详细分体解答#

题目分析和要求梳理：

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

明确要求：

1.2021年上半年

2.各类试卷的做完次数  相比2020年上半年同期的增长率

3.百分比格式，保留1位小数 

4.做完次数排名变化

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

        另外在题目的解释中可以发现，做完次数是并列，队排序窗口函数美敏感的同学立刻能发现这边应该用rank()，那么题目要求的隐含条件还有就是：        

6.做完次数是并列的

        另外，题目给出了另外的表格，如果能把表格先出来，就成功一小半来了。

 解体解答：

第一步，直接求最简单的，求每个类别也就是tag2020年和2021年上半年的昨晚数量：

select tag ,year(start_time) start_year,count(score) exam_cnt
from examination_info e1 join exam_record e2
on e1.exam_id = e2.exam_id
where month(start_time) < 7
group by tag,year(start_time)
having exam_cnt != 0

难点一：上半年的数量，其实也算不上难点，直接求month(start_time) < 7就可以了

难点二：此处加上了exam_cnt != 0，是后来加上的，做完次数为0就不统计了

产生的结果如下:

第二步，利用rank()窗口函数进行排序，给出的答案中要求带着相应的排序

select tag,start_year,exam_cnt,rank() over(partition by start_year order by exam_cnt desc) rk from(
        select tag ,year(start_time) start_year,count(score) exam_cnt
        from examination_info e1 join exam_record e2
        on e1.exam_id = e2.exam_id
        where month(start_time) < 7
        group by tag,year(start_time)
        having exam_cnt != 0
      )t1

这一步非常简单,没啥好说的

第三步,求每个类别的相较于上年的增长率,以及相应的排名,这边用个临时表

with t2 as(
    select tag,start_year,exam_cnt,rank() over(partition by start_year order by exam_cnt desc) rk
    from(
        select tag ,year(start_time) start_year,count(score) exam_cnt
        from examination_info e1 join exam_record e2
        on e1.exam_id = e2.exam_id
        where month(start_time) < 7
        group by tag,year(start_time)
        having exam_cnt != 0
    )t1
)
select t3.tag,t3.exam_cnt exam_cnt_20,
t4.exam_cnt exam_cnt_21,
(t4.exam_cnt-t3.exam_cnt)/t3.exam_cnt de,t3.rk exam_cnt_rank_20,
t4.rk exam_cnt_rank_21,
(cast(t4.rk as signed)-cast(t3.rk as signed)) rank_delta from 
t2 t3 join t2 t4 on t3.tag = t4.tag
where t3.start_year!= t4.start_year && t3.start_year = 2020 && t4.start_year = 2021
order by de desc,exam_cnt_rank_21 desc

难点三:怎么把相同的处于不同行的相同类别的数据连接起来,这边我使用了自连接,我认为比较好理解一点,那么在代码中就是

from t2 t3 join t2 t4 on t3.tag = t4.tag

难点四:过滤条件

where t3.start_year!= t4.start_year && t3.start_year = 2020 && t4.start_year = 2021

这边我写的比较复杂,但是比较通用,即在自连接的情况下,直接过滤类别两个不同时间

难点五:如果直接相减会报错,BIGINT UNSIGNED value is out of range

t4.rk-t3.rk

那么使用cast就可以了

(cast(t4.rk as signed)-cast(t3.rk as signed))

第四步,将百分比添加上

select tag,exam_cnt_20, exam_cnt_21,concat(round(100*de,1),'%'),exam_cnt_rank_20, exam_cnt_rank_21,rank_delta from(
select t3.tag,t3.exam_cnt exam_cnt_20,t4.exam_cnt exam_cnt_21,(t4.exam_cnt-t3.exam_cnt)/t3.exam_cnt de,t3.rk exam_cnt_rank_20,t4.rk exam_cnt_rank_21,(cast(t4.rk as signed)-cast(t3.rk as signed)) rank_delta from 
t2 t3 join t2 t4 on t3.tag = t4.tag
where t3.start_year!= t4.start_year && t3.start_year = 2020 && t4.start_year = 2021
order by de desc,exam_cnt_rank_21 desc
)t5

 答案总和：

with t2 as(
    select tag,start_year,exam_cnt,rank() over(partition by start_year order by exam_cnt desc) rk
    from(
        select tag ,year(start_time) start_year,count(score) exam_cnt
        from examination_info e1 join exam_record e2
        on e1.exam_id = e2.exam_id
        where month(start_time) < 7
        group by tag,year(start_time)
        having exam_cnt != 0
    )t1
)
select tag,exam_cnt_20, exam_cnt_21,concat(round(100*de,1),'%'),exam_cnt_rank_20, exam_cnt_rank_21,rank_delta from(
select t3.tag,t3.exam_cnt exam_cnt_20,t4.exam_cnt exam_cnt_21,(t4.exam_cnt-t3.exam_cnt)/t3.exam_cnt de,t3.rk exam_cnt_rank_20,t4.rk exam_cnt_rank_21,(cast(t4.rk as signed)-cast(t3.rk as signed)) rank_delta from 
t2 t3 join t2 t4 on t3.tag = t4.tag
where t3.start_year!= t4.start_year && t3.start_year = 2020 && t4.start_year = 2021
order by de desc,exam_cnt_rank_21 desc
)t5