nrlakin nrlakin - 1 year ago 62
SQL Question

SQL query to group time series measurements into single record?

I have a table in a MySQL database containing measurements from multiple sensors in roughly 30s intervals, each with a timestamp, like this:

<timestamp, source_device, measurement>
<2016-04-01 01:00:00.1234, sensor 1, 1.234>
<2016-04-01 01:00:01.5467, sensor 2, 2.543>
<2016-04-01 01:00:00.8376, sensor 3, 1.318>
<2016-04-01 01:00:31.7365, sensor 2, 1.927>
<2016-04-01 01:00:30.2610, sensor 1, 2.701>
<2016-04-01 01:00:30.9576, sensor 3, 1.853>

Note that the first 3 records are at roughly the same time, although the timestamps do not match exactly. I need to export summary CSVs and display the data on a frontend console. For example, a client might request all data for a certain time period from sensors 2 and 3, in five minute intervals, in a downloadable CSV. I would like to execute this as a single query with a common (rounded) timestamp:

<timestamp, sensor_2_measurement, sensor_3_measurement>
<2016-04-01 01:00, 2.543, 1.318>
<2016-04-01 01:05, 3.015, 1.692>
<2016-04-01 01:10, 2.752, 1.494>

At the moment, I am doing separate queries for each sensor, but this requires me to load the full query results into memory before constructing a CSV and puts some strain on my application stitching together the CSV rows. Is there a way to boil this down to a single query? Bonus points for a sqlalchemy implementation, but I can work with a pure SQL query.

Answer Source

The following gets the average value for each time unit rounded to 5 minute intervals:

select FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp)/(24*60/5))*(24*60/5)) as ts,
       avg(case when source_device = 1 then measurement end) as sensor_1,
       avg(case when source_device = 2 then measurement end) as sensor_2,
       avg(case when source_device = 3 then measurement end) as sensor_3
from t
group by ts;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download