Re: 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