BigQuery关联前后日期指标查询过慢问题排查与优化请求

我有一张包含日期（current_date）和指标（metric）的表，需要为每条数据添加下月日期的指标值（future_metric），以及下月日期去年同期的指标值（previous_metric）。当前查询逻辑能得到预期结果，但执行时间过长，已经超过1小时仍未完成。

WITH first_table AS (
  SELECT current_date, next1month_date, DATE(DATETIME_SUB(next1month_date, INTERVAL 1 YEAR)) AS previous_date, metric
  FROM (SELECT current_date, DATE(DATETIME_ADD(current_date, INTERVAL 1 MONTH)) AS next1month_date, metric
    FROM `table.name`)
),

second_table AS (
  SELECT current_date, future_metric
  FROM (SELECT current_date, metric AS future_metric
    FROM `table.name`)
),

third_table AS (
  SELECT a.current_date, a.next1month_date, a.previous_date, a.metric, b.future_metric
  FROM first_table AS a FULL JOIN second_table AS b 
  ON a.next1month_date = b.new_date
),

forth_table AS (
  SELECT current_date, previous_metric
  FROM (SELECT current_date, actual AS previous_metric
    FROM `table.name`)
)

SELECT c.current_date, c.next1month_date, c.previous_date, c.metric, c.future_metric, d.previous_metric
FROM third_table AS c FULL JOIN forth_table AS d
ON c.next1month_date = d.metric_date

• 多次重复扫描原表：原查询中first_table、second_table、forth_table都单独扫描table.name，BigQuery重复读取相同数据，IO开销和执行时间翻倍。
• 不必要的嵌套子查询：多层嵌套增加了查询解析复杂度，完全可以合并为单层逻辑。
• 错误的JOIN逻辑：使用FULL JOIN不符合实际需求（只需要保留原表所有数据），且JOIN条件存在字段错误（b.new_date、d.metric_date并非原表字段），会生成大量无效数据，拖慢执行。
• 缺少日期索引优化：若current_date未设为分区/聚簇字段，BigQuery只能全表扫描，关联效率极低。

方案一：单次扫描+窗口/关联查询（最优） #

只扫描原表一次，通过关联或窗口函数直接匹配所需数据：

WITH base_data AS (
  SELECT
    current_date,
    metric,
    DATE_ADD(current_date, INTERVAL 1 MONTH) AS next1month_date,
    DATE_ADD(DATE_ADD(current_date, INTERVAL 1 MONTH), INTERVAL -1 YEAR) AS previous_date
  FROM `table.name`
)
SELECT
  b.current_date,
  b.metric,
  b.next1month_date,
  f.metric AS future_metric,
  p.metric AS previous_metric
FROM base_data b
-- 左关联下月日期的指标
LEFT JOIN base_data f ON b.next1month_date = f.current_date
-- 左关联去年同期下月的指标
LEFT JOIN base_data p ON b.previous_date = p.current_date
ORDER BY b.current_date;

方案二：优化表结构 #

• 将current_date设为分区字段：按日期分区后，BigQuery仅扫描目标分区数据，大幅减少处理量。
• 若频繁按日期关联，将current_date设为聚簇字段，提升JOIN和过滤效率。

方案三：修正原查询的冗余与错误 #

如果保留原结构，至少修正字段错误并减少重复扫描：

WITH base_data AS (
  SELECT
    current_date,
    metric,
    DATE_ADD(current_date, INTERVAL 1 MONTH) AS next1month_date,
    DATE_ADD(DATE_ADD(current_date, INTERVAL 1 MONTH), INTERVAL -1 YEAR) AS previous_date
  FROM `table.name`
),
future_table AS (
  SELECT current_date, metric AS future_metric FROM base_data
),
previous_table AS (
  SELECT current_date, metric AS previous_metric FROM base_data
)
SELECT
  b.current_date,
  b.metric,
  b.next1month_date,
  f.future_metric,
  p.previous_metric
FROM base_data b
LEFT JOIN future_table f ON b.next1month_date = f.current_date
LEFT JOIN previous_table p ON b.previous_date = p.current_date
ORDER BY b.current_date;

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