Make a plpgsql function which will iterate over the rows on which the moving average is to be done (FOR row IN SELECT), of course use the correct order, then use an array as a FIFO, add a row to the moving average and push it, pop the old one and substract it.
Roundoff errors will bite your nether regions.
I spose the purpose of this is not to fetch the whole thing so that your moving average will not have one result row per source row (or you'd do it in the application), thus you can pre-shrink your dataset by putting some avg() and group by in your source select.
Hi, I need to calculate a moving average and I would like to do it with SQL, or a Pg function built for this purpose. I'm on Pg 7.4. Is this possible in Pg without a bunch of self joins, or is there a funtion available? Thanks, Mike
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
