I am storing some telemetry data from some sensors in an SQL table (PostgreSQL) and I want to know how I can I write a query that will group the telemetry data using relational information from two other tables.
I have one table which stores the telemetry data from the sensors. This table contains three fields, one for the timestamp, one for the sensor ID, one for the value of the sensor at that time. The value column is an incrementing count (it only increases)
Telemetry table
| timestamp | sensor_id | value |
|---|---|---|
| 2022-01-01 00:00:00 | 5 | 3 |
| 2022-01-01 00:00:01 | 5 | 5 |
| 2022-01-01 00:00:02 | 5 | 6 |
| ... | ... | ... |
| 2022-01-01 01:00:00 | 5 | 675 |
I have another table which stores the state of the sensor, whether it was stationary or in motion and the start/end dates of that particular state for that sensor:
**Status **table
| start_date | end_date | status | sensor_id |
|---|---|---|---|
| 2022-01-01 00:00:00 | 2022-01-01 00:20:00 | in_motion | 5 |
| 2022-01-01 00:20:00 | 2022-01-01 00:40:00 | stationary | 5 |
| 2022-01-01 00:40:00 | 2022-01-01 01:00:00 | in_motion | 5 |
| ... | ... | ... | ... |
The sensor is located at a particular location. The Sensor table stores this metadata:
**Sensor **table
| sensor_id | location_id |
|---|---|
| 5 | 16 |
In the final table, I have the shifts that occur in each location.
**Shift **table
| shift | location_id | occurrence_id | start_date | end_date |
|---|---|---|---|---|
| A Shift | 16 | 123 | 2022-01-01 00:00:00 | 2022-01-01 00:30:00 |
| B Shift | 16 | 124 | 2022-01-01 00:30:00 | 2022-01-01 01:00:00 |
| ... | ... | ... | ... | ... |
I want to write a query so that I can retrieve telemetry data that is grouped both by the shifts at the location of the sensor as well as the status of the sensor:
| sensor_id | start_date | end_date | status | shift | value_start | value_end |
|---|---|---|---|---|---|---|
| 5 | 2022-01-01 00:00:00 | 2022-01-01 00:20:00 | in_motion | A Shift | 3 | 250 |
| 5 | 2022-01-01 00:20:00 | 2022-01-01 00:30:00 | stationary | A Shift | 25 | 325 |
| 5 | 2022-01-01 00:30:00 | 2022-01-01 00:40:00 | stationary | B Shift | 325 | 490 |
| 5 | 2022-01-01 00:40:00 | 2022-01-01 01:00:00 | in_motion | B Shift | 490 | 675 |
As you can see, the telemetry data would be grouped both by the information contained in the Shift table as well as the Status table. Particularly, if you notice the sensor was in a stationary status between 2022-01-01 00:20:00 and 2022-01-01 00:40:00, however if you notice the 2nd and 3rd rows in the above table, this is cut into two rows based on the fact that the shift had changed at 2022-01-01 00:30:00.
Any idea about how to write a query that can do this? That would be really appreciated, thanks!