它需要在过去21天的每个星期天晚上11:59从BigQuery中提取数据到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。我应该如何编写查询来处理较低的数据?
过滤超过21天的行最简单的方法是:
DATE_DIFF(CURRENT_DATE(), date, DAY) <= 21
如果需要减少扫描大小,最好的方法是按日期对源表进行分区
https://cloud.google.com/bigquery/docs/querying-partitioned-tables use_a_constant_filter_expression
下面的代码可以工作:
date BETWEEN DATE_SUB((DATE_TRUNC(CURRENT_DATE(), WEEK(星期六)))),INTERVAL 21 DAY) AND (DATE_TRUNC(CURRENT_DATE(), WEEK(星期六))))