Google BigQuery使用分区字段仍报错：需添加分区消除过滤条件

执行以下BigQuery查询语句：

select count(1)
from `some_catalogue.some_table_with_partitions`
WHERE date_trunc(period,week(MONDAY)) >= (select max(date_trunc(period,week(MONDAY))) from `some_catalogue.some_table_with_partitions` where period >= date_add(current_date('Asia/Jakarta'),interval -1 day) )

其中some_table_with_partitions是以period列为分区字段的表，收到错误信息：

Cannot query over table
'some_project.some_catalogue.some_table_with_partitions' without a
filter over column(s) 'period' that can be used for partition
elimination.

明明已经用到分区字段，却仍报错，该如何解决？

核心原因 #

BigQuery的分区消除机制要求分区字段必须作为过滤条件的顶层字段，不能被函数包裹。你的外层WHERE条件用date_trunc()处理了period，BigQuery无法解析这个函数后的条件对应哪些分区，因此触发了必须添加有效分区过滤的报错。

修复方案 #

先单独计算出目标周的起始日期，再用period直接与该日期范围做比较，让BigQuery能识别出需要扫描的分区：

WITH target_week_info AS (
  -- 计算要过滤的目标周起始日期
  SELECT MAX(date_trunc(period, week(MONDAY))) AS target_week_start
  FROM `some_catalogue.some_table_with_partitions`
  WHERE period >= DATE_ADD(CURRENT_DATE('Asia/Jakarta'), INTERVAL -1 DAY)
)
SELECT COUNT(1)
FROM `some_catalogue.some_table_with_partitions`
CROSS JOIN target_week_info
-- 直接用分区字段period和明确的日期范围比较，支持分区消除
WHERE period >= target_week_info.target_week_start
  AND period < DATE_ADD(target_week_info.target_week_start, INTERVAL 1 WEEK)

补充说明 #

• 用period < DATE_ADD(target_week_start, INTERVAL 1 WEEK)比BETWEEN更稳妥，避免因period包含时分秒时出现的边界问题。
• 子查询移到CTE中，逻辑更清晰，同时不影响分区消除的判断。

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