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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>