BigQuery视图分区过滤用IN子查询报错，求解决方法

这个错误的核心原因是：你的视图基于分区表data_mart.customer_raw，当用子查询(SELECT business_date FROM dim_date)作为过滤条件时，查询优化器无法提前确定具体的分区值，没法做分区消除，违反了BigQuery对分区表查询的强制过滤要求。

下面是几个可行的解决办法：

方法1：用动态SQL生成明确的日期列表 #

先从日期表提取月末日期，拼成静态的IN条件字符串，再执行查询。这样执行时优化器能看到明确的日期值，正常触发分区消除。

示例代码：

DECLARE eom_dates STRING;

-- 从table_date获取所有月末日期，拼接成逗号分隔的字符串
SET eom_dates = (
  SELECT STRING_AGG(DISTINCT "'" || business_date || "'", ', ')
  FROM table_date
  WHERE eom_flag IS TRUE
);

-- 用动态SQL执行查询
EXECUTE IMMEDIATE '''
SELECT
  customer_id,
  total_transaction
FROM
  table_1
WHERE
  business_date IN (''' || eom_dates || ''')
''';

方法2：添加明确的日期范围引导过滤 #

在WHERE子句中先指定一个明确的、较窄的日期范围（比如某一年/某几个季度），再结合子查询过滤月末日期。这样优化器能先基于这个范围做分区消除，再筛选月末数据。

示例代码：

WITH dim_date AS (
  SELECT DISTINCT business_date
  FROM table_date
  WHERE eom_flag IS TRUE
)
SELECT
  customer_id,
  total_transaction
FROM
  table_1
WHERE
  -- 先指定明确的日期范围，帮助优化器做分区消除
  business_date BETWEEN '2024-01-01' AND '2024-12-31'
  AND business_date IN (SELECT business_date FROM dim_date)

方法3：用函数直接生成月末日期（无需依赖日期表） #

如果你的月末日期是规则的（比如某一年的所有月末），可以用GENERATE_DATE_ARRAY和LAST_DAY函数直接生成，这种方式优化器能识别出具体的日期值，不会触发分区消除错误。

示例代码：

WITH dim_date AS (
  SELECT LAST_DAY(date) AS business_date
  FROM UNNEST(GENERATE_DATE_ARRAY('2024-01-01', '2024-12-31', INTERVAL 1 MONTH)) AS date
)
SELECT
  customer_id,
  total_transaction
FROM
  table_1
WHERE
  business_date IN (SELECT business_date FROM dim_date)

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