Re: Calculating a moving average

"Vanole, Mike" <Mike(dot)Vanole(at)cingular(dot)com> writes:

> 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?

Unfortunately moving averages fall into a class of functions, called analytic
functions (at least that's what Oracle calls them) that are inherently hard to
model efficiently in SQL. Postgres doesn't have any special support for this
set of functions, so you're stuck doing it the inefficient ways that standard
SQL allows.

I think this is even hard to implement correctly using Postgres's extremely
extensible function support. Even if you implemented it in Perl or Python I
don't think there's any way to allocate a temporary static storage area for a
given call site. So your moving average function would behave strangely if you
called it twice in a given query.

But if you can work within that caveat it should be straightforward to
implement it efficiently in Perl or Python. Alternatively you can write a
plpgsql function to calculate the specific moving average you need that does
the select itself.

--
greg