BigQuery日分区表按月份聚合求列平均值的高效实现方案

直接通过一次分组聚合查询就能实现需求，无需循环执行单分区查询，BigQuery会自动利用分区裁剪优化扫描范围，效率远高于遍历方案：

SELECT
  FORMAT_DATE('%Y-%m', DATE(_PARTITIONTIME)) AS `year-month`,
  AVG(your_target_column) AS avg_value
FROM `your_project.your_dataset.name_of_the_table`
-- 可选：添加时间范围过滤，进一步缩小扫描分区
-- WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2022-01-01') AND TIMESTAMP('2023-12-31')
GROUP BY `year-month`
ORDER BY `year-month`

关键说明： #

• FORMAT_DATE('%Y-%m', DATE(_PARTITIONTIME)) 将日分区的时间戳转换为YYYY-MM格式的月份标识，匹配你需要的year-month输出列
• AVG(your_target_column) 替换成你实际要计算平均值的列名即可
• GROUP BY 子句按月份分组后，BigQuery会自动扫描对应月份下的所有日分区，无需手动遍历分区信息
• 若需要限制统计范围，添加WHERE子句指定时间区间，BigQuery会自动跳过无关分区，减少数据扫描量

为什么不需要依赖INFORMATION_SCHEMA的分区信息？ #

BigQuery的分区表在执行聚合查询时，会自动识别分区键（这里是_PARTITIONTIME）并做分区裁剪，不需要手动获取partition_id来拼接查询。手动遍历分区反而会增加多次查询的开销，远不如一次聚合高效。

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