4 ms·
I wonder if the array functions in the presto engine (used for example in Amazon Athena) should be able to handle this too? https://prestodb.io/docs/0.217/func
by fifilura 5y ago
I wonder if the array functions in the presto engine (used for example in Amazon Athena) should be able to handle this too?
https://prestodb.io/docs/0.217/functions/array.html https://prestodb.io/docs/0.217/functions/array.html
- fifilura 5y agoOk, a 15 minute hack. It may be possible to make it look nicer. And it looked better in TFA, so good job! SELECT device_id, reduce( zip_with( array_sort(zip(array_agg(ts), array_agg(val))), slice(array_sort(zip(array_agg(ts), array_agg(val))),2, 5000), (a,b) -> abs(a.field1 - b.field1)) , 0, (s, x) -> s + COALESCE(x,0), s -> s) as res FROM ( VALUES (1, 2, 1), (1, 3, 0), (1, 9, 3), (1, 3, 4), (2, 2, 1), (2, 8, 0), (3, 4, 3) ) AS t(device_id, val, ts) GROUP BY device_id device_id res 1 14 3 0 2 6