BigQuery：如何执行滚动时间戳窗口组计数，该计数每天产生一行

BigQuery：如何执行滚动时间戳窗口组计数，该计数每天产生一行

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 rolling4daysEventsFROM calendar AS cLEFT JOIN dailyAggregations AS aON a.day = c.day