如何在BigQuery SQL中按上月末回溯120天计算窗口SUM？

我在BigQuery中有一个数据集，包含若干float类型列（如amount_1、amount_2等），还有一个day列，为日级timestamp格式，每天仅对应一行数据（已完成分组）。

需求 #

针对每月的每一天，计算以上月最后一天为终点、回溯120天的amount_1之和。例如，当day为'2024-04-05 00:00:00 UTC'时，需计算'2023-12-03'至'2024-03-31'期间的amount_1总和，且当月所有日期的该求和结果应一致。

尝试的SQL（未达预期） #

with LastDayPreviousMonth AS (
  SELECT
    day,
    LAST_DAY(DATE_SUB(CAST(timestamp_trunc(day,month) AS DATE), INTERVAL 1 MONTH)) AS last_day_previous_month
  FROM
    main_table
)
select
day
,  SUM(amount_1) OVER(ORDER BY UNIX_SECONDS(cast(last_day_previous_month as timestamp)) RANGE BETWEEN 10368000 PRECEDING AND 0 PRECEDING) AS amount_sum
FROM
main_table as M
INNER JOIN LastDayPreviousMonth as L on L.day = M.day

调试用SQL #

为验证窗口范围，添加了min(day)查询：

with LastDayPreviousMonth AS (
  SELECT
    day,
    LAST_DAY(DATE_SUB(CAST(timestamp_trunc(day,month) AS DATE), INTERVAL 1 MONTH)) AS last_day_previous_month
  FROM
    main_table
)
select
day
,  SUM(amount_1) OVER(ORDER BY UNIX_SECONDS(cast(last_day_previous_month as timestamp)) RANGE BETWEEN 10368000 PRECEDING AND 0 PRECEDING) AS amount_sum
, min(M.day) OVER(ORDER BY UNIX_SECONDS(cast(last_day_previous_month as timestamp)) RANGE BETWEEN 10368000 PRECEDING AND 0 PRECEDING) as min_day
FROM
main_table as M
INNER JOIN LastDayPreviousMonth as L on L.day = M.day

错误现象 #

预期day为'2024-04-05 00:00:00 UTC'时min_day应为'2023-12-03'，但实际四月所有行的min_day均为'2024-01-01 00:00:00 UTC'，三月所有行则为'2023-12-01 00:00:00 UTC'。

补充说明：此处的“未达到预期”指窗口范围不符合需求，已通过Excel手动计算验证，且通过MIN函数确认窗口错误。

核心错误原因 #

当前窗口函数逻辑存在两个关键问题：

1. 窗口排序键错误：用last_day_previous_month的时间戳做排序键时，同一月份的所有行这个值完全相同（比如四月所有行的last_day_previous_month都是2024-03-31）。相同排序键值会导致RANGE BETWEEN ...的范围计算基于这个统一基准，窗口会包含所有排序键值落在[基准值-120天, 基准值]区间内的行，而非针对每个日期的回溯范围。
2. 固定秒数计算误差：用10368000秒（120243600）代表120天不准确，不同月份天数不同，且BigQuery中应使用日期函数而非固定秒数计算时间范围，避免时区、闰秒等潜在问题。

正确实现方案 #

先为每个月份确定对应的统计区间（上月最后一天往前推120天到上月最后一天），计算该区间的总和后，关联回原表让当月所有行共享这个结果：

WITH month_range AS (
  SELECT
    DATE_TRUNC(day, MONTH) AS current_month,
    LAST_DAY(DATE_SUB(DATE_TRUNC(day, MONTH), INTERVAL 1 MONTH)) AS end_date,
    DATE_SUB(LAST_DAY(DATE_SUB(DATE_TRUNC(day, MONTH), INTERVAL 1 MONTH)), INTERVAL 120 DAY) AS start_date
  FROM main_table
  GROUP BY current_month
),
monthly_sum AS (
  SELECT
    mr.current_month,
    SUM(m.amount_1) AS amount_sum
  FROM month_range mr
  JOIN main_table m
  ON m.day BETWEEN mr.start_date AND mr.end_date
  GROUP BY mr.current_month
)
SELECT
  m.day,
  ms.amount_sum
FROM main_table m
JOIN monthly_sum ms
ON DATE_TRUNC(m.day, MONTH) = ms.current_month

方案说明 #

1. 用DATE_TRUNC提取每个日期所属月份，确定该月份对应的统计区间：start_date（上月最后一天减120天）和end_date（上月最后一天）。
2. 针对每个月份单独计算区间内的amount_1总和，确保当月所有行共享同一结果。
3. 将统计结果关联回原表，得到每个日期对应的月度统计值。

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