如何在BigQuery中获取近3年分年月销售数据（列头展示年销售额）

需求实现方案 #

你的目标是按月份汇总近2-3年的销售数据，将每一年的销售总和作为单独列展示（比如2021_sales、2022_sales），当前SQL仅处理了上一年数据且逻辑存在偏差，以下是适配BigQuery的正确实现：

核心方法：条件聚合行转列 #

通过CASE WHEN配合SUM，将不同年份的销售数据映射为单独列，同时按月份分组确保数据对齐。

方案1：获取近3年销售数据（年份作为列头） #

SELECT
  EXTRACT(MONTH FROM sell_date) AS month,
  -- 按年份筛选并求和，COALESCE将无数据的月份填充为0
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sell_date) = EXTRACT(YEAR FROM CURRENT_DATE()) - 2 THEN sum_sale_value END), 0) AS `year_${EXTRACT(YEAR FROM CURRENT_DATE()) - 2}_sales`,
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sell_date) = EXTRACT(YEAR FROM CURRENT_DATE()) - 1 THEN sum_sale_value END), 0) AS `year_${EXTRACT(YEAR FROM CURRENT_DATE()) - 1}_sales`,
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sell_date) = EXTRACT(YEAR FROM CURRENT_DATE()) THEN sum_sale_value END), 0) AS `year_${EXTRACT(YEAR FROM CURRENT_DATE())}_sales`
FROM `project.dataset.table`
-- 筛选近3年的销售记录
WHERE EXTRACT(YEAR FROM sell_date) >= EXTRACT(YEAR FROM CURRENT_DATE()) - 2
GROUP BY month
ORDER BY month;

提示：如果需要固定年份（不随当前日期变化），可将EXTRACT(YEAR FROM CURRENT_DATE()) - 2替换为具体年份，比如2021，列名也可改为2021_sales更直观。

方案2：获取近2年销售数据 #

只需调整筛选条件和列数：

SELECT
  EXTRACT(MONTH FROM sell_date) AS month,
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sell_date) = EXTRACT(YEAR FROM CURRENT_DATE()) - 1 THEN sum_sale_value END), 0) AS `year_${EXTRACT(YEAR FROM CURRENT_DATE()) - 1}_sales`,
  COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sell_date) = EXTRACT(YEAR FROM CURRENT_DATE()) THEN sum_sale_value END), 0) AS `year_${EXTRACT(YEAR FROM CURRENT_DATE())}_sales`
FROM `project.dataset.table`
WHERE EXTRACT(YEAR FROM sell_date) >= EXTRACT(YEAR FROM CURRENT_DATE()) - 1
GROUP BY month
ORDER BY month;

示例输出（近3年） #

原代码问题说明 #

1. WHERE条件仅筛选当前年份数据，无法获取历史年份的销售记录
2. 仅通过previous_year字段推导上一年，但未实际统计历史年份的销售总和
3. 最终输出仅一列，不符合“各年份销售总和作为列头”的需求

内容的提问来源于stack exchange，提问作者pavan katapur