CODEWITHSUNDEEP
mysqlsql
Vincent L
Vincent L

Reputation: 739

MySQL- Average of 15 minutes intervals

I have the following table:

╔════════════╦══════════╦═══════╦══════╗
║    DATE    ║   TIME   ║ Price ║ Avg  ║
╠════════════╬══════════╬═══════╬══════╣
║ 01/01/2000 ║ 00:00:00 ║   1   ║      ║
║ 01/01/2000 ║ 00:05:00 ║   2   ║      ║
║ 01/01/2000 ║ 00:10:00 ║   3   ║      ║
║ 01/01/2000 ║ 00:15:00 ║   4   ║      ║
║ 01/01/2000 ║ 00:20:00 ║   5   ║      ║
║ 01/01/2000 ║ 00:25:00 ║   6   ║      ║
║ 01/01/2000 ║ 00:30:00 ║   7   ║      ║
║ 01/01/2000 ║ 00:35:00 ║   8   ║      ║
║ 01/01/2000 ║ 00:40:00 ║   9   ║      ║
║ 01/01/2000 ║ 00:45:00 ║   10  ║      ║
║ 01/01/2000 ║ 00:50:00 ║   11  ║      ║
║ 01/01/2000 ║ 00:55:00 ║   12  ║      ║
║ 01/01/2000 ║ 01:00:00 ║   13  ║      ║
╚════════════╩══════════╩═══════╩══════╝

And I want to fill the column Avg with a moving average of price of the last 15 minutes (not including the last average), so that the outcome is:

╔════╦════════════╦══════════╦═══════╦══════╗
║ ID ║    DATE    ║   TIME   ║ Price ║ Avg  ║
╠════╬════════════╬══════════╬═══════╬══════╣
║ 1  ║ 01/01/2000 ║ 00:00:00 ║   10  ║  10  ║
║ 2  ║ 01/01/2000 ║ 00:05:00 ║   2   ║      ║
║ 3  ║ 01/01/2000 ║ 00:10:00 ║   6   ║      ║
║ 4  ║ 01/01/2000 ║ 00:15:00 ║   4   ║  4   ║ <-- Average of 2,6,4
║ 5  ║ 01/01/2000 ║ 00:20:00 ║   7   ║      ║
║ 6  ║ 01/01/2000 ║ 00:25:00 ║   6   ║      ║
║ 7  ║ 01/01/2000 ║ 00:30:00 ║   5   ║  6   ║ <-- Average of 7,6,5
║ 8  ║ 01/01/2000 ║ 00:35:00 ║   2   ║      ║
║ 9  ║ 01/01/2000 ║ 00:40:00 ║   2   ║      ║
║ 10 ║ 01/01/2000 ║ 00:45:00 ║   2   ║  2   ║ <-- Average of 2,2,2
║ 11 ║ 01/01/2000 ║ 00:50:00 ║   10  ║      ║
║ 12 ║ 01/01/2000 ║ 00:55:00 ║   12  ║      ║
║ 13 ║ 01/01/2000 ║ 01:00:00 ║   2   ║  8   ║ <-- Average of 10,12,2
╚════╩════════════╩══════════╩═══════╩══════╝

NOTE: While there is ALWAYS an entry every 5 minutes, there could be additional entries in between(i.e.: There could be a price at 00:32:00).

What I have right now is the following:

UPDATE table AS t 
INNER JOIN 
(SELECT AVG(Price) as average,
    floor((unix_timestamp(CONCAT(date, ' ', time)))/900 ) as timekey
FROM table
GROUP BY timekey ) t1 
ON t.ID = t1.ID
SET 15_MIN_AVERAGE = t1.average

But this seems to group the entries that are on the 15 minutes and the next ones, instead of the previous ones like I want.

Any help is greatly appreciated.

Thanks!

Upvotes: 1

Views: 2349

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You can do what you want with a correlated subquery:

select t.*,
       (select avg(t2.price)
        from table t2
        where t2.time <= t.time and t2.time >= date_sub(t.time, interval 15 minute)
       ) as avgprice
from table t;

You can then do tbe update that you want, using this in a join:

update table t join
       (select t.*,
               (select avg(t2.price)
                from table t2
                where t2.time <= t.time and t2.time >= date_sub(t.time, interval 15 minute)
               ) as avgprice
        from table t
       ) tt
       on t.id = tt.id
    set t.15_MIN_AVERAGE = ttavgprice
    where minute(t.time) in (0, 15, 30, 45);

Upvotes: 4

Related Questions