PostgreSQL calculate difference between multi rows

Question

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

Illustrations: input:year,month,name, size output:increase

 year | month  | name    | Size  | increase
------+--------+------- -+-------+-----------
 2020 | 01     |  john   |10     | 0
 2020 | 01     |  peter  |12     | 0
 2020 | 01     |  kim    |16     | 0

 2020 | 02     |  john   |15     | 5  <- 15 - 10
 2020 | 02     |  peter  |16     | 4  <- 16 - 12
 2020 | 02     |  kim    |17     | 1  <- 17 - 16

 2020 | 03     |  john   |18     | 3  <- 18 - 16
 2020 | 03     |  peter  |19     | 3  <- 19 - 16
 2020 | 03     |  kim    |77     | 60 <- 77 - 17


         -------
 2020 | 12     |  john   |25     | 17
 2020 | 12     |  peter  |70     | 33
 2020 | 12     |  kim    |90     |42

Increase column as output by difference between adjacent "name" rows in size.

JohnHC · Accepted Answer · 2020-01-07 11:11:36Z

4

Use LAG()

select year, 
  month, 
  name, 
  size, 
  size - lag(size) over (partition by name order by year, month) as increase
from MyTable

answered Jan 7, 2020 at 11:11

JohnHC

11.2k1 gold badge28 silver badges42 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-01-07 11:44:44Z

0

If you want 0s for the first set of rows, then use the 3-argument form of lag():

select year, month,  name, size, 
       (size - lag(size, 1, size)) over (partition by name order by year, month) as increase
from MyTable;

Personally, I prefer NULL so I prefer JohnHC's answer. However, the question is explicitly asking for 0 values there.

answered Jan 7, 2020 at 11:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

obi · Accepted Answer · 2020-01-08 16:52:12Z

0

Thanks JohnHC and Gorden for helping.

It works when I run the query on psql commandline. But when I put it into a php script:

$result = pg_query($conn,"select year, month, name, (size - lag(size, 1, size)) over (partition by name order by year, month). as increase from testdb ");

I get error message:

PHP Warning: pg_query(): Query failed: ERROR: syntax error at or near '- lag(size)......'

answered Jan 8, 2020 at 16:52

obi

113 bronze badges

Collectives™ on Stack Overflow

PostgreSQL calculate difference between multi rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related