如何优化BigQuery至Google Sheets定时同步查询以降低数据处理量？

需求为每周日23:59将BigQuery中过去21天的数据同步至Google Sheets。编写的定位周日的查询代码如下：

SELECT
 EXTRACT(YEAR FROM date) AS year,
 week,
 name,
 parent_name,
 sum(total_sales) as total_revenue,
 title
 FROM partitioned_ts
WHERE
 id=14213
 and
 total_sales>0
 and
date BETWEEN DATE_SUB(CURRENT_DATE(), 
             INTERVAL 
             IF( EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1, 
                 6, 
                 EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1) 
             DAY) - INTERVAL 21 DAY AND DATE_SUB(CURRENT_DATE(), 
             INTERVAL 
             IF( EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1, 
                 6, 
                 EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1) 
             DAY)
GROUP BY name, parent_name, week, year, title 
order by year DESC, week DESC, name

该查询运行时将处理85.31 GB数据。旧版查询以当前日期倒推21天筛选数据，代码片段如下：

date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 21 DAY) AND CURRENT_DATE()

旧版仅需处理1.6 GB数据。请问如何编写查询才能降低数据处理量？

核心问题是原查询的嵌套日期计算逻辑，导致BigQuery无法触发分区裁剪（分区表的过滤条件需要是能直接匹配分区字段的简单表达式），只能扫描全部分区，所以数据处理量暴增。以下是两种可行的优化写法：

方法1：用DATE_TRUNC简化周日定位 #

直接用DATE_TRUNC快速定位最近的周日，再倒推21天作为起始日期，表达式简洁且能被分区优化器识别：

SELECT
 EXTRACT(YEAR FROM date) AS year,
 week,
 name,
 parent_name,
 sum(total_sales) as total_revenue,
 title
FROM partitioned_ts
WHERE
 id = 14213
 AND total_sales > 0
 AND date BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), WEEK(SUNDAY)), INTERVAL 21 DAY) 
             AND DATE_TRUNC(CURRENT_DATE(), WEEK(SUNDAY))
GROUP BY name, parent_name, week, year, title 
ORDER BY year DESC, week DESC, name

方法2：用WITH子句提前计算日期范围 #

如果需要保留原有的周日判断逻辑，可以用WITH子句先算出明确的起始/结束日期，再在WHERE条件中直接引用，确保过滤条件是清晰的日期值：

WITH date_range AS (
  SELECT
    -- 计算起始日期：最近周日往前推21天
    DATE_SUB(
      CASE 
        WHEN EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1 THEN CURRENT_DATE()
        ELSE DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1 DAY)
      END, 
      INTERVAL 21 DAY
    ) AS start_date,
    -- 计算结束日期：最近的周日
    CASE 
      WHEN EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) = 1 THEN CURRENT_DATE()
      ELSE DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1 DAY)
    END AS end_date
)
SELECT
 EXTRACT(YEAR FROM date) AS year,
 week,
 name,
 parent_name,
 sum(total_sales) as total_revenue,
 title
FROM partitioned_ts, date_range
WHERE
 id = 14213
 AND total_sales > 0
 AND date BETWEEN date_range.start_date AND date_range.end_date
GROUP BY name, parent_name, week, year, title 
ORDER BY year DESC, week DESC, name

关键原理 #

BigQuery的分区裁剪仅在过滤条件为分区字段与常量/可预计算的日期表达式时生效。原查询的嵌套日期计算让优化器无法提前解析出具体的日期范围，只能扫描全表；优化后的写法让日期范围提前被解析为明确值，触发分区裁剪，仅扫描需要的21天分区，处理量会回到和旧版查询相近的水平。

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