在BigQuery中为当前季度添加上一季度最大月份信息列

解决方案SQL #

WITH monthly_sales AS (
  SELECT
    date,
    Sales,
    -- 标记当前月份是否为所在季度的最后一个月
    CASE WHEN EXTRACT(MONTH FROM date) IN (3, 6, 9, 12) THEN 1 ELSE 0 END AS is_quarter_end_month,
    -- 生成季度末月的标识文本（如"Q1 March 2022"）
    CONCAT(
      'Q', EXTRACT(QUARTER FROM date),
      ' ', FORMAT_DATE('%B', date),
      ' ', EXTRACT(YEAR FROM date)
    ) AS quarter_end_label
  FROM
    `your-project.your-dataset.your-table` -- 替换为你的表路径
),
quarter_end_sales AS (
  SELECT
    -- 计算当前季度的下一个季度起始日期，用于关联下一季度的所有数据
    DATE_ADD(DATE_TRUNC(date, QUARTER), INTERVAL 1 QUARTER) AS next_quarter_start,
    quarter_end_label,
    Sales AS prev_quarter_end_sales
  FROM
    monthly_sales
  WHERE
    is_quarter_end_month = 1
)
SELECT
  original.date,
  original.Sales,
  -- 拼接最终的上季度信息列
  CONCAT(
    qs.quarter_end_label,
    ' (对应销售额', qs.prev_quarter_end_sales, ')'
  ) AS prev_quarter_info
FROM
  `your-project.your-dataset.your-table` original
LEFT JOIN
  quarter_end_sales qs
ON
  DATE_TRUNC(original.date, QUARTER) = qs.next_quarter_start;

逻辑说明 #

1. monthly_sales CTE：

   • 为每条数据标记是否为季度末月（3/6/9/12月）
   • 生成符合要求的季度末月标识文本，利用FORMAT_DATE('%B', date)获取英文月份全称

2. quarter_end_sales CTE：

   • 筛选出所有季度末月的数据
   • 计算该季度的下一个季度起始日期，用于后续关联下一季度的全部数据

3. 主查询关联：

   • 将原始数据按季度与上一季度末月的数据关联，让当前季度的所有行都能匹配到上一季度末的标识和销售额

样本数据输出效果 #

补充：处理无历史数据的情况 #

如果上一季度末月没有数据（如示例中2022年Q1对应的2021年Q4数据缺失），可以用COALESCE优化输出，避免显示NULL：

CONCAT(
  COALESCE(qs.quarter_end_label, 'Q4 December 2021'), -- 手动指定已知缺失的标识
  ' (对应销售额', COALESCE(CAST(qs.prev_quarter_end_sales AS STRING), 'N/A'), ')'
) AS prev_quarter_info

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