Database Reference
In-Depth Information
OVER (ORDER BY start_date) AS num_1,
LAG(num_0, 2, INTEGER(-1))
OVER (ORDER BY start_date) AS num_2
FROM (
SELECT
DATE(start) AS start_date,
INTEGER(COUNT(1)) num_0
FROM [bigquery-e2e:ch10.sessions]
GROUP BY start_date))
ORDER BY start_date
This method is easy to follow but there is one important caveat. If there are
missing days from the inner query, then the moving average will be wrong
because the missing day will not be treated as zero, rather the first nonzero
day before it will be used. If your data can suffer from gaps, you may want
to consider forming the union of the inner query and table containing a zero
entry for every day of interest to ensure there are no gaps.
Finding Concurrency
Now continue with the session dataset used in the previous recipe to
illustrate an interesting way to leverage CROSS JOIN. The goal is to
compute the maximum number of concurrent sessions observed for each
hour of a given day, performing the calculation at minute granularity.
The basic idea is to determine all the sessions that were live during a given
minute. You can do this by cross-joining each session with every minute of
the day and then discarding all the minutes that do not fall within the start
and end time of the minute. Then group the values by minute and count the
occurrences. Finally group the minutes by hour and take the max count over
all the minutes in the given hour.
SELECT
INTEGER(minute / 60) AS hour,
MAX(active) AS active
FROM (
SELECT m.index AS minute, COUNT(s.user_id) AS active
FROM (
SELECT
user_id,
