在 BigQuery 中，计划的 SQL 查询不能在分区字段和另一个字段上 WHERE

Question

奇怪的是，我可以保存并运行这个预定的查询没有问题

DECLARE runTimeVariable TIMESTAMP DEFAULT @run_time;
SELECT * FROM
`myTable`   
WHERE insert_timestamp > timestamp_sub(runTimeVariable, INTERVAL 7 DAY) 

但我试着逃跑

DECLARE runTimeVariable TIMESTAMP DEFAULT @run_time;
SELECT * FROM
`myTable`   
WHERE insert_timestamp > timestamp_sub(runTimeVariable, INTERVAL 7 DAY) 
AND x > 0

或任何其他AND子句那么我不能保存它，我得到Query error: Cannot query over table ... without a filter over column(s) ... that can be used for partition elimination at

谢谢!

Answer 1

我发现了一个奇怪的解决方案，它工作得很好(不需要变量)

SELECT * FROM `mytable`   
WHERE insert_timestamp > timestamp_micros(0) # THIS IS THE STRANGE WORKAROUND
AND insert_timestamp > timestamp_sub(current_timestamp()  , INTERVAL 7 DAY) # THIS IS TH REAL FILTER ON THE PARTITIONED FIELD
AND x >0