How do I calculate a moving average using MySQL?

Question

I need to do something like:

SELECT value_column1 
FROM table1 
WHERE datetime_column1 >= '2009-01-01 00:00:00' 
ORDER BY datetime_column1;

Except in addition to value_column1, I also need to retrieve a moving average of the previous 20 values of value_column1.

Standard SQL is preferred, but I will use MySQL extensions if necessary.

Answer 1

This is just off the top of my head, and I'm on the way out the door, so it's untested. I also can't imagine that it would perform very well on any kind of large data set. I did confirm that it at least runs without an error though. :)

SELECT
     value_column1,
     (
     SELECT
          AVG(value_column1) AS moving_average
     FROM
          Table1 T2
     WHERE
          (
               SELECT
                    COUNT(*)
               FROM
                    Table1 T3
               WHERE
                    date_column1 BETWEEN T2.date_column1 AND T1.date_column1
          ) BETWEEN 1 AND 20
     )
FROM
     Table1 T1

Answer 2

When I had a similar problem, I ended up using temp tables for a variety of reasons, but it made this a lot easier! What I did looks very similar to what you're doing, as far as the schema goes.

Make the schema something like ID identity, start_date, end_date, value. When you select, do a subselect avg of the previous 20 based on the identity ID.

Only do this if you find yourself already using temp tables for other reasons though (I hit the same rows over and over for different metrics, so it was helpful to have the small dataset).

Answer 3

Tom H's approach will work. You can simplify it like this if you have an identity column:

SELECT T1.id, T1.value_column1, avg(T2.value_column1)
FROM table1 T1
INNER JOIN table1 T2 ON T2.Id BETWEEN T1.Id-19 AND T1.Id