Re: 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate