4 ms·
Note that your second example has a misplaced pair of parens: WITH temperature_delta_past_day AS ( SELECT device_id, abs(val - lag(val) OVER (P
by EE84M3i 5y ago
Note that your second example has a misplaced pair of parens:
WITH temperature_delta_past_day AS
(
SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta
FROM measurements
WHERE ts >= now() - '1 day'::interval) calc_delta
)
SELECT device id, sum(abs_delta) as volatility
FROM temperature_delta_past_day
GROUP BY device_id;
should probably be
WITH temperature_delta_past_day AS
(
SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta
FROM measurements
WHERE ts >= now() - '1 day'::interval
)
SELECT device id, sum(abs_delta) as volatility
FROM temperature_delta_past_day
GROUP BY device_id;
- jpitz 5y agoYou're correct - I was in a hurry to express the idea, and I failed to check the where clause.