agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedWindowing ?
3+ messages / 2 participants
[nested] [flat]
* Windowing ?
@ 2017-12-11 17:09 Olivier Leprêtre <o.lepretre@gmail.com>
2017-12-11 17:15 ` Re: Windowing ? Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 3+ messages in thread
From: Olivier Leprêtre @ 2017-12-11 17:09 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Windowing ?
2017-12-11 17:09 Windowing ? Olivier Leprêtre <o.lepretre@gmail.com>
@ 2017-12-11 17:15 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2017-12-11 18:46 ` Re: Windowing ? Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 3+ messages in thread
From: Andreas Kretschmer @ 2017-12-11 17:15 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Windowing ?
2017-12-11 17:09 Windowing ? Olivier Leprêtre <o.lepretre@gmail.com>
2017-12-11 17:15 ` Re: Windowing ? Andreas Kretschmer <andreas@a-kretschmer.de>
@ 2017-12-11 18:46 ` Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 0 replies; 3+ messages in thread
From: Andreas Kretschmer @ 2017-12-11 18:46 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-12-11 18:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-11 17:09 Windowing ? Olivier Leprêtre <o.lepretre@gmail.com>
2017-12-11 17:15 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2017-12-11 18:46 ` Andreas Kretschmer <andreas@a-kretschmer.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox