Re: Sum of multiplied deltas
От | Daniel Verite |
---|---|
Тема | Re: Sum of multiplied deltas |
Дата | |
Msg-id | 0b992980-fe9f-4291-aa13-9d1608bd9e6d@mm обсуждение исходный текст |
Ответ на | Sum of multiplied deltas (Gerhard Wiesinger <lists@wiesinger.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: