53
edits
CloudServices/Sync/ReDash: Difference between revisions
Jump to navigation
Jump to search
added example of unnest
(→Query Examples: added query example) |
(added example of unnest) |
||
| Line 130: | Line 130: | ||
GROUP BY 1,2,3 | GROUP BY 1,2,3 | ||
ORDER 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> | </pre> | ||