Re: Calculating a moving average - Mailing list pgsql-general

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@decibel.org>:

> On Fri, Jan 21, 2005 at 12:53:45AM -0500, Greg Stark wrote:
> > "Vanole, Mike" <Mike.Vanole@cingular.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@decibel.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@postgresql.org)
>