MySQL Average Every 4 Rows

Question

I have a 10 million+ row table that looks like this:

    id | time_read  | value
    -----------------------
    9   1111111111   100
    9   1111111222   200
    9   1111111333   150
    9   1111111444   225

I want to AVG the value of every 4 rows. How do I do this in MySQL?

Also, secondary to my question, how might I do this if I wanted to avg the value of every 4 rows for all the data in a month and return that total for each month of each year in the table? For example, using the same table in my first question, ending up with:

    2008 | 12 | 500000
    2009 | 01 | 450000
    2009 | 02 | 475000

edit: In other words, the total of each average 4 rows grouped by year-month. Each time_read is 15 minutes apart.

I was doing something like this previously, but it wasn't accurate enough. I need to average every 4 rows and total that instead of taking the sum of all value's in a month and dividing by 4.

    SELECT DATE_FORMAT(FROM_UNIXTIME(time_read),'%Y %m') as tr,  
    DATE_FORMAT(FROM_UNIXTIME(time_read),'%Y') as year, 
    DATE_FORMAT(FROM_UNIXTIME(time_read),'%m') as month, SUM(value) as value 
    FROM table WHERE id = 9 
    GROUP BY tr

Answer 1

If you don't want to rely on your "id" column (it might not be sequential), you can just create your own variable to group by that changes every 4 rows, like so:

SET @rank = 0;
SELECT AVG(value), CEIL((@rank:=@rank+1)/4) AS rank FROM "your_table" GROUP BY rank;