![61adc4f2f93c28762302b99d4ef107f8.png](https://i-blog.csdnimg.cn/blog_migrate/ce3c1b0768d144e36328f0508aa97ef2.png)
前言
常见的面试题中包含的知识点,也是平时练手的经典题,把知识点串起来的同时也很好的联系了业务实际。直接将代码背诵记忆同样可在相似场景中发挥作用。知识储备
年月销量数据表 sales
SQL 编辑器(笔者 dbForge Studio)
SQL 基本知识(SELECT, GROUP BY, AS, 聚合函数SUM)
业务背景
转置财务报表,将源数据以更简洁明了的形式呈现给同事、上司,合作伙伴等,以促进后续的数据分析工作。
年份分组,对每一年的各月份的零散销量进行汇总统计,行转置成列,打横输出,列名刚好对应月份,十分简洁明了。
流程分析
Step1:思路探索
需求的实现不一定都是一蹴而就的,先简单模拟一下低端版本的效果可以为最终实现打开思路。
SELECT year_ , month_ /* 这种格式写代码比较整洁 1. 可以直接注释掉一行而不影响其他行 2. 可在一行末尾添加注释而不影响其他行 3. 可提醒自己不要漏掉括号*/ , SUM(amount) AS total_amountFROM salesGROUP 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_amountFROM salesGROUP BY year_, month_) AS new_table-- 子查询结束(养成良好的标记子查询开始与结束的习惯);
不要看 CASE WHEN 的语句那么长,其实也就生成一列而已,长是因为生成的列中每一行都需要根据需求定制。
Step3:正式组合
先看首列,只是显示两个年份,对 year_ 使用 ' 搜索 CASE '
SELECTCASE WHEN year_ = 2018 THEN 2018WHEN year_ = 2019 THEN 2019END AS Year_FROM salesGROUP BY year_;
![8003f24930613e69d0c8d012b4ec6439.png](https://i-blog.csdnimg.cn/blog_migrate/33dfd31e6285c8e76b631fabb5a0dc6c.png)
注意要加上 group by,毕竟属性 year_ 有重复
接下来我们可以先缩小问题范围,仅尝试取出两年的一月份的对应销量,再求和。注意叙述顺序:取出对应年份的一月份的销量,再求和。是先取出销量再求和。
仅需要添加代码(效果图在代码段后)
SUM(CASE WHEN month_ = 1 THEN amount END) AS Jan
![27465a88e0bbc7ecd28404e1a8fd3501.png](https://i-blog.csdnimg.cn/blog_migrate/eb676e0529647d729d5bebd4d18b11dc.png)
同理,应用到每一个月中,只是复制粘贴的问题了。
SELECT-- year_-- , month_CASE WHEN year_ = 2018 THEN 2018WHEN year_ = 2019 THEN 2019END 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 salesGROUP BY year_;
![d2e286ec691cd5f0e63458281065f050.png](https://i-blog.csdnimg.cn/blog_migrate/61fc45015dbdbeb1e620addd217c35b9.png)
问题探究
如果将聚合函数 SUM() 用在了 THEN 后,则会出现如下事与愿违的效果
CASE WHEN month_ = 1 THEN SUM(amount) END AS Jan -- 仅对一月份进行尝试
一下子就将整一年的销量都显示在了一月份。(具体解释以后再说)
![eb4bcbd163ddd719609779ebaa4eeb09.png](https://i-blog.csdnimg.cn/blog_migrate/dae17969708d4e9a9c63d13b042eb752.png)
模拟面试
现场写代码
CASE WHEN 有了解吗?有几种 CASE WHEN,说说异同优劣
书写 CASE WHEN 有什么值得注意的地方吗?
谈谈你对 CASE WHEN 的理解(自己的话叙述执行过程)
你通常都把 CASE WHEN 用到什么地方?(考察业务背景和知识面)
后记
SQL 中 CASE WHEN 的作用远不止于此,还有非常多的骚操作,熟练掌握可大大提高 SQL 取数的工作效率,加油
注:相关数据源和超详细的 SQL 代码已经整理好,在 “ 数据分析与商业实践 ” 公众号后台回复 “ 转置财报 ” 即可获取。模型精度提升 -- 虚拟变量的设置