mysql simple moving average in last N hours

Question

There is a cron job that populates the rate every 5 minutes in the following table, Would appreciate some pointers on how to get the simple moving average in the last 6 hours of the rate column

The SQL table looks as follows:

id  rate        created_at          updated_at
1   11478.01    2020-08-29 03:11:28 2020-08-29 03:11:28
3   11481.28    2020-08-29 03:20:33 2020-08-29 03:20:33
4   11485.67    2020-08-29 03:29:53 2020-08-29 03:29:53
7   11486.57    2020-08-29 03:39:50 2020-08-29 03:39:50
8   11507.53    2020-08-29 03:48:10 2020-08-29 03:48:10
9   11515.37    2020-08-29 03:53:47 2020-08-29 03:53:47
10  11496.06    2020-08-29 04:01:06 2020-08-29 04:01:06
11  11499.58    2020-08-29 04:02:49 2020-08-29 04:02:49
12  11490.15    2020-08-29 04:22:00 2020-08-29 04:22:00
13  11509.22    2020-08-29 05:19:55 2020-08-29 05:19:55
14  11491.55    2020-08-29 05:26:34 2020-08-29 05:26:34

Should the difference between created_at and updated_at be 6 hours? — Ankit Jindal, Aug 29 '20 at 05:38
@ankit jindal there would be 12 inserts per hour into the table, ideally it should get the SMA over the last 6 hours, base on the value and created_at only — maximus 69, Aug 29 '20 at 05:42
Please see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Aug 29 '20 at 06:24

score 1 · Answer 1 · answered Aug 29 '20 at 10:50

You can use window functions and a range frame:

select
    t.*,
    avg(rate) over(
        order by created_at
        range between interval 6 hour preceding and current row
    ) avg_rate
from mytable t

Note that window functions are available in version 8.0 of MySQL only.

score -1 · Answer 2 · answered Aug 29 '20 at 05:48

-1

SELECT AVG(rate)
FROM TABLE
WHERE created_at <= DATE_SUB(NOW(), INTERVAL 6 HOUR);

The DATE_SUB() function subtracts a time value (or an interval) from a DATE or DATETIME value

You can read details about DATE_SUB and AVG

answered Aug 29 '20 at 05:48

Ankit Jindal

3,672
3
25
37

mysql simple moving average in last N hours

2 Answers2