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