Postgres Pro
Downloads
Home   >   mailing lists

Thread: Windowing ?

Windowing ?

From
Olivier Leprêtre
Date:

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. I tried some windowing tests but was unsuccessful.

 

Input table :

 

road   segment       weight

1        1                 80

1        2                 5

1        3                 3

2        1                 75

2        2                 8

2        3                 12

2        4                 3

 

The result should be a new delta column :

 

road   segment       weight delta

1        1                 80                80      (80-0) first row

1        2                 5                 75      (80-5)

1        3                 3                 72      (75-3)

 

2        1                 75                75      (75-0)

2        2                 8                 67      (75-8)

2        3                 12                55      (67-12)

2        4                 3                 52      (55-3)

 

Thanks for any help,

 

By the way, thanks again to Andreas and Martin that unstucked my mind yesterday as I was searching for complicated solutions when a very simple one existed.

 

Have a good day (or good evening)

 

Olivier


Garanti sans virus. www.avast.com

Re: Windowing ?

From
Andreas Kretschmer
Date:

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) ...



Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: Windowing ?

From
Andreas Kretschmer
Date:

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