Am 11.12.2017 um 18:15 schrieb Andreas Kretschmer:
>
>
> Am 11.12.2017 um 18:09 schrieb Olivier Leprêtre:
>>
>> Hi,
>>
>> I have a table describing sort of ordered road segments and their
>> associated weight. I would like to calculate weight deltas between
>> two consecutive segments. Problem is to substract previous from
>> current row and reuses result as input for the next row.
>>
>
> You can use the lag() - function.
>
> select ..., lag(weight) over (partition by road order by segment) ...
>
>
sorry, my mistake, that was wrong. That should do the job:
test=*# select * from xxx;
road | segment | weight
------+---------+--------
1 | 1 | 80
1 | 2 | 5
1 | 3 | 3
2 | 1 | 75
2 | 2 | 8
2 | 3 | 12
2 | 4 | 3
(7 Zeilen)
test=*# select road, segment, weight, first_value(weight) over
(partition by road order by segment) -y from (select *, sum(weight) over
(partition by road order by segment) - first_value(weight) over
(partition by road order by segment) as y from xxx) foo;
road | segment | weight | ?column?
------+---------+--------+----------
1 | 1 | 80 | 80
1 | 2 | 5 | 75
1 | 3 | 3 | 72
2 | 1 | 75 | 75
2 | 2 | 8 | 67
2 | 3 | 12 | 55
2 | 4 | 3 | 52
(7 Zeilen)
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
