BigQuery递归CTE报错：需用数据集限定表名问题排查

错误原因分析 #

1. 缺少递归声明关键字：BigQuery要求递归CTE必须使用WITH RECURSIVE声明，否则会将递归引用的CTE视为外部表，触发Table "cte_test" must be qualified with a dataset错误。
2. 语法错误：原查询中cte_lag定义的FROM main_table后多了一个逗号，导致后续CTE解析异常。
3. 递归逻辑缺陷：原递归部分的连接条件未按日期链关联，无法实现按7天间隔遍历日期的需求，且cte_lag未显式选择start_date字段，导致后续逻辑无法使用该字段。

修正后的完整查询 #

WITH RECURSIVE cte_lag AS(
  SELECT
    id,
    code,
    start_date,
    LAG(start_date) OVER (PARTITION BY id, code ORDER BY start_date ASC) AS previous_start_date
  FROM main_table
),
cte_test AS(
  -- 递归基准数据集：筛选出首次满足7天间隔的记录
  SELECT
    id,
    code,
    start_date,
    previous_start_date,
    1 AS lvl
  FROM cte_lag
  WHERE DATE_DIFF(start_date, previous_start_date, DAY) <= 7

  UNION ALL

  -- 递归遍历：关联下一条符合7天间隔的日期记录
  SELECT
    z.id,
    z.code,
    z.start_date,
    z.previous_start_date,
    y.lvl + 1
  FROM cte_test AS y
  INNER JOIN cte_lag AS z 
    ON y.id = z.id
    AND y.code = z.code
    AND y.start_date = z.previous_start_date
    AND DATE_DIFF(z.start_date, z.previous_start_date, DAY) <= 7
)
SELECT * FROM cte_test

关键修复点说明 #

• 新增RECURSIVE关键字：这是BigQuery识别递归CTE的必要条件，没有该关键字，系统无法解析递归引用的内部CTE。
• 修正语法错误：移除cte_lag定义末尾的多余逗号，确保CTE间的分隔正确。
• 完善递归逻辑：明确关联当前递归层的start_date与下一层的previous_start_date，保证按日期顺序遍历所有符合7天间隔的记录，同时补全start_date字段的选择，避免字段缺失错误。

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