We're collecting lots of sensor data and logging them to a postgres DB.
Basic schema - cut down:
id | BIGINT PK
sensor-id| INT FK
location-id | INT FK
sensor-value | NUMERIC(0,2)
last-updated | TIMESTAMP_WITH_TIMEZONE
I'm trying to get the biggest change in sensor data in the last day. By that I mean, out of all the sensors, sensor ids 4,5,6,7 changed the biggest compared to the previous day. Before that, I'm trying to get a SQL query to figure out the delta between last reading and latest reading.
I thought maybe the lead and lag functions would help, but my query doesn't quite give me the result I was after:
SELECT
srd.last_updated,
spi.title,
lead(srd.value) OVER (ORDER BY srd.sensor_id DESC) as prev,
lag(srd.value) OVER (ORDER BY srd.sensor_id DESC) as next
FROM
sensor_rt_data srd
join sensor_prod_info spi on srd.sensor_id = spi.id
where srd.last_updated >= NOW() - '1 day'::INTERVAL -- current_date - 1
ORDER BY
srd.last_updated DESC
Simple dataset - making this up now because i can't login to the DB right now:
id|sensor,location,value,updated
1|1,1,24,'2017-04-28 19:30'
2|1,1,22,'2017-04-27 19:30'
3|2,1,35,'2017-04-28 19:30'
4|2,1,33,'2017-04-28 08:30'
5|2,1,31,'2017-04-27 19:30'
6|1,1,25,'2017-04-26 19:30'
Forgetting the join (that's for the user-friendly sensor tag name field staff need and the location), how do I workout which sensor has reported the biggest change in temperature over a time-series when they're grouped by sensor-id?
I'd be expecting:
updated,sensor,prev,next
'2017-04-28 19:30',1,24,22
'2017-04-28 19:30',2,33,31
(then from that, I can subtract and order to workout the top 10 sensors that have changed)
I noticed that Postgres 9.6 has some other functions too but want to try get Lead/Lag working first.
Window function aren't a best fit for this kind of task. Try this:
select sensor, max(value)-min(value) as value_change
from sensordata
where updated>=?-'1 day'::interval
group by sensor
order by value_change desc
limit 1;
Not much use for indexes besides updated for this kind of query. It would be probably possible to use a specially crafted index if you would be looking the largest change for a calendar day instead of last 24 hours.