BigQuery：如何在滚动时间戳窗口内对行进行分组和计数？

一尘不染

BigQuery：如何在滚动时间戳窗口内对行进行分组和计数？

sql

我在MongoDB上有一些经验，并且正在学习BigQuery。我正在尝试执行以下任务，但我不知道如何使用BigQuery的标准SQL来执行此任务。

我有一张包含以下数据的表。它包含在不同网站URL上发生的事件。时间戳表示给定事件发生的时间。例如，第一行表示“事件’xx’发生在世界标准时间2016-10-18
15:55:16的URL’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天滚动窗口中每个网址上发生的每个事件的数量。换句话说，我希望能够说以下几点：

“在时间间隔[2016-10-18 00:00:00 UTC，2016-10-21 00:00:00 UTC）的url’a.html’上，事件’xx’发生了两次。”
“在时间间隔[2016-10-19 00:00:00 UTC，2016-10-22 00:00:00 UTC）的url’a.html’上，事件’xx’发生了一次。”
“在时间间隔[2016-10-20 00:00:00 UTC，2016-10-23 00:00:00 UTC）的url’a.html’上，事件’xx’发生了0次。” （注意：不需要将它作为一行返回。缺少此行可以表示该事件发生了0次。）

一些注意事项：我的数据库每天包含超过10万行，并且事件的发生情况各不相同。意思是，在1天之内，事件“ xx”将发生约10,000次，事件“
zz”将发生约0-2次。

鉴于我有限的SQL知识，我不想为结果表提供结构，因为我认为这可能会错误地限制可能的答案。谢谢！

阅读 151

2021-03-10

共1个答案

一尘不染

以下是适用于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天范围，因此您可以设置所需的任何实际滚动周期

2021-03-10