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

【场景】：等级值百分比

【分类】：专用窗口函数、多表连接

分析思路

难点：

长整型的数据类型要求不能有负号产生，用cast函数转换数据类型为signed。

（1）统计2020年上半年各类试卷的做完次数

• [条件]：where date(start_time) between '20200101' and '20200630'
• [使用]：group by

（2）统计2021年上半年各类试卷的做完次数

• [条件]：where date(start_time) between '20210101' and '20210630'
• [使用]：group by

（3）统计2020上半年做完次数排名

• [使用]：rank()

（4）统计2021上半年做完次数排名

• [使用]：rank()

（5）统计每个用户增长率、做完次数排名变化，按增长率和21年排名降序输出

使用左连接相当于where条件exam_cnt_20 is not null

• [条件]：exam_cnt_21 is not null
• [使用]：left join

注意： 1.增长率计算公式：(exam_cnt_21-exam_cnt_20)/exam_cnt_20 2.做完次数排名变化（2021年和2020年比排名升了或者降了多少） 3.计算公式：exam_cnt_rank_21-exam_cnt_rank_20 4.条件：exam_cnt_20、exam_cnt_21都不为空 5.长整型的数据类型要求不能有负号产生，所以用cast函数转换数据类型为signed。如果不用cast函数两数相减时一旦产生负数会报错：BIGINT UNSIGNED（长整型数字无符号）。所以要转换数据类型为signed类型，保证产生负数也不会报错。对比增长率，明明相减的时候它也会产生负数，为什么就不用cast处理呢？因为round函数对正数、负数都可以做处理。

扩展

前往查看： MySQL 窗口函数

求解代码

方法一

with子句

with 
    main as(
        #2020年上半年各类试卷的做完次数
        select
            tag,
            '2020' as start_year_20,
            count(start_time) as exam_cnt_20
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and 7 > month(start_time)
        and year(start_time)=2020
        and submit_time is not null
        group by tag
    ),
    attr as(
    #2021年上半年各类试卷的做完次数
        select
            tag,
            '2021' as start_year_21,
            count(start_time) as exam_cnt_21
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and date(start_time) between '20210101' and '20210630'
        and submit_time is not null
        group by tag
    ),
    main1 as(
        #2020上半年做完次数排名
        select
            tag,
            start_year_20,
            exam_cnt_20,
            rank() over (partition by start_year_20 order by exam_cnt_20 desc) as exam_cnt_rank_20
        from main
    ),
    attr1 as(
        #2021上半年做完次数排名
        select
            tag,
            start_year_21,
            exam_cnt_21,
            rank() over (partition by start_year_21 order by exam_cnt_21 desc) as exam_cnt_rank_21
        from attr
    )

#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出
select
    tag,
    exam_cnt_20,
    exam_cnt_21,
    concat(round(100*(exam_cnt_21-exam_cnt_20)/exam_cnt_20,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 main1
left join attr1 using(tag)
where exam_cnt_21 is not null
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)/exam_cnt_20,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(
    #2020上半年做完次数排名
    select
        tag,
        start_year_20,
        exam_cnt_20,
        rank() over (order by exam_cnt_20 desc) as exam_cnt_rank_20
    from(
        #2020年上半年各类试卷的做完次数
        select
            tag,
            '2020' as start_year_20,
            count(start_time) as exam_cnt_20
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and date_format(start_time,'%Y%m%d') between '20200101' and '20200630'
        and submit_time is not null
        group by tag
    ) main
) main1
left join(
    #2021上半年做完次数排名
    select
        tag,
        start_year_21,
        exam_cnt_21,
        rank() over (order by exam_cnt_21 desc) as exam_cnt_rank_21
    from(
        #2021年上半年各类试卷的做完次数
        select
            tag,
            '2021' as start_year_21,
            count(start_time) as exam_cnt_21
        from examination_info a,exam_record b
        where a.exam_id = b.exam_id
        and substring(start_time,1,10) between '2021-01-01' and '2021-06-30'
        and submit_time is not null
        group by tag
    ) attr
) attr1 using(tag)
where exam_cnt_21 is not null
order by growth_rate desc, exam_cnt_rank_21 desc