Re: Calculating a moving average

Unless I'm grossly misunderstanding the problem i think that a trigger written
in PL/pgsql would work fine. Something like this:

CREATE TABLE foo (
foo_id SERIAL primary key,
foo TEXT);

CREATE TABLE bar (
foo_id INTEGER references foo,
bar_id SERIAL primary key,
bar DOUBLE PRECISION NOT NULL);

CREATE TABLE bar_avg (
foo_id INTEGER primary key references foo,
bar_avg DOUBLE PRECISION);

CREATE OR REPLACE FUNCTION get_bar_avg() RETURNS TRIGGER AS '
DECLARE
bar_record RECORD;
x INTEGER;
y DOUBLE PRECISION := 0;
BEGIN
IF TG_OP = ''INSERT'' THEN
y := y + NEW.bar;
x := 1;
FOR bar_record IN SELECT * FROM bar LOOP
IF NEW.foo_id = bar_record.foo_id THEN
y := y + bar_record.bar;
x := x + 1;
END IF;
END LOOP;
y := y/x;
IF EXISTS(SELECT * FROM bar_avg WHERE foo_id = NEW.foo_id) THEN
UPDATE bar_avg
SET bar_avg.bar_avg = y
WHERE foo_id = NEW.foo_id;
ELSE
INSERT INTO bar_avg VALUES (NEW.foo_id, y);
END IF;
RETURN NEW;
ELSIF TG_OP = ''DELETE'' THEN
x := 0;
FOR bar_record IN SELECT * FROM bar LOOP
IF OLD.foo_id = bar_record.foo_id AND OLD.bar_id <>
bar_record.bar_id THEN
y := y + bar_record.bar;
x := x + 1;
END IF;
END LOOP;
IF x <> 0 THEN
y := y/x;
END IF;
UPDATE bar_avg
SET bar_avg.bar_avg = y
WHERE foo_id = OLD.foo_id;
RETURN OLD;
ELSE
y := y + NEW.bar;
x := 1;
FOR bar_record IN SELECT * FROM bar LOOP
IF OLD.bar_id <> bar_record.bar_id THEN
y := y + bar_record.bar;
x := x + 1;
END IF;
END LOOP;
y := y/x;
UPDATE bar_avg
SET bar_avg.bar_avg = y
WHERE foo_id = OLD.foo_id;
RETURN NEW;
END IF;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER get_bar_avg BEFORE INSERT OR DELETE OR UPDATE ON bar FOR EACH ROW
EXECUTE PROCEDURE get_bar_avg();

That should work,

regards,
matt

Quoting "Jim C. Nasby" <decibel(at)decibel(dot)org>:

> 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?"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>