BigQuery分区表已加过滤条件仍要求指定分区筛选器问题求助

首先明确两次尝试失败的核心原因：

1. BigQuery默认要求分区过滤条件为查询编译阶段可确定的静态值，未开启配置时动态子查询、运行时生成的数组都无法被识别为合法的分区过滤条件
2. 第二次尝试存在明显的变量名拼写错误：声明的变量为date_filter，实际引用时写为data_filter，直接导致分区过滤逻辑失效

可行方案1：开启动态分区裁剪配置（最推荐） #

目前BigQuery已经原生支持动态分区裁剪，只需要在查询开头开启会话参数，修正变量名即可正常运行：

-- 开启动态分区裁剪开关
SET @@query_configuration.dynamic_partition_pruning = true;

-- 声明日期过滤数组，注意变量名一致
DECLARE date_filter ARRAY<DATE> DEFAULT (SELECT ARRAY_AGG(DISTINCT day) FROM your_date_source_table);

SELECT a.hello,b.ciao
FROM your_table_a a
INNER JOIN your_partitioned_table b
  ON a.time = b.time 
  AND b.date IN UNNEST(date_filter)

该方案可以自动识别运行时生成的日期数组，仅扫描匹配的分区，不会全表扫描。

可行方案2：将分区过滤移至WHERE子句 #

部分场景下JOIN的ON子句条件会晚于分区裁剪逻辑执行，将分区过滤条件移到WHERE子句可以提升识别优先级：

SET @@query_configuration.dynamic_partition_pruning = true;

DECLARE date_filter ARRAY<DATE> DEFAULT (SELECT ARRAY_AGG(DISTINCT day) FROM your_date_source_table);

SELECT a.hello,b.ciao
FROM your_table_a a
INNER JOIN your_partitioned_table b
  ON a.time = b.time 
WHERE b.date IN UNNEST(date_filter)

可行方案3：固定分区范围边界（无配置依赖） #

如果无法修改会话参数，可以先计算出日期的最小、最大值作为静态边界先过滤，再匹配具体日期，既满足分区裁剪要求，又不会多扫无关分区：

WITH date_range AS (
  SELECT MIN(day) AS min_day, MAX(day) AS max_day, ARRAY_AGG(DISTINCT day) AS day_list
  FROM your_date_source_table
)
SELECT a.hello,b.ciao
FROM your_table_a a
INNER JOIN your_partitioned_table b
  ON a.time = b.time 
INNER JOIN date_range dr
  -- 先用静态可识别的范围过滤分区，裁剪掉无关分区
  ON b.date BETWEEN dr.min_day AND dr.max_day
  -- 再精确匹配日期
  AND b.date IN UNNEST(dr.day_list)

该方案不需要开启任何特殊配置，利用BigQuery可以识别BETWEEN静态范围的特性先完成分区裁剪，再做精确匹配，性能损失极低。

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