Re: [GENERAL] 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of multiplied deltas
Marc very concerned about the 'dirty' classification any suggestions? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Subject: Re: [GENERAL] Sum of multiplied deltas > Date: Tue, 9 Jun 2009 13:46:03 +0200 > From: m.ma...@intershop.de > To: oliver.li...@gtwm.co.uk; pgsql-general@postgresql.org > > > > Hello, > > I've found a dirty hack with custom GUC variables here: > http://archives.postgresql.org/pgsql-hackers/2008-11/msg00643.php > > Although dirty, it seems that it beats the windowing performances of > 8.4. > > So I wonder if there are any concern about this... > > Cheers, > > Marc Mamin > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_BR_life_in_synch_062009
Re: [GENERAL] Sum of multiplied deltas
Hello, I've found a dirty hack with custom GUC variables here: http://archives.postgresql.org/pgsql-hackers/2008-11/msg00643.php Although dirty, it seems that it beats the windowing performances of 8.4. So I wonder if there are any concern about this... Cheers, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of multiplied deltas
Gerhard Wiesinger wrote: 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). 1.) Self join with one row shift? Self-join only helps if the id comes from a gap-less sequence. Row numbers could be used if available, but they are not in 8.3. A possible way of solving this (when a procedural method is not wanted) is to lay out the dataset in temporary arrays that are repeated for every row you need to compute. That can be arranged in a self-contained sql query, like this: select sum((av1[i]-av1[i-1])*av2[i]) from (select av1,av2,generate_series(2,array_upper(av1,1)) as i from (select array_accum(val1) as av1, array_accum(val2) as av2 from (select val1,val2 from TABLENAME order by datetime) s0 ) s1 ) s2 However, this would probably be too slow for a large dataset. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of multiplied deltas
On 8 Jun 2009, at 19:01, David Fetter 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% + ... This is best done in 8.4 using Windowing. Sadly, it's an 8.4-only feature, and dodgy hacks are the rule until you can use them. Cheers, David. I do this type of thing now and again using a self join with an offset. select test_a.val1 - test_b.val1 from test test_a inner join test test_b on test_a.pkey = test_b.pkey - 1; Thought I was quite clever the first time, didn't know it was a dodgy hack! I'm trying to learn more about windowing before 8.4, how would this example be done with that? Regards Oliver -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of multiplied deltas
Gerhard Wiesinger writes: > Any hints for an 8.3 environment (currently)? Use a FOR loop in plpgsql and remember the previous row's value in a variable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of multiplied deltas
Any hints for an 8.3 environment (currently)? Thnx. Ciao, Gerhard -- http://www.wiesinger.com/ On Mon, 8 Jun 2009, David Fetter wrote: On Mon, Jun 08, 2009 at 08:35:20AM +0200, 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% + ... This is best done in 8.4 using Windowing. Sadly, it's an 8.4-only feature, and dodgy hacks are the rule until you can use them. Cheers, David. 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 -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Sum of multiplied deltas
On Mon, Jun 08, 2009 at 08:35:20AM +0200, 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% + ... This is best done in 8.4 using Windowing. Sadly, it's an 8.4-only feature, and dodgy hacks are the rule until you can use them. Cheers, David. > > 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 -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
