"Vanole, Mike" <Mike.Vanole@xxxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
