题解 | #试卷完成数同比2020年的增长率及排名变化#
试卷完成数同比2020年的增长率及排名变化
http://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
思路一:
分析:
既然最后只需要得到2020和2021年都有作答记录的tag,
不妨分别筛选出来2020年和2021年的数据再用join连接.
2020、2021各自的作答情况去对应的表中取即可
实现:
select q1.tag,
q1.exam_cnt as exam_cnt_20,
q2.exam_cnt as exam_cnt_21,
concat(round((q2.exam_cnt-q1.exam_cnt)*100/q1.exam_cnt,1),'%')
as growth_rate,
q1.exam_cnt_rank as exam_cnt_rank_20,
q2.exam_cnt_rank as exam_cnt_rank_21,
cast(q2.exam_cnt_rank as signed)-cast(q1.exam_cnt_rank as signed) --易错点1
as rank_delta
from
(select tag,'2020' as start_year,
count(submit_time) as exam_cnt,
rank() over (order by count(submit_time) desc) as exam_cnt_rank --易错点2
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where start_time between '2020-01-01 00:00:00'
and '2020-06-30 23:59:59'
group by tag) as q1
join
(select tag,'2021' as start_year,
count(submit_time) as exam_cnt,
rank() over (order by count(submit_time) desc) as exam_cnt_rank --易错点2
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where start_time between '2021-01-01 00:00:00'
and '2021-06-30 23:59:59'
group by tag) as q2
on q1.tag=q2.tag
where q1.exam_cnt!=0 and q2.exam_cnt!=0 --易错点3
order by growth_rate desc,exam_cnt_rank_21 desc
易错点:
- 计算名次变化时q2.exam_cnt_rank-q1.exam_cnt_rank as rank_delta会得到负数,报错:“BIGINT UNSIGNED值超出范围”。解决办法——cast(q2.exam_cnt_rank as signed)-cast(q1.exam_cnt_rank as signed) as rank_delta
- 分别筛选并排序时不应有partition by 部分,尤其不应partition by tag,不然每个tag得到的名次都为1
- 为确保所得到的tag中有完成作答记录,应避免取到exam_cnt=0的情况,故最后应用where语句加以筛选——where q1.exam_cnt!=0 and q2.exam_cnt!=0
思路二:
分析:
- 按照题目解释得到对应表格——rank()over(partition by start_year)
- 取2020、2021年数据时要借助if分别得到
- tag需要同时具有2020、2021作答记录——count(tag)=2
实现:
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,
concat(round(sum(if(start_year=2021,exam_cnt,-exam_cnt))/sum(
if(start_year=2020,exam_cnt,0))*100,1),'%') as growth_rate,
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,
sum(if(start_year=2020,-exam_cnt_rank,exam_cnt_rank)) as rank_delta
from
(select tag,year(submit_time) as start_year,
count(submit_time) as exam_cnt,
rank()over (partition by year(submit_time) order by
count(submit_time) desc) as exam_cnt_rank
from exam_record as er
join examination_info as ei
on er.exam_id=ei.exam_id
where (year(submit_time)=2020 or year(submit_time)=2021)
and month(submit_time)<=6
group by tag,start_year) as q
group by tag
having count(tag)=2
order by growth_rate desc,exam_cnt_rank_21 desc