dies ist eine Erweiterung zu einer Frage, die ich auf StackOverflow @ gestellt und gelöst habHie.

Ich bin ein Anfänger in BigQuery und SQL und wollte eine Standard-SQL-Abfrage erstellen, die Ereignisse in einem fortlaufenden Zeitfenster von X Tagen gruppiert und zählt. Meine Datentabelle sieht so aus:

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

Ich verfolge Ereignisse, die auf URLs auftreten. Ich möchte wissen, wie oft jedes Ereignis auf jeder URL während eines fortlaufenden Zeitraums von X Tagen aufgetreten ist. Als ich diese Frage stellte, bekam ich eine großartige Antwort:

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

where 259200 ist 3 Tage in Sekunden (3 x 24 x 3600). Soweit ich weiß, erstellt diese Abfrage eine Zwischentabelle, in der Ereignisse nach Tag gruppiert und gezählt werden. Es konvertiert auch das Zeitstempelfeld in sein zweites Unix-Äquivalent. Anschließend werden die Ereignisse in einem Fenster zusammengefasst, das in Sekunden gemessen wird.

Jetzt wird eine Tabelle mit korrekten laufenden Summen erstellt, es wird jedoch nicht für jedes Datum, jede URL und jedes Ereignis eine Zeile garantiert. Mit anderen Worten, es fehlen Daten in der resultierenden Tabelle, wenn es Daten gab, an denen ein bestimmtes Ereignis in einer bestimmten URL nie aufgetreten ist. Fazit: Kann ich die obige Abfrage ändern (oder eine andere Abfrage erstellen), mit der die Werte für rolling4daysEvents für jedes Datum in einem Intervall korrekt erstellt werden? zB: wie ein Intervall definiert als:

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

Vielen Dank