Re: Windowing ?

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