一、知识点总结与拓展

1）对同一列数据进行加减操作

  • 情况1可排序时：先排序并错位LEAD（）OVER（）
  • 直接根据条件进行加减筛选SUM(IF(真,1,0))或者 SUM(CASE WHEN ... THEN...END)，并使用聚合函数GROUP BY。
  • 看到有别的小伙伴是这样直接计算的，做个记录： 
SELECT #第一步：找到所有tag在2020，2021上半年的完成数
    tag,
    SUM(IF(DATE_FORMAT(submit_time, '%Y-%m') BETWEEN '2020-01' AND '2020-06', 1, 0)) exam_cnt_20,
    SUM(IF(DATE_FORMAT(submit_time, '%Y-%m') BETWEEN '2021-01' AND '2021-06', 1, 0)) exam_cnt_21
  FROM exam_record
  LEFT JOIN examination_info ei USING(exam_id)
  GROUP BY 1)
2）百分比格式表示
CONCAT(字段名,'%')

3）数据格式转换
  • CAST(字段名 AS 格式类型 )
  • SQL常用的格式类型
    • 二进制：BINARY    
    • 字符型: CHAR
    • 日期 : DATE     
    • 时间: TIME     
    • 日期时间型 : DATETIME     
    • 浮点数 : DECIMAL      
    • 整数 : SIGNED     
    • 无符号整数 : UNSIGNED

二、题目解读与解题步骤拆分

1、题目解读

求：请计算2021年上半年各类试卷的做完次数相比2020年上半年同期的增长率（百分比格式，保留1位小数），以及做完次数排名变化，按增长率和21年排名降序输出。

坑1：增长率的计算：（2021做完次数-2020做完次数）/2020做完次数

坑2：做完次数排名变化：2021排名-2020排名

坑3：排名方式:根据解释中2020年的排名1 1 3 可知，选择rank( )over( )函数处理排名

需求字段：

  • tag     试卷名
  • exam_cnt_20 2020年上半年做完次数
  • exam_cnt_21 2021年上半年做完次数    
  • growth_rate     增长率    
  • exam_cnt_rank_20 2020年上半年做完次数排名
  • exam_cnt_rank_21 2021年上半年做完次数排名
  • rank_delta     排名变化（eg.后退1名）

2、解题步骤拆分

1）请计算2021年上半年
  • 2020和2021上半年的数据范围圈定

2）各类试卷的做完次数
  • 按照年份和类别，对各类试卷上半年的完成情况进行聚类：COUNT(score) AS exam_cnt_20/21

 

3）相比2020年上半年同期的增长率（百分比格式，保留1位小数）

  • 增长率=（2021上半年你做完次数-2020上半年做完次数）/2020上半年做完次数
  • (exam_cnt_21 - exam_cnt_20)/exam_cnt_20 AS growth_rate    
  • 小数点保留1位小数ROUND(growth_rate,1)

 

4）以及做完次数排名变化

  • 对次数进行排名:RANK( )OVER( ) AS exam_cnt_rank_20/21
  • 排名变化：exam_cnt_rank_21 - exam_cnt_rank_20

 

5）按增长率和21年排名降序输出。

 

三、步骤代码

1）2020和2021上半年的数据范围圈定

  • MONTH(submit_time) between 1 AND 6 -- 上半年数据
  • submit_time BETWEEN '2020-01-00 00:00:00' AND '2022-01-01 00:00:00' -- 选取2020和2021年的数据
2）按照年份和类别，对各类试卷上半年的完成情况进行聚类，并进行排名
  • RANK()OVER(PARTITION BY YEAR(submit_time) ORDER BY COUNT(score) DESC -- 分别对2021和2020年的做题情况进行排名
  • YEAR(submit_time)  、 COUNT(score)这俩是聚合函数数据，所以要配合 GROUP BY 使用
     
SELECT exam_id,YEAR(submit_time) start_year ,COUNT(score) exam_cnt,RANK()OVER(PARTITION BY YEAR(submit_time) ORDER BY COUNT(score) DESC) rk 
/*分别对2021和2020的做完情况进行排名*/
FROM exam_record
WHERE MONTH(submit_time) BETWEEN 1 AND 6 -- 选取下半年数据
AND submit_time BETWEEN '2020-01-00 00:00:00' AND '2022-01-01 00:00:00' -- 选取2020和2021年的数据
GROUP BY YEAR(submit_time),exam_id /*对年份和类别进行聚类*/
;

3）增长率=（2021上半年你做完次数-2020上半年做完次数）/2020上半年做完次数

 (exam_cnt_21 - exam_cnt_20)/exam_cnt_20  AS growth_rate    
  • 将2020年和2021年的完成次数和排名数据分开 
SELECT exam_id,IF(start_year='2020',exam_cnt,NULL)exam_cnt_20,-- 2020年的完成次数
	LEAD(exam_cnt,1)OVER(PARTITION BY exam_id ORDER BY start_year) exam_cnt_21,-- 2021年的完成次数
	IF(start_year='2020',rk,NULL)exam_cnt_rank_20,-- 2020年的排名
    LEAD(rk,1)OVER(PARTITION BY exam_id ORDER BY start_year) exam_cnt_rank_21 -- 2021年的排名
FROM (
	SELECT exam_id,YEAR(submit_time) start_year ,COUNT(score) exam_cnt,RANK()OVER(PARTITION BY YEAR(submit_time) ORDER BY COUNT(score) DESC) rk 
	/*分别对2021和2020的做完情况进行排名*/
	FROM exam_record
	WHERE MONTH(submit_time) BETWEEN 1 AND 6 -- 选取下半年数据
	AND submit_time BETWEEN '2020-01-00 00:00:00' AND '2022-01-01 00:00:00' -- 选取2020和2021年的数据
	GROUP BY YEAR(submit_time),exam_id /*对年份和类别进行聚类*/
	)t1;

  • 小数点保留1位小数ROUND(growth_rate,1)=ROUND((exam_cnt_21 - exam_cnt_20)/exam_cnt_20,1)
  • 改成百分比格式：CONCAT(ROUND((exam_cnt_21 - exam_cnt_20)/exam_cnt_20*100,1),'%') AS growth_rate
5）排名变化
  • exam_cnt_rank_21 - exam_cnt_rank_20
⚠️⚠️⚠️直接相减系统报错，对exam_cnt_rank_21 和 exam_cnt_rank_20的格式进行强行更改为整数。
  • CAST(exam_cnt_rank_21 AS SIGNED ) - CAST(exam_cnt_rank_20 AS SIGNED )  AS rank_delta

7）按增长率和21年排名降序输出。


  • ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC


四、完整代码 

WITH t2 AS(
SELECT exam_id,IF(start_year='2020',exam_cnt,NULL)exam_cnt_20,-- 2020年的完成次数
	LEAD(exam_cnt,1)OVER(PARTITION BY exam_id ORDER BY start_year) exam_cnt_21,-- 2021年的完成次数
	IF(start_year='2020',rk,NULL)exam_cnt_rank_20,-- 2020年的排名
    LEAD(rk,1)OVER(PARTITION BY exam_id ORDER BY start_year) exam_cnt_rank_21 -- 2021年的排名
FROM (
	SELECT exam_id,YEAR(submit_time) start_year ,COUNT(score) exam_cnt,RANK()OVER(PARTITION BY YEAR(submit_time) ORDER BY COUNT(score) DESC) rk 
	/*分别对2021和2020的做完情况进行排名*/
	FROM exam_record
	WHERE MONTH(submit_time) BETWEEN 1 AND 6 -- 选取下半年数据
	AND submit_time BETWEEN '2020-01-00 00:00:00' AND '2022-01-01 00:00:00' -- 选取2020和2021年的数据
	GROUP BY YEAR(submit_time),exam_id /*对年份和类别进行聚类*/
	)t1
    
)
SELECT a.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 t2

LEFT JOIN examination_info AS a ON a.exam_id=t2.exam_id

WHERE exam_cnt_21 IS NOT NULL
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC
;