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

需要注意的几个点：

• 排名变化：rank()返回无符号类型，不能直接减，用cast转换一下。cast(strng as type),type:char(字符型)、DATE(日期型)、DATETIME(日期和时间型)、DECIMAL(float型 SIGNED(int整数型)、TIME(时间型)
• 排序：对tag类别中的时间排序，不要下意识用partition

select tag,exam_cnt_20,exam_cnt_21,
			 concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,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 (
			select tag,start_year_20,exam_cnt_20,exam_cnt_rank_20,start_year_21,exam_cnt_21,exam_cnt_rank_21
			from (
					select tag,'2020' as start_year_20,count(submit_time) as exam_cnt_20,
								 rank() over(order by count(submit_time) desc) as exam_cnt_rank_20
					from exam_record
					left join examination_info using(exam_id)
					where year(start_time)=2020 and date_format(start_time,'%Y%m%d') between '20200101' and '20200630' and submit_time is not null
					group by tag
					 ) as t20
			inner join (
					select tag,'2021' as start_year_21,count(submit_time) as exam_cnt_21,
									rank() over(order by count(submit_time) desc) as exam_cnt_rank_21
					from exam_record
					left join examination_info using(exam_id)
					where year(start_time)=2021 and date_format(start_time,'%Y%m%d') between '20210101' and '20210630' and submit_time is not null
					group by tag
								) as t21 using(tag)
		 ) as t
order by growth_rate desc,exam_cnt_rank_21 desc