Re: Calculating a moving average

On Fri, Jan 21, 2005 at 12:53:45AM -0500, Greg Stark wrote:
> "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.

If you're feeling adventurous, you might look at Oracle's documentation
on their analytic functions and see if you can come up with something
generic for PostgreSQL. Even if you only do a moving average function it
would be a good start.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"