Google BigQuery不硬编码过滤条件且不使用declare实现分区裁剪的方法

可行方案说明 #

你当前的写法已经可以触发GBQ的分区裁剪，不需要额外用DECLARE语句，仅需满足两个前提：

• 子查询返回的是唯一单行单值：你的tmp_table中partitionStartDate只能有1行数据，否则标量子查询会直接报错，也无法触发裁剪
• 子查询是不相关查询：子查询的逻辑不依赖外层主查询的任何字段，你现在写的独立CTE写法完全符合这个要求，GBQ优化器会在执行主查询前先计算出固定的日期值，再对table1做分区裁剪

如果担心当前写法触发裁剪不稳定，可以换成更稳妥的JOIN写法，哪怕需要过滤多个分区值也可以正常生效：

SELECT t.*
FROM table1 t
INNER JOIN tmp_table tmp
  ON t.partitionDate = tmp.partitionStartDate

注意事项 #

• 不要对分区字段做函数运算，比如DATE(t.partitionDate) = (select partitionStartDate from cte)这类写法会直接导致分区裁剪失效
• 验证裁剪是否生效可以直接看GBQ的查询预估扫描字节数，若数值和你预期的对应分区的数据量一致，说明裁剪已经正常生效

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