BigQuery创建含ROW_NUMBER的视图触发分区过滤策略报错问题

这是BigQuery优化器的谓词下推逻辑限制导致的：

• 无窗口函数的普通视图，查询时外层的TS过滤条件可以直接下推到底层分区表，既满足require_partition_filter的校验要求，也能正常触发分区裁剪，因此查询正常。
• 加入ROW_NUMBER()这类窗口函数后，优化器默认认为：窗口函数的计算需要基于完整的输入数据集，如果提前将外层的TS过滤条件下推到窗口计算前执行，会改变窗口函数返回的行号结果，属于逻辑错误，因此会阻止过滤条件下推。最终底层分区表无法拿到可用于分区裁剪的TS过滤条件，触发require_partition_filter的报错。
• 在视图里加恒真TS过滤条件的做法，只能绕过规则校验，但无法让后续查询的自定义TS条件下推，查询还是会扫全部分区，因此性能极差。

• 方案1：使用参数化视图（推荐，性能最优）
  如果业务允许行号基于用户查询的时间范围计算，直接用BigQuery的参数化视图，将TS过滤条件作为必填入参，参数会直接下推到底层分区表，既满足校验要求，也能正常触发分区裁剪。
  创建视图示例：

CREATE OR REPLACE VIEW mydataset.test_view (query_min_ts TIMESTAMP)
AS 
SELECT 
  *,
  ROW_NUMBER() OVER (
    PARTITION BY ID
    ORDER BY ID, TS
  ) AS row_number 
FROM mydataset.test_table
WHERE TS > query_min_ts

查询示例：

SELECT * FROM mydataset.test_view(TIMESTAMP("2021-09-05 08:30:00"))

• 方案2：使用预计算的分区物化视图
  如果业务要求行号必须基于全表数据计算（即同一ID的行号不受查询时间范围影响），改用分区物化视图预先计算窗口函数结果，物化视图本身按TS分区并开启require_partition_filter，查询时直接过滤物化视图的TS列即可触发分区裁剪。
  创建物化视图示例：

CREATE MATERIALIZED VIEW mydataset.test_mv
PARTITION BY DATE(TS)
OPTIONS(require_partition_filter = TRUE)
AS
SELECT 
  *,
  ROW_NUMBER() OVER (
    PARTITION BY ID
    ORDER BY ID, TS
  ) AS row_number 
FROM mydataset.test_table

可以根据业务对数据时效性的要求配置自动刷新或定期手动刷新，查询时直接加TS过滤条件即可。

• 方案3：使用会话变量兼容全量查询场景
  如果需要同时支持用户自定义时间范围查询和全量查询，可以在视图里内置会话变量作为过滤条件，用户查询时可通过设置变量控制过滤范围，变量会正常下推触发分区裁剪，需要查全量时将变量设为最小时间即可。
  创建视图示例：

CREATE OR REPLACE VIEW mydataset.test_view
AS 
SELECT 
  *,
  ROW_NUMBER() OVER (
    PARTITION BY ID
    ORDER BY ID, TS
  ) AS row_number 
FROM mydataset.test_table
WHERE TS >= IFNULL(@custom_ts_filter, TIMESTAMP("1970-01-01 00:00:00"))

指定范围查询示例：

SET @custom_ts_filter = TIMESTAMP("2021-09-05 08:30:00");
SELECT * FROM mydataset.test_view;

全量查询示例：

SET @custom_ts_filter = TIMESTAMP("1970-01-01 00:00:00");
SELECT * FROM mydataset.test_view;

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