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

# table examination_info exam_record
# 计算2021【上半年】各类试卷的【做完】次数 （看submit_time 或 score）
# 与2020【上半年】同期的增长率 【百分比格式（*100 保留一位小数）
# 以及做完次数排名变化
# 按增长率和21年排名降序输出
# 输出 tag exam_cnt_20 exam_cnt_21 growth_rate exam_cnt_rank_20

with temp as(
    select
        t2.*, rank() over (partition by t2.endtime order by cnt desc) rk 
    from(
        select distinct # 之后要排序 去重处理
            exam_id, endtime,
            count(t.submit_time) over (partition by t.endtime, t.exam_id) cnt
        from (
            select 
                *,
                (case date_format(submit_time,'%Y') when '2020' then 2020 else 2021 end) endtime
		  		# case when 方便之后直接用int类型的2021和2020相减进行自连接查询
            from exam_record
            where submit_time is not null 
                and (
                    submit_time <= '2020-06-30' or
				    (submit_time >= '2021-01-01' and submit_time <= '2021-06-30') 
				  # 划定时间范围【上半年】
                )
        )t
    )t2
)
select 
    e.tag tag,
    t2020.cnt exam_cnt_20,
    t2021.cnt exam_cnt_21,
    concat(ROUND((t2021.cnt-t2020.cnt)/t2020.cnt*100,1),'%') growth_rate, # concat与字符串'%'相连接
    t2020.rk exam_cnt_rank_20,
    t2021.rk exam_cnt_rank_21,
    cast(t2021.rk as signed)-cast(t2020.rk as signed) rank_delta
	# rank()排序是Unsigned，直接相减如果出现负数会报错，因此先用cast将字段类型转化为signed
from temp t2020, temp t2021, examination_info e
where t2021.endtime-t2020.endtime=1 
	and t2021.exam_id = t2020.exam_id 
	and t2020.exam_id=e.exam_id
order by growth_rate desc, exam_cnt_rank_21 desc;