SQL query to find deltas between multiple series in time-series data

user_594211 0
Viewed 119

I have IoT time-series data in Timescale DB (PostgreSQL) that looks like this:

time device_id key value
2022-01-28 16:49:51+00 device_05 sensor_1 0
2022-01-28 16:49:51+00 device_05 sensor_2 0
2022-01-28 16:50:16+00 device_01 sensor_1 1
2022-01-28 16:50:16+00 device_01 sensor_2 1
2022-01-28 16:50:24+00 device_03 sensor_1 0
2022-01-28 16:50:24+00 device_03 sensor_2 1
2022-01-28 16:50:52+00 device_06 sensor_2 0
2022-01-28 16:50:52+00 device_06 sensor_1 0
2022-01-28 16:50:56+00 device_15 sensor_2 0
2022-01-28 16:50:56+00 device_15 sensor_1 0

I would like to write a SQL query to find discrepencies among sensors on the same device at a given time. In the above example data, device_03 has sensors that are reporting conflicting values.

time device_id delta
2022-01-28 16:49:51+00 device_05 0
2022-01-28 16:50:16+00 device_01 0
2022-01-28 16:50:24+00 device_03 1
2022-01-28 16:50:52+00 device_06 0
2022-01-28 16:50:56+00 device_15 0

Each device reports data from all of its sensors at the same time, so let's assume that the timestamps will be the same. (However, if it's not terribly more complicated, it would be nice to handle the case where this is not guaranteed, in which case the last known value applies.)

timescaledb iot postgresql sql
1 Answers

Aha, it was a matter of combining GROUP BY with COUNT(DISTINCT())!

SELECT
    "time",
    device_id,
    COUNT(DISTINCT(value)) - 1 AS "delta"
FROM
    data
WHERE measurement = 'sensors'
GROUP BY 1, 2
ORDER BY 1
user_5942110
Related