4 ms·
Ok, 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( zi
by fifilura 5y ago
Ok, 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