我有一些MongoDB的经验,我正在学习BigQuery。我正在尝试执行以下任务,我不知道如何使用BigQuery的标准SQL。
我有一个包含以下数据的表。它包含发生在不同网站url上的事件。时间戳表示给定事件发生的时间。例如,第一行表示,"事件'xx'在2016-10-18 15:55:16 UTC发生在url ' .html'上。"
event_id | url | timestamp
-----------------------------------------------------------
xx a.html 2016-10-18 15:55:16 UTC
xx a.html 2016-10-19 16:68:55 UTC
xx a.html 2016-10-25 20:55:57 UTC
yy b.html 2016-10-18 15:58:09 UTC
yy a.html 2016-10-18 08:32:43 UTC
zz a.html 2016-10-20 04:44:22 UTC
zz c.html 2016-10-21 02:12:34 UTC
我想在滚动的3天窗口中计算每个url上发生的每个事件的数量。换句话说,我希望能够说出以下内容:
"在url ' .html'上,在[2016-10-18 00:00:00 UTC, 2016-10-21 00:00:00 UTC]时间间隔内,事件'xx'发生两次。"
"在url ' .html'上,在[2016-10-19 00:00:00 UTC, 2016-10-22 00:00:00 UTC]时间间隔内,事件'xx'发生一次。"
"在url ' .html'上,在[2016-10-20 00:00:00 UTC, 2016-10-23 00:00:00 UTC]时间间隔内,事件'xx'发生了0次。"(注意:这并不需要作为一行返回。如果没有这一行,则表示该事件发生了0次。
一些注意事项:我的数据库每天包含超过100k行,并且事件的发生变化。也就是说,在1天内,事件'xx'将发生~10,000次,事件'zz'将发生~0-2次。
考虑到我有限的SQL知识,我不想为结果表提供结构,因为我认为这可能会错误地限制可能的答案。谢谢!
下面是BigQuery标准SQL(参见启用标准SQL
)我使用ts
作为字段名称(而不是timestamp
,因为它在您的示例中),并假设此字段为TIMESTAMP
数据类型
WITH dailyAggregations AS (
SELECT
DATE(ts) AS day,
url,
event_id,
UNIX_SECONDS(TIMESTAMP(DATE(ts))) AS sec,
COUNT(1) AS events
FROM yourTable
GROUP BY day, url, event_id, sec
)
SELECT
url, event_id, day, events,
SUM(events)
OVER(PARTITION BY url, event_id ORDER BY sec
RANGE BETWEEN 259200 PRECEDING AND CURRENT ROW
) AS rolling3daysEvents
FROM dailyAggregations
-- ORDER BY url, event_id, day
259200的值实际上是3x24x3600,所以设置了3天的范围,所以你可以设置任何你需要的实际滚动周期