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

# V1
# # 2021年上半年各类试卷的做完次数  以及 同比
# # 以及做完次数排名变化，按照增长率📈  和  21年排名降序输出
# # 字段要求：tag\20年做完次数\21年做完次数\同比\20排名\21排名\排名增长数

# # 表一：链接
# with table1 as (
#     select tag,uid,exam_id,submit_time
#     from exam_record left join examination_info using(exam_id)
#     where submit_time is not null 
#     And year(submit_time) between 2020 and 2021
#     AND month(submit_time) between 1 and 6
# ),
# # 表二：按照tag、year进行分类计算
# # 注意点：groupby按照tag、year分组，partition 按照year分组
# # 注意点：排名允许并列且跳过，用rank
# table2 as(
#     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 table1
#     group by tag,year(submit_time)
# ),
# # 表三： 感觉要用lead函数？
# table3 as(
#     select tag,
#     (case when start_year=2020 then exam_cnt else 0 end) as exam_cnt_20,
#     lead(exam_cnt,1)over(partition by tag order by start_year) as exam_cnt_21,
#     concat(round((lead(exam_cnt,1)over(partition by tag order by start_year)-(case when start_year=2020 then exam_cnt else 0 end))/(case when start_year=2020 then exam_cnt else 0 end)*100,1),"%") as growth_rate,	


#     (case when start_year=2020 then exam_cnt_rank else 0 end) as exam_cnt_rank_20,
#     lead(exam_cnt_rank,1)over(partition by tag order by start_year) as exam_cnt_rank_21,
#     (lead(exam_cnt_rank,1)over(partition by tag order by start_year)-(case when start_year=2020 then exam_cnt_rank else 0 end)) as rank_delta
#     from table2
# )

# select *
# from table3
# where growth_rate is not null
# order by exam_cnt_20 asc


# V2 
# 时间：2021上半年vs2020上半年
# 字段：tag、20做完次数、21做完次数、同比、20做完次数排名、21做完次数排名、排名变化

# tb1：过程表，筛选时间，并且生成时间字段
with tb1 as(
    select tag,start_time,submit_time,score,year(submit_time) as year
    from exam_record left join examination_info using(exam_id)
    where score is not null 
    # AND month(submit_time) <= 6 AND year(submit_time) = 2020 or year(submit_time) = 2021 AND month(submit_time) >=1
    # 注意点： 时间筛选的时候的逻辑表达式需要商榷 用bewteen and 会好点
    AND month(submit_time) between 1 and 6
    AND year(submit_time) between 2020 and 2021
),
# tb2：维度：tag、year. 指标：计数score，排序rank
tb2 as(
select tag,year,count(score) as cnt,
# 按照year分组、cnt排序,使用的是rank（跳过）
rank() over(partition by year order by count(score) desc) as rk
from tb1
group by tag,year
),

# tb3: 拆分字段20/21，过程表
tb3 as(
    select tag,
    case when year = 2020 then cnt else 0 end as exam_cnt_20,
    # 运用lead函数 将下一期的数据搬到一列新字段
    lead(cnt,1)over(partition by tag order by year) as exam_cnt_21,
    # case when year = 2021 then cnt else 0 end as exam_cnt_21,
    case when year = 2020 then rk else 0 end as exam_cnt_rank_20,
    # 运用lead函数
    lead(rk,1)over(partition by tag order by year) as exam_cnt_rank_21
    from tb2
)
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 tb3
where exam_cnt_20 != 0 
AND exam_cnt_21 !=0 
order by growth_rate desc,exam_cnt_rank_21 desc

# select tag,start_time,submit_time,score,year(submit_time) as year
#     from exam_record left join examination_info using(exam_id)
#     where score is not null 
#     # AND month(submit_time) <= 6 AND year(submit_time) = 2020 or year(submit_time) = 2021 AND month(submit_time) >=1
#     AND month(submit_time) between 1 and 6
#     AND year(submit_time) between 2020 and 2021