如何在BigQuery中基于分区列筛选指定时段每月最后一天的数据

你的表结构如下：

需求是筛选指定时段（比如2024-01-01至2024-04-01）内updateDate为每月最后一天的数据，通过减少扫描的分区数来降低查询成本，目标日期是2024-01-31、2024-02-29、2024-03-31。

你之前尝试的SQL存在问题，代码如下：

SELECT keyword, 
     qcCount, 
     updateDate
FROM my_table
WHERE updateDate IN 
(
   SELECT LAST_DAY(dt) 
   FROM UNNEST(
          GENERATE_DATE_ARRAY(DATE(2024,1,1), 
          DATE(2024,4,1)
        )
   ) AS dt
);

问题原因 #

这段SQL会生成时段内的所有日期，再把每个日期转成当月最后一天，导致子查询里充满重复的月末日期。更关键的是，这种写法无法让BigQuery触发分区裁剪——因为分区字段updateDate需要匹配明确的常量值，子查询的动态生成方式会让优化器无法识别要扫描哪些分区，最终还是会扫全量分区，达不到降成本的目的。

正确解法 #

解法1：高效生成不重复的月末日期

用INTERVAL 1 MONTH作为步长生成每月起始日，再取当月最后一天，这样子查询只会输出不重复的月末日期，BigQuery能识别这些常量并触发分区裁剪：

SELECT keyword, qcCount, updateDate
FROM my_table
WHERE updateDate IN (
  SELECT LAST_DAY(dt)
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE(2024,1,1), DATE(2024,4,1), INTERVAL 1 MONTH)) AS dt
)

这里GENERATE_DATE_ARRAY只会生成2024-01-01、2024-02-01、2024-03-01、2024-04-01，再通过LAST_DAY得到对应的月末日期，没有重复值，效率更高。

解法2：手动指定月末日期（适合短时段）

如果时段范围不大，直接列出目标日期是最稳妥的，完全符合分区字段的常量筛选要求，查询效率最高：

SELECT keyword, qcCount, updateDate
FROM my_table
WHERE updateDate IN ('2024-01-31', '2024-02-29', '2024-03-31')

解法3：日期范围+月末判断（需注意优化器行为）

如果不想手动生成日期，也可以用范围过滤结合月末判断，但要确认BigQuery能识别分区过滤逻辑：

SELECT keyword, qcCount, updateDate
FROM my_table
WHERE updateDate BETWEEN DATE(2024,1,1) AND DATE(2024,4,1)
  AND updateDate = LAST_DAY(updateDate)

不过这种写法在某些场景下，优化器可能还是会扫描时段内所有分区，所以优先推荐前两种方法。

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