PostgreSQL calculate difference between rows

Question

I tried to calculate difference between rows in a field using a query:

Illustrations:
input:year,month,fixes
output:increase

     year | month | fixes    | increase
    ------+-------+----------+-----------
     2006 | 04    |       1  | 0
     2006 | 05    |       4  | 3
     2006 | 06    |       3  | -1

Increase column as output by difference between adjacent rows in fixes.

What output do you expect?

Arion
– Arion

2014-07-11 06:38:31 +00:00
Commented Jul 11, 2014 at 6:38 — Arion
– Arion, Commented Jul 11, 2014 at 6:38
increase column as output

user2177232
– user2177232

2014-07-11 06:39:58 +00:00
Commented Jul 11, 2014 at 6:39 — user2177232
– user2177232, Commented Jul 11, 2014 at 6:39

user330315 · Accepted Answer · 2014-07-11 06:40:01Z

148

This is what window functions are for:

select year, 
       month,
       fixes,
       fixes - lag(fixes) over (order by year, month) as increase,
from the_table;

For more details please see the manual:
http://www.postgresql.org/docs/current/static/tutorial-window.html

answered Jul 11, 2014 at 6:40

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL calculate difference between rows

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related