现有试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
现有试卷信息表examination_info(exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间):
drop table if exists examination_info,exam_record; CREATE TABLE examination_info ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', exam_id int UNIQUE NOT NULL COMMENT '试卷ID', tag varchar(32) COMMENT '类别标签', difficulty varchar(8) COMMENT '难度', duration int NOT NULL COMMENT '时长', release_time datetime COMMENT '发布时间' )CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE exam_record ( id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID', uid int NOT NULL COMMENT '用户ID', exam_id int NOT NULL COMMENT '试卷ID', start_time datetime NOT NULL COMMENT '开始时间', submit_time datetime COMMENT '提交时间', score tinyint COMMENT '得分' )CHARACTER SET utf8 COLLATE utf8_general_ci; INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES (9001, 'SQL', 'hard', 60, '2020-01-01 10:00:00'), (9002, 'C++', 'hard', 80, '2020-01-01 10:00:00'), (9003, '算法', 'hard', 80, '2020-01-01 10:00:00'), (9004, 'PYTHON', 'medium', 70, '2020-01-01 10:00:00'); INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES (1001, 9001, '2020-08-02 10:01:01', '2020-08-02 10:31:01', 89), (1002, 9001, '2020-04-01 18:01:01', '2020-04-01 18:59:02', 90), (1001, 9001, '2020-04-01 09:01:01', '2020-04-01 09:21:59', 80), (1002, 9003, '2021-01-20 10:01:01', '2021-01-20 10:10:01', 81), (1002, 9001, '2021-03-02 19:01:01', '2021-03-02 19:32:00', 20), (1001, 9003, '2021-04-02 19:01:01', '2021-04-02 19:40:01', 89), (1004, 9004, '2020-05-02 12:01:01', '2020-05-02 12:20:01', 99), (1003, 9001, '2021-05-02 12:01:01', '2021-05-02 12:31:01', 98), (1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99), (1002, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81), (1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69), (1001, 9004, '2021-09-02 12:11:01', null, null), (1003, 9001, '2020-01-02 10:01:01', '2020-01-02 10:31:01', 89), (1002, 9004, '2020-01-01 12:11:01', '2020-01-01 12:31:01', 83), (1002, 9003, '2021-01-01 18:01:01', '2021-01-01 18:59:02', 90), (1002, 9002, '2020-02-02 12:01:01', null, null), (1002, 9002, '2020-03-02 12:11:01', null, null), (1001, 9002, '2021-05-05 18:01:01', null, null);
SQL|3|2|-33.3%|1|2|1
with cnt_2021_2020 AS ( select tag,start_year,exam_cnt,rank()over(partition by start_year order by exam_cnt desc) as exam_cnt_rank from (select distinct ef.tag,year(start_time) as start_year,count(submit_time)over(partition by ef.tag) as exam_cnt from exam_record er left join examination_info ef on er.exam_id=ef.exam_id where year(start_time)='2021' and month(start_time)<=6 union all select distinct ef.tag,year(start_time) as start_year,count(submit_time)over(partition by ef.tag) as exam_cnt from exam_record er left join examination_info ef on er.exam_id=ef.exam_id where year(start_time)='2020' and month(start_time)<=6) t2 ) select t3.tag ,t3.exam_cnt as exam_cnt_20 ,t4.exam_cnt as exam_cnt_21 ,concat(left((t4.exam_cnt-t3.exam_cnt)/t3.exam_cnt*100,5),'%') as growth_rate ,t3.exam_cnt_rank as exam_cnt_rank_20 ,t4.exam_cnt_rank as exam_cnt_rank_21 ,if(t4.exam_cnt_rank>t3.exam_cnt_rank,t4.exam_cnt_rank-t3.exam_cnt_rank,-(t3.exam_cnt_rank-t4.exam_cnt_rank)) as rank_delta from (select * from cnt_2021_2020 where start_year='2020') t3 left join (select * from cnt_2021_2020 where start_year='2021') t4 on t3.tag=t4.tag where t4.exam_cnt>=1 order by growth_rate desc,exam_cnt_rank_21 desc
SET sql_mode = 'NO_UNSIGNED_SUBTRACTION'; select * ,exam_cnt_rank_21-exam_cnt_rank_20 as rank_delta from( select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate, rank()over(order by exam_cnt_20 desc) as exam_cnt_rank_20, rank()over(order by exam_cnt_21 desc) as exam_cnt_rank_21 from( select i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20, count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21 from exam_record r left join examination_info i on r.exam_id=i.exam_id group by i.tag ) a )b where exam_cnt_20*exam_cnt_21>0 order by growth_rate desc,exam_cnt_rank_21 desc ;
select *,cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta from( select *,concat(round((exam_cnt_21-exam_cnt_20)*100/exam_cnt_20,1),'%') as growth_rate, rank()over(order by exam_cnt_20 desc) as exam_cnt_rank_20, rank()over(order by exam_cnt_21 desc) as exam_cnt_rank_21 from( select i.tag,count(if(year(submit_time)=2020 and month(submit_time)<=6,r.id,null)) as exam_cnt_20, count(if(year(submit_time)=2021 and month(submit_time)<=6,r.id,null)) as exam_cnt_21 from exam_record r left join examination_info i on r.exam_id=i.exam_id group by i.tag ) a )b where exam_cnt_20*exam_cnt_21>0 order by growth_rate desc,exam_cnt_rank_21 desc ;结构清晰,代码精简,高效执行是我写sql的三大追求,诚与诸君共勉。
【场景】:百分比格式
【分类】:专用窗口函数、select条件
难点:
长整型的数据类型要求不能有负号产生,用cast函数转换数据类型为signed。
(1)统计2020年、2021年上半年各类试卷的做完次数和做完次数排名
(2)计算同期的增长率以及做完次数排名变化,按增长率和21年排名降序输出
注意:
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函数对正数、负数都可以做处理。
最终结果
select 查询结果 [试卷类型;2020年做完次数;2021年做完次数;增长率;2020年做完次数排名;2021年做完次数排名;做完次数排名变化] from 从哪张表中查询数据[多个join连接的表] where 查询条件 [2020年和2021年做完次数不为空] group by 分组条件 [用户ID;月份] order by 对查询结果排序 [增长率、21年排名降序];
方法一
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 7 > month(start_time) and year(start_time) = 2021 and submit_time is not null group by tag ), main1 as( #2020上半年做完次数排名 select tag, start_year_20, exam_cnt_20, rank() over (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 (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 join attr1 using(tag) where exam_cnt_21*exam_cnt_20 <> 0 order by growth_rate desc, exam_cnt_rank_21 desc
代码优化
将条件 (2020年、2021年上半年) 放在 select 语句中
with main as( #2020年、2021年上半年各类试卷的做完次数和做完次数排名 select tag, count(if(date(start_time) between '20200101' and '20200630',start_time,null)) as exam_cnt_20, count(if(date(start_time) between '20210101' and '20210630',start_time,null)) as exam_cnt_21, rank() over (order by count(if(date(start_time) between '20200101' and '20200630',start_time,null)) desc) as exam_cnt_rank_20, rank() over (order by count(if(date(start_time) between '20210101' and '20210630',start_time,null)) desc) as exam_cnt_rank_21 from examination_info join exam_record using(exam_id) where submit_time is not null group by tag ) #计算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 main where exam_cnt_21*exam_cnt_20 <> 0 order by growth_rate desc, exam_cnt_rank_21 desc
main 表输出结果:
1 算法|0|3|4|1 1 2 SQL|3|2|1|2 3 C++|3|0|1|3 4 PYTHON|2|0|3|3
方法二
from 子查询
#计算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( #2020年、2021年上半年各类试卷的做完次数和做完次数排名 select tag, count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) as exam_cnt_20, count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) as exam_cnt_21, rank() over (order by count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) desc) as exam_cnt_rank_20, rank() over (order by count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) desc) as exam_cnt_rank_21 from examination_info join exam_record using(exam_id) where submit_time is not null group by tag ) main where exam_cnt_21*exam_cnt_20 <> 0 order by growth_rate desc, exam_cnt_rank_21 desc
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, (exam_cnt_rank_21 - exam_cnt_rank_20) as rank_delta from( select tag, sum(if(start_year = 2020, exam_cnt, 0)) as exam_cnt_20, sum(if(start_year = 2021, exam_cnt, 0)) as exam_cnt_21, sum(if(start_year = 2020, exam_cnt_rank, 0)) as exam_cnt_rank_20, sum(if(start_year = 2021, exam_cnt_rank, 0)) as exam_cnt_rank_21 from( SELECT tag, year(start_time) as start_year, count(submit_time) as exam_cnt, rank() over(order by count(submit_time) desc) as exam_cnt_rank from exam_record er right join examination_info ei on er.exam_id = ei.exam_id where year(start_time) = 2020 and month(start_time) <= 6 group by tag union ALL SELECT tag, year(start_time) as start_year, count(submit_time) as exam_cnt, rank() over(order by count(submit_time) desc) as exam_cnt_rank from exam_record er right join examination_info ei on er.exam_id = ei.exam_id where year(start_time) = 2021 and month(start_time) <= 6 group by tag ) as table1 group by tag ) as table2 where exam_cnt_20 != 0 and exam_cnt_21 != 0 order by growth_rate desc, exam_cnt_rank_21 desc
#1.先分步骤走,思路会非常轻松,主要思路写完, #2.然后逐渐完善细节,自测通过, #3.提交后继续扣细节最后完成 #当然如果细节能提前想到会更好,如果不能也没事 # 2021年上半年各类试卷的做完次数相比2020年上半年 # 同期的增长率(百分比格式,保留1位小数) with temp1 as( select exam_id, count(score) exam_cnt_20, rank() over(order by count(score) desc) exam_cnt_rank_20 from exam_record where year(submit_time)=2020 and month(submit_time)<7 group by exam_id ), temp2 as( select exam_id, count(score) exam_cnt_21, rank() over(order by count(score) desc) exam_cnt_rank_21 from exam_record where year(submit_time)=2021 and month(submit_time)<7 group by exam_id ) # 以及做完次数排名变化, select tag, exam_cnt_20, exam_cnt_21, concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),"%") growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) rank_delta from temp1 join temp2 on temp1.exam_id=temp2.exam_id join examination_info e on temp1.exam_id=e.exam_id order by growth_rate desc,exam_cnt_rank_21 desc # 按增长率和21年排名降序输出。
select tag ,sum(case when start_year =2020 then exam_cnt else 0 end) as exam_cnt_20 ,sum(case when start_year =2021 then exam_cnt else 0 end) as exam_cnt_21 ,concat(round((sum(case when start_year =2021 then exam_cnt else 0 end)-sum(case when start_year =2020 then exam_cnt else 0 end)) /sum(case when start_year =2020 then exam_cnt else 0 end)*100,1),'%') as growth_rate ,sum(case when start_year =2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20 ,sum(case when start_year =2021 then exam_cnt_rank else 0 end) as exam_cnt_rank_21 ,sum(case when start_year =2021 then exam_cnt_rank else 0 end) - sum(case when start_year =2020 then exam_cnt_rank else 0 end) as rank_delta from (-- 关键是这里求出完成次数级排名 要使用rank美式排名 select tag,year(start_time) as start_year, count(submit_time) as exam_cnt, rank() over(partition by year(start_time) order by count(submit_time) desc) as exam_cnt_rank from exam_record t join examination_info t1 using(exam_id) where month(start_time)<=6 group by tag,year(start_time) ) t group by tag having exam_cnt_20 >0 and exam_cnt_21>0 order by growth_rate desc,exam_cnt_rank_21 desc
先求出完成次数级排名 要使用rank美式排名
然后在这个基础之上,进行大量的case when的操作,新建列。
优点麻烦,自测的时候没错误,提交的时候rank_delta时候老是报错,参考了评论里面的大神用的cast,解决了 select a.tag,a.cnt1 exam_cnt_20,b.cnt2 exam_cnt_20,concat(round((b.cnt2 - a.cnt1)*100/a.cnt1,1),'%') growth_rate, a.t_rank1 exam_cnt_rank_20,b.t_rank2 exam_cnt_rank_21,cast(b.t_rank2 as signed) - cast(a.t_rank1 as signed) as rank_delta from (select ei.tag,count(er.submit_time) cnt1, rank() over (order by count(er.submit_time) desc) t_rank1 from exam_record er inner join examination_info ei on ei.exam_id = er.exam_id where date_format(er.submit_time,'%Y%m') between '202001' and '202006' group by ei.tag) as a inner join (select ei.tag,count(er.submit_time) cnt2, rank() over (order by count(er.submit_time) desc) t_rank2 from exam_record er inner join examination_info ei on ei.exam_id = er.exam_id where date_format(er.submit_time,'%Y%m') between '202101' and '202106' group by ei.tag) as b on a.tag = b.tag group by a.tag order by growth_rate desc,exam_cnt_rank_21 desc
with e_e as ( select er.exam_id, tag, score, start_time, submit_time from exam_record as er left join examination_info as ei on er.exam_id = ei.exam_id ) select t21.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 ( select tag , count(score) as exam_cnt_20 , rank() over(order by count(score) desc) exam_cnt_rank_20 from e_e where date_format(submit_time,'%Y%m') between '202001' and '202006' and submit_time is not null group by tag) as t20 inner join ( select tag , count(score) as exam_cnt_21 , rank() over(order by count(score) desc) exam_cnt_rank_21 from e_e where date_format(submit_time,'%Y%m') between '202101' and '202106' and submit_time is not null group by tag) as t21 on t20.tag = t21.tag order by growth_rate desc, exam_cnt_21 asc;
SELECT t1.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, COUNT(submit_time) AS exam_cnt_20, RANK() OVER(ORDER BY COUNT(submit_time) DESC) AS exam_cnt_rank_20 FROM exam_record AS er RIGHT JOIN examination_info AS ei ON er.exam_id = ei.exam_id WHERE YEAR(submit_time) = 2020 AND MONTH(submit_time) < 7 GROUP BY tag) AS t1 JOIN (SELECT tag, COUNT(submit_time) AS exam_cnt_21, RANK() OVER(ORDER BY COUNT(submit_time) DESC) AS exam_cnt_rank_21 FROM exam_record AS er RIGHT JOIN examination_info AS ei ON er.exam_id = ei.exam_id WHERE YEAR(submit_time) = 2021 AND MONTH(submit_time) < 7 GROUP BY tag) AS t2 ON t1.tag = t2.tag ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC
SELECT *, cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) as rank_delta FROM (SELECT a.tag, COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)) AS exam_cnt_20, COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL)) AS exam_cnt_21, CONCAT(ROUND((COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL))-COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)))/COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL))*100,1),'%') AS growth_rate, RANK() OVER (ORDER BY COUNT(IF(submit_time BETWEEN '2020-01-01' AND '2020-06-30',submit_time,NULL)) DESC) AS exam_cnt_rank_20, RANK() OVER (ORDER BY COUNT(IF(submit_time BETWEEN '2021-01-01' AND '2021-06-30',submit_time,NULL)) DESC) AS exam_cnt_rank_21 FROM examination_info a INNER JOIN exam_record b ON a.exam_id=b.exam_id GROUP BY a.tag) t1 WHERE exam_cnt_20*exam_cnt_21>0 ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC
select tag,exam_cnt_20,exam_cnt_21, concat(round((exam_cnt_21 - exam_cnt_20)*100/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 ( select * from (select tag,year(start_time) as 2020_years,count(submit_time) as exam_cnt_20,rank() over(partition by year(start_time) 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 month(start_time) <=6 group by tag,year(start_time) having count(submit_time) != 0 ) a #将2020年上半年需要的数据提取出来 join (select tag,year(start_time) as 2021_years,count(submit_time) as exam_cnt_21,rank() over(partition by year(start_time) 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 month(start_time) <=6 group by tag,year(start_time) having count(submit_time) != 0 ) b #将2021年上半年需要的数据提取出来 using(tag) ) c #内连接,将两个时期都有的记录提取出来 ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC #按要求计算数值并排序 #期待大家指出不足之处
select tag, exam_cnt_20, exam_cnt_21, concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate, exam_cnt_rank20, exam_cnt_rank21, (cast(exam_cnt_rank21 as signed)-cast( exam_cnt_rank20 as signed)) rank_delta from (select tag, sum(if( start_year=2020 , exam_cnt ,0)) exam_cnt_20, sum(if(start_year=2021,exam_cnt,0)) exam_cnt_21, sum(if(start_year=2020,exam_cnt_rank,0)) exam_cnt_rank20, sum(if(start_year=2021,exam_cnt_rank,0)) exam_cnt_rank21 from (select tag,year(submit_time) start_year ,count(*) exam_cnt , rank() over(partition by year(submit_time) order by count(*) desc) exam_cnt_rank from exam_record e_r join examination_info e_i using(exam_id) where submit_time is not null and year(submit_time) BETWEEN 2020 and 2021 and month(submit_time)<=6 group by tag,year(submit_time))mm group by tag )nn where exam_cnt_21 <>0 and exam_cnt_20 <>0 order by growth_rate desc, exam_cnt_rank21 desc
with t1 as ( select tag, count(if(year(submit_time) = 2020 and month(submit_time) <= 6,1,null)) exam_cnt_20, count(if(year(submit_time) = 2021 and month(submit_time) <= 6,1,null)) exam_cnt_21 from exam_record left join examination_info using(exam_id) group by tag ), t2 as ( select tag,exam_cnt_20,exam_cnt_21, rank() over(order by exam_cnt_20 desc) exam_cnt_rank_20, rank() over(order by exam_cnt_21 desc) exam_cnt_rank_21 from t1 ) select tag,exam_cnt_20,exam_cnt_21, growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,rank_delta from ( 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 t2 ) t3 where exam_cnt_20 > 0 and exam_cnt_21 > 0 order by growth_rate desc, exam_cnt_rank_21 desc
with t1 as( select tag, count(if(year(submit_time)=2020,1,null)) exam_cnt_20, count(if(year(submit_time)=2021,1,null)) exam_cnt_21 from examination_info ei left join exam_record er on ei.exam_id=er.exam_id and year(er.start_time) in(2020,2021) and (month(er.start_time) between 1 and 6) group by tag ), t2 as( select *, concat(round((exam_cnt_21/exam_cnt_20-1)*100,1),'%') growth_rate, rank()over(order by exam_cnt_20 desc) exam_cnt_rank_20, rank()over(order by exam_cnt_21 desc) exam_cnt_rank_21 from t1 ) select tag,exam_cnt_20,exam_cnt_21,growth_rate,exam_cnt_rank_20,exam_cnt_rank_21, cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) rank_delta from t2 where exam_cnt_21<>0 and growth_rate is not null order by growth_rate desc,exam_cnt_rank_21 desc
select tag,e20 exam_cnt_20,e21 exam_cnt_21 ,concat(round((e21/e20-1)*100,1),'%') growth_rate ,rk20 exam_cnt_rank_20,rk21 exam_cnt_rank_21 ,cast(rk21 as signed)-cast(rk20 as signed) rank_delta from(select * ,rank()over(order by e20 desc) rk20 ,rank()over(order by e21 desc) rk21 from(select tag ,count(if(month(submit_time)<7 and year(submit_time)=2020,1,null)) e20 ,count(if(month(submit_time)<7 and year(submit_time)=2021,1,null)) e21 from exam_record join examination_info using(exam_id) group by 1) a) b where e20*e21 != 0 order by 4 desc,6 desc;优雅!
with t as ( select tag, count(if(year(submit_time) = 2020 and month(submit_time) <= 6,submit_time,null)) exam_cnt_20, count(if(year(submit_time) = 2021 and month(submit_time) <= 6,submit_time,null)) exam_cnt_21, rank() over(order by count(if(year(submit_time) = 2020 and month(submit_time) <= 6,submit_time,null)) desc) exam_cnt_rank_20, rank() over(order by count(if(year(submit_time) = 2021 and month(submit_time) <= 6,submit_time,null)) desc) exam_cnt_rank_21 from examination_info ei join exam_record er on ei.exam_id = er.exam_id group by tag ) select tag, exam_cnt_20, exam_cnt_21, concat(round((exam_cnt_21 - exam_cnt_20) * 100 / exam_cnt_20,1),'%') growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, cast(exam_cnt_rank_21 as signed) - cast(exam_cnt_rank_20 as signed) rank_delta from t where exam_cnt_20 != 0 and exam_cnt_21 != 0 order by growth_rate desc,exam_cnt_rank_21 desc;
#计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率(百分比格式,保留1位小数),以及做完次数排名变化,按增长率和21年排名降序输出 select tag, exam_cnt_20, exam_cnt_21, concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, cast(exam_cnt_rank_21 as signed )-cast(exam_cnt_rank_20 as signed) rank_deIta from ( select tag, count(if(date_format(start_time,'%Y%m%d') between '20200101' and '20200630',start_time,null)) exam_cnt_20, count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) exam_cnt_21, rank() over(order by count(if(date_format(start_time,'%Y%md') between '20200101' and '20200630',start_time,null)) desc) exam_cnt_rank_20, rank() over(order by count(if(substring(start_time,1,10) between '2021-01-01' and '2021-06-30',start_time,null)) desc) exam_cnt_rank_21 from exam_record join examination_info using(exam_id) where score is not null group by tag ) t where exam_cnt_20*exam_cnt_21 <>0 order by 4 desc,6 desc
select tag, exam_cnt_20, exam_cnt_21, concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) rank_delta from (select tag, count(if(year(start_time)='2020'and month(start_time) between 1 and 6,score,null)) exam_cnt_20, count(if(year(start_time)='2021'and month(start_time)<7,score,null)) exam_cnt_21, rank()over(order by (count(if(year(start_time)='2020' and month(start_time)<7,score,null))) desc) exam_cnt_rank_20, rank()over(order by (count(if(year(start_time)='2021' and month(start_time)<7,score,null))) desc) exam_cnt_rank_21 from examination_info join exam_record using(exam_id) where score is not null group by 1) t where exam_cnt_20* exam_cnt_21!=0 order by 4 desc,6 desc