Update table with data from same table (Moving average)

Question

I am trying to find the query for updating a table, so that it have a column with a moving average value.

Table: 
person_ID
test_date
test_results
test_MA_3

Unique(person_ID, test_date)

Currently the value for all rows is 0 for the column test_MA_3 (Moving Average, for 3 days)

Moving average: It is like a low pass filter, where for all positions the moving average is the average of the (in my case) 3 last days.

So får date 2013-03-12 the moving average is:

SUM(test_results)/COUNT(test_date) For test_date = (2013-03-12, + 2 previous days) GROUP BY person_ID

(The code is pseude code)

I tried to solve it using a sub query I wasnt able, even with a clean SELECT query I couldnt get the moving average calculated.

I tried:

SELECT a.test_date, a.person_ID, 
        (SELECT SUM(b.test_results)/COUNT(b.test_date) AS results 
         FROM table AS b WHERE b.person_ID = a.person_ID AND b.test_date <= a.test_date 
         ORDER BY b.test_date DESC LIMIT 3 
         GROUP BY b.person_ID
        ) AS out
FROM table 

AS

I might have some error in the query above, because I dont have phpmyadmin available right now. But i tried multiple versions of the query above, without any success.

Anyone that can help me solve this?

Answer 1

Try this:

SELECT a.test_date, a.person_ID, 
        (SELECT SUM(b.test_results)/COUNT(b.test_date) AS results 
         FROM table AS b WHERE b.person_ID = a.person_ID AND b.test_date <= a.test_date 
         GROUP BY b.person_ID
        ) AS out
FROM table