SQL | CASE WHEN 实战 -- 转置财报
SQL | CASE WHEN 实战 -- 转置财报
前言
常见的面试题中包含的知识点，也是平时练手的经典题，把知识点串起来的同时也很好的联系了业务实际。直接将代码背诵记忆同样可在相似场景中发挥作用。
知识储备
- 年月销量数据表 sales
- SQL 编辑器(笔者 dbForge Studio)
- SQL 基本知识(SELECT, GROUP BY, AS, 聚合函数SUM)
业务背景
转置财务报表，将源数据以更简洁明了的形式呈现给同事、上司，合作伙伴等，以促进后续的数据分析工作。
年份分组，对每一年的各月份的零散销量进行汇总统计,行转置成列，打横输出，列名刚好对应月份，十分简洁明了。
流程分析
Step1：思路探索
需求的实现不一定都是一蹴而就的，先简单模拟一下低端版本的效果可以为最终实现打开思路。
SELECT
year_
, month_
/* 这种格式写代码比较整洁
1. 可以直接注释掉一行而不影响其他行
2. 可在一行末尾添加注释而不影响其他行
3. 可提醒自己不要漏掉括号*/
, SUM(amount) AS total_amount
FROM
sales
GROUP BY year_, month_
;代码阅读顺序拆解：从(FROM) sales 表中读入数据 --> 按照年月分组(每一年有六个月，1:6 的关系) --> 将分组后的内容呈现(SELECT) 出来，形式为 年(year_)，月(month_)，每个月(month_) 对应的总销量(SUM amount) 的别名(total_amount)，
面试时聊到代码的阅读顺序的话一上来就说先从 SELECT 开始读的话会显得比较没有经验，毕竟 SELECT 只是最后的呈现形式，效果图如下：
有点味道了，只不过还暂时是打竖的，显得比较冗余(相同的年份和月份都重复出现多次)
Step2：引入 CASE WHEN
SQL 中每一种函数的妙用都有很多，这里根据业务需求，有一说一，只分享 CASE WHEN 的冰山一角。SQL 的 CASE WHEN 与编程语言中的 if-else 结构非常相似，而该函数又分为 ‘ 简单 CASE ’ 与 ‘ 搜索 CASE ’ 两种，‘ 搜索 CASE‘ 功能更强大(其实也已经包括了简单 CASE 的内容)，通常直接用 ’ 搜索 CASE ‘ 就可以了。
两种 CASE 的格式如下：
搜索表达式之所以叫 “ 搜索 ”，是因为搜索 CASE 后面的表达式可以完全不同，而简单表达式是 WHEN 后面的表达式可以不同，但 CASE 后面的表达式都是一样的。记住最后都要 END 结尾，格式也建议稍微注意一下，毕竟一大块（所以建议只记搜索表达式）
SELECT
*
-- 简单 CASE
, CASE new_table.total_amount -- 只能针对一个条件进行
WHEN 1000 THEN '刚好 1000' -- 无法设置区间
WHEN 2000 THEN '刚好 2000'
ELSE '看着办'
END AS '等级'
-- ============================
-- 方便得多的搜索 CASE
, CASE WHEN new_table.total_amount BETWEEN 300 AND 500 THEN '达标'
WHEN new_table.total_amount BETWEEN 501 AND 600 THEN '销量不错'
WHEN new_table.total_amount > 600 THEN '异常高'
-- 搜索CASE 还可以同时加上多种类型的判断条件，即跨列
-- 这里垮了 total_amount 和 month 列，简单 CASE 无法实现
WHEN new_table.month_ = 1 THEN '新年伊始高低无所谓'
ELSE '未达标' -- 销量小于 300 的都算作 ‘未达标’
END AS '销量等级'
FROM
-- 子查询开始
(
SELECT
year_
, month_
, SUM(amount) AS total_amount
FROM
sales
GROUP BY year_, month_
) AS new_table
-- 子查询结束(养成良好的标记子查询开始与结束的习惯)
;
不要看 CASE WHEN 的语句那么长，其实也就生成一列而已，长是因为生成的列中每一行都需要根据需求定制。
Step3：正式组合
先看首列，只是显示两个年份，对 year_ 使用 ' 搜索 CASE '
SELECT
CASE WHEN year_ = 2018 THEN 2018
WHEN year_ = 2019 THEN 2019
END AS Year_
FROM
sales
GROUP BY year_
;
注意要加上 group by，毕竟属性 year_ 有重复
接下来我们可以先缩小问题范围，仅尝试取出两年的一月份的对应销量，再求和。注意叙述顺序：取出对应年份的一月份的销量，再求和。是先取出销量再求和。 仅需要添加代码（效果图在代码段后）
SUM(CASE WHEN month_ = 1 THEN amount END) AS Jan
刚才提到的注意顺序问题，即聚合函数 SUM() 需要包裹在最外面，若我们对 THEN 后面的 amount 使用，即 SUM(amount) ，则无法得到我们想要的结果(等下展示)
同理，应用到每一个月中，只是复制粘贴的问题了。
SELECT
-- year_
-- , month_
CASE WHEN year_ = 2018 THEN 2018
WHEN year_ = 2019 THEN 2019
END AS Year_
, SUM(CASE WHEN month_ = 1 THEN amount END) AS Jan
, SUM(CASE WHEN month_ = 2 THEN amount END) AS Feb
, SUM(CASE WHEN month_ = 3 THEN amount END) AS Mar
, SUM(CASE WHEN month_ = 4 THEN amount END) AS Apr
, SUM(CASE WHEN month_ = 5 THEN amount END) AS May
, SUM(CASE WHEN month_ = 6 THEN amount END) AS June
-- , SUM(amount)
FROM
sales
GROUP BY year_
;
问题探究
如果将聚合函数 SUM() 用在了 THEN 后，则会出现如下事与愿违的效果
CASE WHEN month_ = 1 THEN SUM(amount) END AS Jan -- 仅对一月份进行尝试一下子就将整一年的销量都显示在了一月份。（具体解释以后再说）
模拟面试
- 现场写代码
- CASE WHEN 有了解吗？有几种 CASE WHEN，说说异同优劣
- 书写 CASE WHEN 有什么值得注意的地方吗？
- 谈谈你对 CASE WHEN 的理解(自己的话叙述执行过程)
- 你通常都把 CASE WHEN 用到什么地方？（考察业务背景和知识面）
后记
SQL 中 CASE WHEN 的作用远不止于此，还有非常多的骚操作，熟练掌握可大大提高 SQL 取数的工作效率，加油
腾讯云开发者
