BigQuery日分区表分析函数的分区修剪启用方法咨询

我太懂你这个痛点了——在日分区表上用分析函数后，明明写了WHERE过滤特定分区，结果却扫了全表，但去掉分析函数或者套个UNION ALL就正常。不用额外加UNION ALL的话，这几个方法能让分区修剪重新生效：

方法1：先通过子查询过滤分区，再应用分析函数 #

把分区过滤逻辑放在内层子查询里，让BigQuery先把目标分区的数据捞出来，再在外层执行分析函数计算。这样优化器能明确知道只需要处理筛选后的小数据集，自然会触发分区修剪。

举个实际的例子：

-- 原来的查询（分区修剪失效）
SELECT
  station_id,
  temp,
  ROW_NUMBER() OVER(PARTITION BY station_id ORDER BY temp DESC) AS temp_rank
FROM mydataset.gsod_partitioned
WHERE day = '20100101';

-- 修改后的查询（分区修剪生效）
SELECT
  station_id,
  temp,
  ROW_NUMBER() OVER(PARTITION BY station_id ORDER BY temp DESC) AS temp_rank
FROM (
  SELECT station_id, temp 
  FROM mydataset.gsod_partitioned 
  WHERE day = '20100101'
) AS filtered_data;

方法2：用物化CTE固定过滤后的数据集 #

BigQuery的MATERIALIZED关键字可以强制CTE先被物化（也就是先执行过滤逻辑，生成临时数据集），后续的分析函数直接基于这个临时数据集计算，这样就能确保分区修剪在物化阶段就完成。

示例代码：

WITH filtered_partition AS MATERIALIZED (
  SELECT station_id, temp, day
  FROM mydataset.gsod_partitioned
  WHERE day = '20100101'
)
SELECT
  *,
  AVG(temp) OVER(PARTITION BY station_id) AS avg_temp
FROM filtered_partition;

方法3：在分析函数的PARTITION BY中明确关联分区字段（逻辑允许时） #

如果你的分析函数逻辑允许，可以把表的分区字段（比如day）加入到分析函数的PARTITION BY子句中。这样优化器能识别到分析函数的计算范围被限定在目标分区内，进而触发分区修剪。

比如：

SELECT
  station_id,
  temp,
  ROW_NUMBER() OVER(PARTITION BY day, station_id ORDER BY temp DESC) AS temp_rank
FROM mydataset.gsod_partitioned
WHERE day = '20100101';

验证方法 #

修改完查询后，你可以在BigQuery的查询执行计划里查看「Input bytes processed」，或者直接看扫描的分区数，确认是否只读取了day=20100101的分区。

内容的提问来源于stack exchange，提问作者Arkadiusz Gąsior