sql - 大查询 : how to perform rolling timestamp window group count that produces row for each day

Question

这是我在 StackOverflow 上提出并解决的问题的扩展 here .

我是 BigQuery 和 SQL 的新手，我想构建一个标准 SQL 查询，该查询将在 X 天的滚动时间窗口内对事件进行分组和计数。我的数据表如下所示:

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

我正在跟踪 url 上发生的事件。我想知道在 X 天的滚动时间段内每个 url 上每个事件发生了多少次。当我问这个问题时，我得到了一个很好的答案:

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 rolling4daysEvents
FROM dailyAggregations

其中 259200 是以秒为单位的 3 天 (3x24x3600)。据我了解，此查询创建了一个中间表，该表按天对事件进行分组和计数。它还将时间戳字段转换为其 unix 秒等价物。然后它使用以秒为单位的窗口总结事件。

现在这将生成一个包含正确运行总计的表格，但它不保证每个日期、URL 和事件都有一行。换句话说，如果给定 url 上从未发生给定事件的日期，结果表中将缺少日期。最重要的是，我是否可以修改上述查询(或构建不同的查询)以正确生成间隔中每个日期的 rolling4daysEvents 值？例如:像一个间隔定义为:

SELECT *
  FROM UNNEST (GENERATE_DATE_ARRAY('2016-08-28', '2016-11-06')) AS day
  ORDER BY day ASC

谢谢!

Answer 1

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
),
calendar AS (
  SELECT day
  FROM UNNEST (GENERATE_DATE_ARRAY('2016-08-28', '2016-11-06')) AS day
)
SELECT 
  c.day, url, event_id, events, 
  SUM(events) 
    OVER(PARTITION BY url, event_id ORDER BY sec 
      RANGE BETWEEN 259200 PRECEDING AND CURRENT ROW
  ) AS rolling4daysEvents
FROM calendar AS c
LEFT JOIN dailyAggregations AS a
ON a.day = c.day