jeudi 23 juin 2016

Join Tables Based on Time and ID

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