SQL优化 - 同比计算

记录一次SQL优化, 在计算同比的时候. 就太久没有写语句了, 能力在逐渐下滑, 思维也是, 感觉还是有点可怕的. 自从转业务以来, 就基本没有碰过代码这方面了. 甚至连 SQL 都开始要搜索了. 而我日常更多是让专业的开发来做, 我负责跟进度, 也不知道我这样存在的意义是否有价值. 正好又在弄BI, 然后呢根据过往经验, 处理逻辑还是尽量在后台处理好, 前台就直接展示即可.

有这样一个基础的算同比的需求, 分为两步. 一个是要对某字段进行合并, 然后基于合并再进行汇总和计算同比. 我的 1.0 版本是这样的.

1.0 临时表 + 2表连接

用的是 Sql Server

WITH A AS (
	SELECT 
	  CASE
	  WHEN AGENT IN ('AA', 'AAA')       THEN 'AA'
	  WHEN AGENT IN ('BB', 'BBB')       THEN 'BB'
	  WHEN AGENT IN ('CC', 'CCC')       THEN 'CCC'
	  ELSE AGENT END AS 代理
	  , 品类
	  , 销售时间
	  , 数量 
	FROM AAAAA
	WHERE 是否电商 = 1
	  AND 品类 IN ('A品类', 'B品类', 'C品类')
 ) 
 -- MAIN
-- 当月销量
SELECT 
  B.*
  , C.当月销量 AS 同期19月销
FROM (
	SELECT 
	  A.代理
	  , A.品类
	  , SUM(A.数量)     AS 当月销量
	FROM A
	WHERE 销售时间  >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()), 1)
	   AND 销售时间 <= GETDATE()
	GROUP BY 代理, 品类
) AS B 
-- 19年同期销量
LEFT JOIN (
	SELECT 
	  代理
	  , 品类
	  , SUM(数量)     AS 当月销量
	FROM A
	WHERE 销售时间  >= DATEFROMPARTS(2019, MONTH(GETDATE()), 1)
	   AND 销售时间 <= DATEFROMPARTS(2019, MONTH(GETDATE()), DAY(GETDATE())-1)
	GROUP BY 代理, 品类
) AS C
  ON B.代理 = C.代理 AND B.品类 = C.品类
  
ORDER BY B.当月销量 DESC 
  

问题点

• 为了处理一个字段, 建了个临时查询, 相等于遍历了一整个表
• 算同比, 用到了 2个表 (当期, 同期) 进行拼接 (各自还执行了一次 group by )
• 代码冗余, 结构不清晰

对于计算同比这块, 其实结构是完全一样的, 只是 时间 不同而已, 那判断时间就可以了, 完全没必要用 Join 搞两张表.

2.0 用 Case when 时间 代替表 Join 算同比

WITH A AS (
	SELECT 
	  CASE
	  WHEN AGENT IN ('AA', 'AAA')       THEN 'AA'
	  WHEN AGENT IN ('BB', 'BBB')       THEN 'BB'
	  WHEN AGENT IN ('CC', 'CCC')       THEN 'CCC'
	  ELSE AGENT END AS 代理
	  , 品类
	  , 销售时间
	  , 数量 
	FROM AAAAA
	WHERE 是否电商 = 1
	  AND 品类 IN ('A品类', 'B品类', 'C品类')
 ) 
 
 -- MAIN 
 SELECT 
   代理
   , 品类
   , SUM(CASE WHEN 
         销售时间 BETWEEN DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()), 1) AND GETDATE()
         THEN 数量 ELSE 0 END) AS 当月销量
   , SUM(CASE WHEN 
         销售时间 BETWEEN DATEFROMPARTS(2019, MONTH(GETDATE()), 1) AND DATEFROMPARTS(2019, MONTH(GETDATE()), DAY(GETDATE())-1)
         THEN 数量 ELSE 0 END) AS 同期19年月销量
 FROM A
 GROUP BY 代理, 品类
 ORDER BY 当月销量 DESC 
 

问题点

• 是减少了查询, 极大优化了, 但 临时表 也是不必要的, 可以一步到位.
• 美中不足, 尚可再优化

3.0 将 Case when 结合 Group By 一步到位

sql server 很烦的一点是, 不能同级引用...

 SELECT 
	  CASE
	  WHEN AGENT IN ('AA', 'AAA')       THEN 'AA'
	  WHEN AGENT IN ('BB', 'BBB')       THEN 'BB'
	  WHEN AGENT IN ('CC', 'CCC')       THEN 'CCC'
	  ELSE AGENT END AS 代理
   , 品类
   , SUM(CASE WHEN 
         销售时间 BETWEEN DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()), 1) AND GETDATE()
         THEN 数量 ELSE 0 END) AS 当月销量
   , SUM(CASE WHEN 
         销售时间 BETWEEN DATEFROMPARTS(2019, MONTH(GETDATE()), 1) AND DATEFROMPARTS(2019, MONTH(GETDATE()), DAY(GETDATE())-1)
         THEN 数量 ELSE 0 END) AS 同期19年月销量
 FROM AAAAA
 WHERE 是否电商 = 1
   AND 品类 IN ('A品类', 'B品类', 'C品类')
 GROUP BY 品类, CASE
	  WHEN AGENT IN ('AA', 'AAA')       THEN 'AA'
	  WHEN AGENT IN ('BB', 'BBB')       THEN 'BB'
	  WHEN AGENT IN ('CC', 'CCC')       THEN 'CCC'
	  ELSE AGENT END 
 ORDER BY 当月销量 DESC 
 

抽空还是要多练练, 这个很容易就忘掉了.