google-bigquery - 在大查询中进行滚动聚合的更好方法?
问题描述
我有一些用 BigQuery 编写的 SQL 代码,它对三天前的当前日期的值进行滚动求和。
with weekly_agg AS
(
select
* ,
UNIX_SECONDS(TIMESTAMP(event_date)) as timestamps
from
test.window_test
order by event_date
)
select
country,
event_date
sum(value) over (partition by country order by timestamps range between 259200 PRECEDING AND CURRENT ROW) as rolling
from
weekly_agg
这似乎是一个冗长的解决问题的方法,有没有更好的方法来完成同样的事情?第一个限制是您需要对值 259200(3 天)进行硬编码,您无法输入诸如 ((3600 * 24) * 3) 之类的计算。如果我能在分区部分做一个日期范围会更好。
解决方案
第一个限制是您需要硬编码值 259200(3 天),您无法输入诸如 ((3600 * 24) * 3) 之类的计算
以下仅使用3
几天
#standardSQL
WITH weekly_agg AS (
SELECT
* ,
DATE_DIFF(event_date, '2000-01-01', DAY) AS day
FROM `test.window_test`
ORDER BY event_date
)
SELECT
country,
event_date,
SUM(value) OVER(PARTITION BY country ORDER BY day RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling
FROM weekly_agg
有没有办法使用日期而不是数字之间的范围?
如果您将使用日期而不是范围 - 这将是一些其他逻辑(不是 a rolling aggregation
) - 例如简单的分组
SELECT
country,
event_date,
SUM(value)
FROM weekly_agg
WHERE event_date BETWEEN <date1> AND <date2>
GROUP BY country, event_date
但这很可能不是您想要的...