Estamos recopilando muchos datos de sensores y registrándolos en una base de datos postgres.
Esquema básico - reducido:
id | BIGINT PK sensor-id| INT FK location-id | INT FK sensor-value | NUMERIC(0,2) last-updated | TIMESTAMP_WITH_TIMEZONEEstoy tratando de obtener el mayor cambio en los datos del sensor en el último día. Con eso quiero decir que, de todos los sensores, los ID de sensor 4,5,6,7 cambiaron más en comparación con el día anterior. Antes de eso, estoy tratando de obtener una consulta SQL para averiguar el delta entre la última lectura y la última lectura.
Pensé que tal vez las funciones de adelanto y retraso ayudarían, pero mi consulta no me da el resultado que buscaba:
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 DESCConjunto de datos simple: estoy inventando esto ahora porque no puedo iniciar sesión en la base de datos en este momento:
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'Olvidando la unión (eso es para el nombre de la etiqueta del sensor fácil de usar que el personal de campo necesita y la ubicación), ¿cómo puedo averiguar qué sensor ha informado el mayor cambio de temperatura durante una serie de tiempo cuando están agrupados por ID de sensor?
estaría esperando:
updated,sensor,prev,next '2017-04-28 19:30',1,24,22 '2017-04-28 19:30',2,33,31(luego de eso, puedo restar y ordenar para entrenar los 10 sensores principales que han cambiado)
Me di cuenta de que Postgres 9.6 también tiene otras funciones, pero quiero intentar que Lead/Lag funcione primero.
La función de ventana no es la mejor opción para este tipo de tarea. Prueba esto:
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; No sirve de mucho para los índices, además de updated para este tipo de consulta. Probablemente sería posible usar un índice especialmente diseñado si buscara el cambio más grande para un día calendario en lugar de las últimas 24 horas.