BigQuery中如何使用不同日期变量多次引用同一CTE

不需要为每个日期范围重复编写完整WITH子句，你最初的CTE复用思路方向可行，但原写法存在逻辑缺陷：如果CTE内提前完成聚合，会丢失行级datetime_col字段，无法支撑后续不同区间的筛选；如果CTE内存放行级原始数据，UNION ALL拼接多段查询的写法会重复扫描数据，且维护成本高。

推荐实现方式 #

通过「日期区间维度表+单次关联聚合」的方式实现，仅需一次扫描原表即可输出所有区间的聚合结果，聚合逻辑仅需编写一次，后续扩展区间也非常方便：

• 单独用CTE定义所有需要统计的日期区间，存每个区间的名称、开始时间、结束时间，后续新增统计区间仅需在这个CTE中追加行即可
• 抽取原表需要用到的字段，提前做粗粒度时间过滤减少无效数据扫描
• 用日期区间关联原表数据，按区间名称分组完成聚合

参考代码（以你给出的2022-01-01样例数据、统计MTD/QTD为例）：

WITH
-- 配置所有需要统计的日期区间
date_ranges AS (
  SELECT 
    'MTD' AS date_name,
    DATE_TRUNC(DATE('2022-01-01'), MONTH) AS start_dt,
    DATE('2022-01-01') AS end_dt
  UNION ALL
  SELECT 
    'QTD' AS date_name,
    DATE_TRUNC(DATE('2022-01-01'), QUARTER) AS start_dt,
    DATE('2022-01-01') AS end_dt
  -- 如需新增YTD、上周、上月同期等统计口径，直接在此处追加UNION ALL行即可
),
-- 原表数据预处理，仅保留需要用到的字段+做粗过滤
base_table AS (
  SELECT
    DATE(datetime_col) AS dt,
    col_a,
    col_b
  FROM my_table
  WHERE DATE(datetime_col) BETWEEN (SELECT MIN(start_dt) FROM date_ranges) AND (SELECT MAX(end_dt) FROM date_ranges)
)
-- 按区间聚合得到最终结果
SELECT
  dr.date_name,
  SUM(bt.col_a) AS sum_col_a,
  SUM(bt.col_b) AS sum_col_b
FROM date_ranges dr
LEFT JOIN base_table bt
  ON bt.dt BETWEEN dr.start_dt AND dr.end_dt
GROUP BY dr.date_name

运行后会直接返回你需要的结果集：

其他思路的缺陷 #

• 你最初写的UNION ALL拼接CTE方案：如果每个UNION分支都直接从原表取数聚合，会重复扫描原表数据，数据量大时性能很差；如果CTE存原始行级数据，每个分支筛选聚合的逻辑完全重复，后续修改聚合规则需要改多处代码，维护成本高
• 开头声明变量逐次赋值执行的方案：需要多次提交查询，无法一次拿到全量区间结果，后续对接下游系统、导出数据时需要处理多次返回的结果，效率极低

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