Re: Sum of multiplied deltas

Hello,

Finally I used a function below which works well. Only one problem is
left: It polutes the buffer cache because of the cursor. Any idea to get
rid of this behavior?

BTW: WINDOWING FUNCTION of 8.4 should help but noone could provide an
examples how this could work. Any further comments how to implement it?

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/

CREATE TYPE PS AS
(
   sum_m1 double precision,
   sum_m2 double precision
);

DROP FUNCTION getSum();
CREATE OR REPLACE FUNCTION getSum(IN start_ts timestamp with time
zone, IN stop_ts timestamp with time zone) RETURNS PS AS $$
DECLARE
   curs CURSOR FOR
   SELECT
     *
   FROM
     log_entries
   WHERE
     datetime >= start_ts
     AND datetime <= stop_ts
   ORDER BY
     datetime
   ;
   row log_entries%ROWTYPE;
   i bigint = 0;
   datetime_old timestamp with time zone;
   old double precision;
   sum_m1 double precision = 0;
   sum_m2 double precision = 0;
   psum PS;
BEGIN
   OPEN curs;
   LOOP
     FETCH curs INTO row;
     EXIT WHEN NOT FOUND;
     IF row.col IS NOT NULL THEN
       IF i > 0 THEN
         sum_m1 = sum_m1 + (row.col - old) * 0.01 * row.col2;
         sum_m2 = sum_m2 + EXTRACT('epoch' FROM row.datetime - datetime_old) * row.col3;
       END IF;
       i = i + 1;
       old = row.old;
       datetime_old = row.datetime;
     END IF;
   END LOOP;
   CLOSE curs;
   psum.sum_m1 = sum_m1;
   psum.sum_m2 = sum_m2;
   RETURN psum;
END;
$$ LANGUAGE plpgsql;


On Mon, 8 Jun 2009, Gerhard Wiesinger wrote:

> Hello!
>
> I've the following data:
> datetime | val1 | val2
> time1    |    4 | 40%
> time2    |    7 | 30%
> time3    |   12 | 20%
> ...
>
> I'd like to sum up the following:
>
> (7-4)*30% + (12-7)*20% + ...
>
> datetime is ordered (and unique and has also an id).
>
> Rows are in the area of millions.
>
> How is it done best?
> 1.) Self join with one row shift?
> 2.) function?
>
> Any hint?
>
> Thnx.
>
> Ciao,
> Gerhard
>
> --
> http://www.wiesinger.com/
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>