Re: Calculating a moving average

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Calculating a moving average
Date: 2005-01-21 06:13:48
Message-ID: 20050121061348.GR67721@decibel.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2005-01-21 06:14:00 Re: Multiline plpython procedure
Previous Message Greg Stark 2005-01-21 05:53:45 Re: Calculating a moving average