53
edits
Changes
added example of unnest
GROUP BY 1,2,3
ORDER BY 1,2,3
</pre>
This example is how you would unpack the engines array into it's own table to then query:
<pre>
WITH engine_errors AS (
SELECT
uid,
date_trunc('day', from_unixtime("when"/1000)) AS date,
engine
FROM sync_summary
/* The CROSS JOIN UNNEST will join the array to each row */
CROSS JOIN UNNEST(engines) AS t (engine)
--LIMIT 1000000
)
SELECT
engine.name AS engine_name,
SUM(
CASE
WHEN engine.failureReason IS NOT NULL THEN 1
ELSE 0
END
) AS errors
FROM engine_errors
WHERE date >= timestamp '{{start_date}}' AND date <= timestamp '{{end_date}}'
GROUP BY engine.name
</pre>
