Database Reference
In-Depth Information
This query is fairly complicated, but it has the advantage that you do not
need to run and wait for a query job for each field that you need to analyze.
The pivot by field type in the top-level
SELECT
clause is not actually
required, so you could replace that with a simple aggregation by field type
and simplify the query a little.
Trailing Averages
With the introduction of window functions, it is fairly easy to generate
trailing (or other moving averages). The main issue to deal with is some
of the restrictions around the use of window functions. To demonstrate
how to construct the query, we have created a synthetic dataset
(
bigquery-e2e:ch10.sessions
) corresponding to user sessions on
some hypothetical service. Each record has a user ID and a start and end
timestamp.
[
{"name": "user_id", "type": "string"},
{"name": "start", "type": "timestamp"},
{"name": "end", "type": "timestamp"}
]
The metric we are going to smooth is the daily active users. We start with
an inner query that computes the base metric and then wrap it in a query
that uses window functions to fetch trailing rows. The outermost query
combines the trailing values into a weighted average paying attention to
missing values.
SELECT
start_date,
((num_0 +
IF(num_1 > -1, num_1, num_0) * 0.5 +
IF(num_2 > -1, num_2, num_0) * 0.25) /
1.75) AS smooth_num
FROM (
SELECT
start_date,
num_0,
LAG(num_0, 1, INTEGER(-1))
