题解 | #试卷完成数同比2020年的增长率及排名变化#
试卷完成数同比2020年的增长率及排名变化
https://www.nowcoder.com/practice/13415dff75784a57bedb6d195262be7b
- 题目:请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。
-
计算出每年上半年的各类试卷的做完次数,做完次数排名.month(submit_time) <= 6,排名
rank() over(partition by year(submit_time)order bycount(submit_time) desc) exam_cnt_rank注意按照年份分组排名,因为是要每年上半年不同tag之间的比较排名
- 自连接,这样可以不仅仅求2020,2021,每年的都统一求出来
- concat函数应用求百分比,记得×100
- cast函数应用 BIGINT UNSIGNED value is out of range mysql,这个值超过了字段设计的范围,是因为它超过了UNSIGNED的范围，因为UNSIGNED不存在负数，所以报错。
select
t.tag,
t.exam_cnt,
t1.exam_cnt,
concat(
round((t1.exam_cnt- t.exam_cnt)/ t.exam_cnt*100, 1),
'%'
) growth_rate,
t.exam_cnt_rank exam_cnt_rank_20,
t1.exam_cnt_rank exam_cnt_rank_21,
cast(t1.exam_cnt_rank as signed) - cast(t.exam_cnt_rank as signed)
# if(t1.exam_cnt_rank >= t.exam_cnt_rank,(t1.exam_cnt_rank - t.exam_cnt_rank), -(t1.exam_cnt_rank - t.exam_cnt_rank)) rank_delta -- 这种方式不能得到负数
from
(
select
tag,
year(submit_time) start_year,
count(submit_time) exam_cnt,
rank() over(
partition by year(submit_time)
order by
count(submit_time) desc
) exam_cnt_rank
from
exam_record
left join examination_info using(exam_id)
where
month(submit_time) <= 6 and submit_time is not null
group by
tag,
year(submit_time)
) t
left join (
select
tag,
year(submit_time) start_year,
count(submit_time) exam_cnt,
rank() over(
partition by year(submit_time)
order by
count(submit_time) desc
) exam_cnt_rank
from
exam_record
left join examination_info using(exam_id)
where
month(submit_time) <= 6 and submit_time is not null
group by
tag,
year(submit_time)
) t1 on t.tag = t1.tag
and t.start_year + 1 = t1.start_year
where
t1.tag is not null
order by growth_rate desc,exam_cnt_rank_21 desc
百威成长空间 52人发布
