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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
