Re: Sum of multiplied deltas - Mailing list pgsql-general

From Daniel Verite
Subject Re: Sum of multiplied deltas
Date
Msg-id 0b992980-fe9f-4291-aa13-9d1608bd9e6d@mm
Whole thread Raw
In response to Sum of multiplied deltas  (Gerhard Wiesinger <lists@wiesinger.com>)
List 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 by date:

Previous
From: "Roderick A. Anderson"
Date:
Subject: Re: Adding the host name to the PgSQL shell
Next
From: Chris
Date:
Subject: Re: max execution time of query