I have two tables of time series data that I am trying to query and don't know how to properly do it.
The first table is time series data of device measurements. Each device is associated with a source and the data contains an hourly measurement. In this example there are 5 devices (101-105) with data for 5 days (June 1-5).
device_id date_time source_id meas
101 2016-06-01 00:00 ABC 105
101 2016-06-01 01:00 ABC 102
101 2016-06-01 02:00 ABC 103
...
101 2016-06-05 23:00 ABC 107
102 2016-06-01 00:00 XYZ 102
...
105 2016-06-05 23:00 XYZ 104
The second table is time series data of source measurements. Each source has three hourly measurements (meas_1, meas_2 and meas_3).
source_id date_time meas_1 meas_2 meas_3
ABC 2016-06-01 00:00 100 101 102
ABC 2016-06-01 01:00 99 100 105
ABC 2016-06-01 02:00 104 108 109
...
ABC 2016-06-05 23:00 102 109 102
XYZ 2016-06-01 00:00 105 106 103
...
XYZ 2016-06-05 23:00 103 105 101
I am looking for a query to get the data for a specified date range that grabs the device's measurements and its associated source's measurements. This example is the result for querying for device 101 from June 2-4.
device_id date_time source_id d.meas s.meas_1 s.meas_2 s.meas_3
101 2016-06-02 00:00 ABC 105 100 101 102
101 2016-06-02 01:00 ABC 102 99 100 105
101 2016-06-02 02:00 ABC 103 104 108 109
...
101 2016-06-04 23:00 ABC 107 102 109 102
The actual data set could get large with lets say 100,000 devices and 90 days of hourly measurements. So any help on properly indexing the tables would be appreciated. I'm using MySQL.
Aucun commentaire:
Enregistrer un commentaire