如何在BigQuery中补充缺失日期的每月首日以计算准确MOM

我需要生成过去两年的上月销售额，以此准确计算月环比（MOM）。当前遇到以下问题：

• datetime字段为timestamp类型，操作受限；
• 转换类型后无法调用字段别名；
• 无销售额的月份没有timestamp记录，导致LAG函数只能关联有销售记录的最近月份。

希望补全所有缺失的每月首日，将对应销售额设为0，实现如下示例的正确环比计算：

原数据示例 #

datetime   total    prev_total
1/1/2022   $13,000  $11,000   
3/1/2022   $9,000   $13,000
4/1/2022   $5,000   $9,000

目标数据示例 #

datetime    total    prev_total
1/1/2022   $13,000  $11,000
2/1/2022   $0       $13,000
3/1/2022   $9,000   $0 
4/1/2022   $5,000   $9,000

现有代码（存在语法错误） #

SELECT
  x1,
  x2,
  SUM(amount) AS total,
  LAG(SUM(total)) OVER (PARTITION BY x1, x2 ORDER BY total ASC ) as prev_total,
  prev_total
FROM(
SELECT  
    t2.x1,
    t3.name,
    SUM(amount) AS total,
    DATE_TRUNC(t1.datetime, MONTH) AS total  -- 别名冲突：两个字段都命名为total
  FROM testtable1 AS t1
  JOIN testtable2 As t2 ON op = oc
  INNER testtable3 AS t3 ON t3.bc = CAST(t2.ob AS int64)  -- 缺失JOIN关键字
  INNER JOIN testtable4 AS t4 ON t4.pi = t3.pc
 WHERE t4.pc NOT IN ('MN', 'MN2','MN3', 'OH', 'OI', 'PT', 'RT')
    AND t2.pa = 'FD'
    AND t1.datetime >='2021-01-01'
    AND t2.od <> 'PC'
  GROUP BY 
    op.o_name,  -- 与SELECT中的t2.x1字段不匹配
    bh.b_name,  -- 未在SELECT子句中出现
    pr.P_ActionDateTime  -- 未在SELECT子句中出现，且应为DATE_TRUNC后的日期字段
)
GROUP BY
  x1,
  x2,
  prev_total  -- prev_total是窗口函数生成的字段，不能直接用于GROUP BY
ORDER BY 
  total DESC;

修正后的解决方案代码 #

步骤1：生成完整的月份序列 #

先创建覆盖过去两年所有月份首日的序列，确保无月份遗漏：

WITH date_range AS (
  SELECT 
    DATE_TRUNC(date, MONTH) AS month_start
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 23 MONTH),
      DATE_TRUNC(CURRENT_DATE(), MONTH),
      INTERVAL 1 MONTH
    )
  ) AS date
),

步骤2：按月份聚合原始销售数据 #

修正字段关联与分组逻辑，得到有销售记录的月份数据：

sales_by_month AS (
  SELECT
    t2.x1,
    t3.name AS x2,  -- 假设t3.name对应原代码中的x2字段
    DATE_TRUNC(t1.datetime, MONTH) AS month_start,
    COALESCE(SUM(t1.amount), 0) AS total
  FROM testtable1 AS t1
  JOIN testtable2 AS t2 ON t1.op = t2.oc  -- 补全表别名，明确关联字段
  INNER JOIN testtable3 AS t3 ON t3.bc = CAST(t2.ob AS INT64)
  INNER JOIN testtable4 AS t4 ON t4.pi = t3.pc
  WHERE t4.pc NOT IN ('MN', 'MN2','MN3', 'OH', 'OI', 'PT', 'RT')
    AND t2.pa = 'FD'
    AND t1.datetime >= '2021-01-01'
    AND t2.od <> 'PC'
  GROUP BY t2.x1, t3.name, DATE_TRUNC(t1.datetime, MONTH)
),

步骤3：补全缺失月份并计算环比 #

通过左连接补全缺失月份，用LAG函数准确获取上月销售额：

full_sales_data AS (
  SELECT
    dr.month_start AS datetime,
    s.x1,
    s.x2,
    COALESCE(s.total, 0) AS total,
    LAG(COALESCE(s.total, 0)) OVER (PARTITION BY s.x1, s.x2 ORDER BY dr.month_start) AS prev_total
  FROM date_range dr
  LEFT JOIN sales_by_month s ON dr.month_start = s.month_start
)
SELECT
  datetime,
  FORMAT("$%.2f", total) AS total,  -- 格式化金额为货币格式
  FORMAT("$%.2f", prev_total) AS prev_total
FROM full_sales_data
ORDER BY datetime ASC;

关键说明 #

• 日期序列生成：使用GENERATE_DATE_ARRAY生成连续的月份首日，确保覆盖过去两年的所有时间范围；
• 左连接补全：通过日期序列与销售数据左连接，自动将无销售额的月份销售额填充为0；
• 窗口函数修正：LAG函数按month_start排序，确保获取的是上月数据，而非最近有销售记录的月份；
• 语法错误修正：补全JOIN关键字、解决别名冲突、确保GROUP BY字段与SELECT子句一致，避免无效分组。

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