【数据分析面试】 18.计算每季度的各部门支出（SQL：EXTRACT函数介绍）

题目

假设我们有一张记录了2023年内发生的交易的表，每笔交易属于公司内不同的部门。我们想要计算IT、HR和Marketing的总支出，并且还有Other部门的总支出，按财季分组。

编写一个查询来显示这个结果。

注意：只显示至少有一笔交易发生的季度。季度名称应为Q1、Q2、Q3和Q4。Q1指一月至三月。

示例:

输入:

transactions table

Column	Type
transaction_id	INTEGER
department	VARCHAR
amount	FLOAT
transaction_date	DATE

输出:

Column	Type
quarter	VARCHAR
it_spending	FLOAT
hr_spending	FLOAT
marketing_spending	FLOAT
other_spending	FLOAT

答案

按照交易日期的财季进行分组。各个季度如下：

• Q1：一月至三月
• Q2：四月至六月
• Q3：七月至九月
• Q4：十月至十二月

SELECT
  CASE 
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 1 AND 3 THEN 'Q1'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 4 AND 6 THEN 'Q2'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 7 AND 9 THEN 'Q3'
    WHEN EXTRACT(MONTH FROM transaction_date) BETWEEN 10 AND 12 THEN 'Q4'
  END AS quarter,
  SUM(CASE WHEN department = 'IT' THEN amount ELSE 0 END) AS it_spending,
  SUM(CASE WHEN department = 'HR' THEN amount ELSE 0 END) AS hr_spending,
  SUM(CASE WHEN department = 'Marketing' THEN amount ELSE 0 END) AS marketing_spending,
  SUM(CASE WHEN department NOT IN ('IT', 'HR', 'Marketing') THEN amount ELSE 0 END) AS other_spending
FROM
  transactions
GROUP BY
  quarter;

上述查询中使用了CASE WHEN语句的两种方式：

• 第一种将交易日期按季度分类；
• 第二种嵌套了求和和case语句，按部门汇总支出。

输出表显示了按财季分组的每个部门的总销售金额。‘OtherSales’列包括了未明确列出的部门的销售额（比如交易表中的‘Finance’）。

EXTRACT函数

EXTRACT函数在SQL中用于从日期或时间值中提取特定的部分，比如年份、月份、日等。以下是EXTRACT函数的简单语法总结：

EXTRACT(field FROM source)

其中：

• field 是要提取的日期或时间部分，可以是YEAR、MONTH、DAY、HOUR、MINUTE、SECOND等。
• source 是要从中提取部分的日期或时间值，通常是列名、表达式或者直接的日期/时间值。

举例来说，如果你想要从一个日期列中提取年份，你可以这样写：

SELECT EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees;

这将返回一个包含员工入职年份的结果集。 EXTRACT函数使得在SQL中从日期或时间值中获取特定部分变得非常方便。

更多详细答案可关注公众号查阅。