I read a few posts regarding the moving average estimate in the mysql query, however it seems that my situation is a bit more complicated since there is no column in the table that I want to calculate on average. I need to count the number of rows for each group and present the moving average of this group.
I basically only have one column per table, and that is a DATETIME column. A table can contain multiple rows with the same date. I want to group the YEARWEEK table and COUNT (*) row numbers for each YEARWEEK group. This easy, tricky part is also to calculate a moving 4-week average
+-------------+ | DatoLagtTil | +-------------+ | 2012-11-01 | | 2012-10-25 | | 2012-10-25 | | 2012-10-11 | | 2012-10-04 | | ... | | ... | | ... | | ... | +-------------+
This is the output table I'm looking for to create - the hard part of the 4-week (YEARWEEK) moving average (Note: the example below is not based on the data from the example above)
+------------+------------+-----------+ | YEARWEEK | COUNT | m_average | +------------+------------+-----------+ | 201201 | 5 | 5 | | 201202 | 10 | 7.5 | | 201203 | 5 | 6.6 | | 201204 | 15 | 8.8 | | 201205 | 10 | 10 | | 201206 | 5 | 8.8 | | 201207 | 5 | 8.8 | +------------+------------+-----------+
I found some good sources explaining how to calculate a moving average, but I can't get them to work in my case. I hope you guys can help.
thanks
--- Status update ---
Tried a few solutions since I posted this question. I feel like approaching, but there is still something significant.
With the query below I am not getting any errors, however I am getting too high a Count (k.DatoLagtTil) and the same AVG (cnt) for all YEARWEEK groups ... I really believe that this is due to the WHERE clause, which I use
SELECT YEARWEEK(k.DatoLagtTil), COUNT(k.DatoLagtTil), AVG(cnt) FROM Kunder_db k, (SELECT COUNT(*) AS cnt FROM Kunder_db WHERE YEARWEEK(DatoLagtTil) BETWEEN YEARWEEK(DatoLagtTil) - 3 AND YEARWEEK(DatoLagtTil) GROUP BY YEARWEEK(DatoLagtTil)) x GROUP BY YEARWEEK(k.DatoLagtTil)