53
edits
(→Engine Record: added q2 and a2) |
(→Query Examples: added query example) |
||
Line 79: | Line 79: | ||
WHERE failurereason IS NOT NULL | WHERE failurereason IS NOT NULL | ||
) | ) | ||
SELECT | SELECT | ||
name, | name, | ||
Line 85: | Line 84: | ||
FROM errors | FROM errors | ||
GROUP BY name | GROUP BY name | ||
</pre> | |||
The next example shows how to handle unix time stamps and how to use the Re:Dash date picker. It's important to either cast the date or to use the type constructor like below or you won't be able to use any operators on the date which is required for the date picker. | |||
<pre> | |||
WITH syncs AS ( | |||
SELECT | |||
/* convert date from unix timestamp */ | |||
date_trunc('day', from_unixtime("when"/1000)) AS day, | |||
status.service AS service | |||
FROM | |||
sync_summary | |||
) | |||
SELECT day, status, volume | |||
FROM ( | |||
SELECT | |||
day, | |||
'success' as status, | |||
SUM( | |||
CASE | |||
WHEN service IS NULL THEN 1 | |||
ELSE 0 | |||
END | |||
) AS volume | |||
FROM syncs | |||
GROUP BY day | |||
UNION ALL | |||
SELECT | |||
day, | |||
'failed' as status, | |||
SUM( | |||
CASE | |||
WHEN service IS NOT NULL THEN 1 | |||
ELSE 0 | |||
END | |||
) AS volume | |||
FROM syncs | |||
GROUP BY day | |||
) | |||
/* date picker */ | |||
WHERE day >= timestamp '{{start_date}}' AND day <= timestamp '{{end_date}}' | |||
GROUP BY 1,2,3 | |||
ORDER BY 1,2,3 | |||
</pre> | </pre> |
edits