Content
- 输入数据
- 输出效果
- 思路
- 代码实现
- 细节讨论
❤️ 「更多数据分析真题」
《数据分析真题日刷 | 目录索引》
❤️ 「更多我的秋招经验贴」
《2020我的秋招总结帖 [数据分析岗] | 目录索引》
一、 输入数据
表due 如下,数据自己生成的 ~
二、输出效果
三、思路
- 在每个year下,对month分情况判断(month=1还是2还是3还是4),输出对应的amount;因此用CASE WHEN语句;
- 关键点,除了对月份 CASE WHEN 分类判断,还需要对year处理,把同一年的amount 加总,这里用到SUM 和 GROUP BY。没明白没关系,可以转跳第五part 的讨论。
四、代码实现
SELECT
year,
SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) AS m1,
SUM(CASE WHEN month = 2 THEN amount ELSE 0 END) AS m2,
SUM(CASE WHEN month = 3 THEN amount ELSE 0 END) AS m3,
SUM(CASE WHEN month = 4 THEN amount ELSE 0 END) AS m4
FROM due
GROUP BY year;
五、细节讨论
我们讨论SUM
和 GROUP BY
的作用。
如果是下面这段代码,
SELECT
year,
(CASE WHEN month = 1 THEN amount ELSE 0 END) AS m1,
(CASE WHEN month = 2 THEN amount ELSE 0 END) AS m2,
(CASE WHEN month = 3 THEN amount ELSE 0 END) AS m3,
(CASE WHEN month = 4 THEN amount ELSE 0 END) AS m4
FROM due;
输出效果如下,
我考场上就是这么写的……,但是这种情况下,会按照每年每月分别一行,而不是按照年合并,因此需要将同一年的amount 加总,即SUM(amount)
,并且 GROUP BY year
。
SELECT
year,
SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) AS m1,
SUM(CASE WHEN month = 2 THEN amount ELSE 0 END) AS m2,
SUM(CASE WHEN month = 3 THEN amount ELSE 0 END) AS m3,
SUM(CASE WHEN month = 4 THEN amount ELSE 0 END) AS m4
FROM due
GROUP BY year;
- 正确输出