Bigquery SQL用于滑动窗口聚合

Question

嗨，我有一张看起来像这样的桌子

Date         Customer   Pageviews
2014/03/01   abc          5
2014/03/02   xyz          8
2014/03/03   abc          6

我想要按周分组的页面视图聚合，但显示过去30天的聚合-（每周滑动窗口聚合，窗口大小为30天）

我正在使用Google bigquery

编辑：戈登-对您的“客户”发表评论，实际上我需要的是稍微复杂一点的，这就是为什么我在上表中包括客户的原因。我希望获得每周30天的浏览量>
n的客户数量。像这样的东西

Date        Customers>10 pageviews in 30day window
2014/02/01  10
2014/02/08  5
2014/02/15  6
2014/02/22  15

但是，为了简单起见，如果我能够获得页面浏览量的滑动窗口汇总而完全忽略客户，我将按我的方式工作。像这样的东西

Date        count of pageviews in 30day window
2014/02/01  50
2014/02/08  55
2014/02/15  65
2014/02/22  75

Answer 1

这个怎么样：

SELECT changes + changes1 + changes2 + changes3 changes28days, login, USEC_TO_TIMESTAMP(week)
FROM (
  SELECT changes,
         LAG(changes, 1) OVER (PARTITION BY login ORDER BY week) changes1,
         LAG(changes, 2) OVER (PARTITION BY login ORDER BY week) changes2,
         LAG(changes, 3) OVER (PARTITION BY login ORDER BY week) changes3,
         login,
         week
  FROM (
    SELECT SUM(payload_pull_request_changed_files) changes, 
           UTC_USEC_TO_WEEK(created_at, 1) week,
           actor_attributes_login login,
    FROM [publicdata:samples.github_timeline]
    WHERE payload_pull_request_changed_files > 0
    GROUP BY week, login
))
HAVING changes28days > 0

对于每个用户，它计算他们每周提交了多少更改。然后使用LAG（）我们可以窥视下一行，他们在-1，-2和-3周提交了多少更改。然后，我们只需要加上这4周的时间，就可以查看过去28天内提交了多少更改。

现在，您可以将所有内容包装在新查询中，以过滤具有> X的用户并对其进行计数。