MySQL - Average values of data for every 3 hours?

Question

I currently have two tables in mysql that are collecting data. One collects every 3 hours and the other every 15 minutes. I am trying to create a forecast using this data using machine learning techniques which requires the data to be of the same time interval.

My question is, how can I get the average value for every 3 hours from both tables into a new table that organises them by date and hour? So of a format like:

Date / Hour / Table 1 3-hour average / Table 2 3-hour average

Table 1 (every 3 hours): datetime(timestamp)/level(double)

Table 2 (every 15 minutes): datetime(timestamp)/value(double)

I have managed to create a table that gives me the average per day, but I'd prefer something more accurate!

This is the statement I've already got for daily averages:

SELECT DAY(`datetime`),
AVG(`level`) AS `Table 1 Average` ,
(SELECT AVG(`value`) AS `Table 2 Daily Average` 
FROM `table2` 
WHERE DAY(`datetime`) = DAY(table1.datetime)
GROUP BY DAY(`datetime`))
FROM `table1` GROUP BY DAY(`datetime`);

Thanks!

Gordon Linoff Gordon Linoff · Accepted Answer · 2015-01-14T12:26:55

You really just need to change the subquery. I think this may do way you want:

SELECT t1.*,
       SELECT AVG(t2.`value`) 
        FROM `table2` 
        WHERE t2.datetime <= t1.datime and
              t2.datetime >= date_sub(t1.datetime, interval 3 hour)
       ) as t2_moving_average
FROM `table1` t1;

MySQL - Average values of data for every 3 hours?

1 Answers