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

题解

注意点

1. 上半年:1月到6月 所以使用month()函数,对月份进行过滤
2. 使用百分号输出增长率 concat函数+round函数

WITH t0 AS (
  -- t0形成宽表
        SELECT uid,
           tag,
           start_time,
           year(start_time) year,
           submit_time
    FROM exam_record er
    JOIN examination_info ei
        ON er.exam_id = ei.exam_id
        AND month(start_time) BETWEEN 1 AND 6

),
     t1 AS (
       -- 筛选出2021年的数据,进行分组聚合
         SELECT tag,
                count(submit_time) exam_cnt_21
         FROM t0
         WHERE year(start_time)=2021
         GROUP BY tag
         HAVING exam_cnt_21 != 0
     ),
     t2 AS (
       -- 对2021年分组聚合后的数据进行排序
         SELECT *,
                rank() OVER (ORDER BY exam_cnt_21 DESC ) exam_cnt_rank_21
         FROM t1
     ),
     -- t3 到 t4 重复上面的动作
     t3 AS (
         SELECT tag,
                count(submit_time) exam_cnt_20
         FROM t0
         WHERE year=2020
         GROUP BY tag
         HAVING exam_cnt_20 !=0
     ),
     t4 AS (
         SELECT *,
                rank()  OVER (ORDER BY exam_cnt_20 DESC ) exam_cnt_rank_20
         FROM t3
     ),
     t5 AS (
       -- t2 与 t4 关联到一起,以便后续的计算
         SELECT t2.tag,
                exam_cnt_20,
                exam_cnt_21,
                exam_cnt_rank_20,
                exam_cnt_rank_21
         FROM t2 JOIN t4
         ON t2.tag=t4.tag
     )
     -- 计算指标
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 t5
ORDER BY growth_rate DESC ,exam_cnt_rank_21 DESC ;