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
