BigQuery SQL嵌套循环问题求助：计算近2个月每日7日回溯指标

原代码的核心问题 #

• 递归CTE的终止条件完全无效：days >= days - 60和iter >= iter -7这两个逻辑永远为真，因为任何日期减去正数后肯定比原日期小，导致递归无限执行，陷入死循环。
• 嵌套递归完全多余，逻辑绕远路，用窗口函数就能直接实现过去7天的统计需求。

修复后的SQL代码 #

WITH RECURSIVE gen_dates AS (
  -- 起始日期：统计的截止日期
  SELECT DATE'2025-05-27' AS days
  UNION ALL
  -- 逐日往前推，直到覆盖近2个月的范围
  SELECT days - 1
  FROM gen_dates
  WHERE days >= DATE_SUB(DATE'2025-05-27', INTERVAL 2 MONTH)
),
-- 关联业务表，确保每个日期都有记录（无数据时num_recs设为0）
date_joined_data AS (
  SELECT
    'CUSTOMER_SALES' AS tgt_tbl,
    gd.days AS job_dtm,
    COALESCE(a.num_recs, 0) AS num_recs
  FROM gen_dates gd
  LEFT JOIN `my_project.my_dataset.audit_tbl` a
    ON DATE(a.job_dtm) = gd.days
    AND a.tgt_tbl = 'CUSTOMER_SALES'
),
-- 计算过去7天（含当天）的统计指标
calc_metrics AS (
  SELECT
    tgt_tbl,
    job_dtm,
    num_recs,
    -- 窗口函数计算过去7天的总和（6天前到当天）
    SUM(num_recs) OVER (
      PARTITION BY tgt_tbl 
      ORDER BY job_dtm 
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS sum_7d,
    FORMAT_DATE('%a', job_dtm) AS dayofweek
  FROM date_joined_data
)
-- 最终计算平均值和偏差百分比
SELECT 
  tgt_tbl,
  job_dtm,
  dayofweek,
  num_recs,
  sum_7d,
  ROUND(sum_7d / 7, 2) AS avg_7d, -- 过去7天的平均值，除以7更合理
  -- 计算当日数据与7天均值的偏差百分比，避免除以0的情况
  ROUND(
    IF(sum_7d = 0, 0, ((num_recs - sum_7d/7) / (sum_7d/7)) * 100),
    2
  ) AS pct_deviation
FROM calc_metrics
ORDER BY job_dtm DESC;

关键修复点说明 #

1. 递归日期生成：只用一个递归CTE生成近2个月的完整日期范围，终止条件改为days >= DATE_SUB(结束日期, INTERVAL 2 MONTH)，确保递归到指定日期后停止。
2. 左连接补全日期：用LEFT JOIN关联业务表，保证即使某天没有业务数据，也会保留日期记录，避免统计遗漏。
3. 窗口函数替代嵌套递归：直接用窗口函数ROWS BETWEEN 6 PRECEDING AND CURRENT ROW计算过去7天的总和，逻辑更清晰，性能更好。
4. 优化指标计算：将平均值改为除以7（过去7天的均值），同时增加IF判断避免除以0的错误。

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