SQL perform AVG of a column every X rows

Question

I have a table that has the following (relevant) columns: id_mi, date and value. I want to build a graph using the values in the "value" column in the y axis and the dates in the x axis, but since they are a lot I would like that the points on my graph were the average of X rows.

Let's say X = 10 for this example:

What I'm trying to do is to get the first 10 values on my table, calculate the average value and store it in a row, than the following row will contain the average of the values from 11 to 20 and so on.

Basically I need to "compress" 10 rows in a single one that has the average value for the "value" column.

I'm using Postgres 9.2

Answer 1

You can do this using the window functions:

select avg(value) over (order by date
                        rows between 9 preceding and current row) as MovingAvg10
from t;

If you wanted to be sure that you start on the 10th row, you could do:

select (case when row_number() over (order by date) >= 10
             then avg(value) over (order by date
                                   rows between 9 preceding and current row
                                  )
        end) as MovingAvg10
from t;

EDIT:

Your revised question is much easier:

select ((seqnum - 1) / 10) as whichgroup, avg(value)
from (select row_number() over (order by date) as seqnum, t.*
      from table t
     ) t
group by ((seqnum - 1) / 10)
order by 1;

Postgres does integer division, but if you wanted to be explicit, you could do: trunc((seqnum - 1) / 10) to get the group number.