我有一些MongoDB的经验,我正在学习BigQuery.我正在尝试执行以下任务,我不知道如何使用BigQuery的标准sql来完成它.
我有一张包含以下数据的表格.它包含在不同网站网址上发生的事件.时间戳表示给定事件发生的时间.例如,第一行表示“事件’xx’发生在2016-10-18 15:55:16 UTC的网址’a.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天窗口中每个网址上发生的每个事件的数量.换句话说,我希望能够说出以下内容：
>“在网址’a.html’,在间隔期间[2016-10-18 00:00:00 UTC,2016-10-21 00:00:00 UTC],事件’xx’发生两次.”
>“在网址’a.html’上,在间隔期间[2016-10-19 00:00:00 UTC,2016-10-22 00:00:00 UTC],事件”xx“发生一次.”
>“在网址’a.html’,在间隔[2016-10-20 00:00:00 UTC,2016-10-23 00:00:00 UTC]期间,事件’xx’发生了零次.” (注意：这不需要作为行返回.没有这一行可能意味着事件发生了零次.)
一些注意事项：我的数据库每天包含超过10万行,并且事件的发生会有所不同.意思是,在1天内,事件’xx’将发生~10,000次,事件’zz’将发生~0-2次.
解决方法
以下是BigQuery Standard sql(参见
Enabling Standard SQL
我使用ts作为字段名称(而不是在您的示例中的时间戳)并假设此字段是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,sec
)
SELECT
url,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,day
259200的值实际上是3x24x3600,因此设置3天范围,因此您可以设置所需的实际滚动周期
