Confirmed users
95
edits
Telemetry/LongitudinalExamples: Difference between revisions
Added UNNEST example
Gfritzsche (talk | contribs) m (→Arrays: use subscript operator) |
Gfritzsche (talk | contribs) (Added UNNEST example) |
||
| Line 15: | Line 15: | ||
FROM longitudinal_v20160229 | FROM longitudinal_v20160229 | ||
WHERE os = 'Linux' | WHERE os = 'Linux' | ||
To expand arrays and maps and work on the data in them row-wise we can use <code>UNNEST(array)</code>: | |||
WITH lengths AS | |||
(SELECT os, greatest(-1, least(31, sl / (24*60*60))) AS days | |||
FROM longitudinal_v20160229 | |||
CROSS JOIN UNNEST(session_length, reason) AS t(sl, r) | |||
WHERE r = 'shutdown' OR r = 'aborted-session') | |||
SELECT os, days, count(*) AS count | |||
FROM lengths | |||
GROUP BY days, os ORDER BY days ASC | |||
Links: | Links: | ||
* [https://prestodb.io/docs/current/functions/array.html Documentation on array functions] | * [https://prestodb.io/docs/current/functions/array.html Documentation on array functions] | ||
* [https://prestodb.io/docs/current/sql/select.html#unnest <code>UNNEST</code> documentation] | |||
=== Maps === | === Maps === | ||