Postgresql difference between rows

Question

My data:

How to compute column 'change'?

id value  change  |    my comment, how to compute
1     10     10   |    20-10
1     20     40   |    60-20
1     60     40   |    default_value-60. In this example default_value=100 
2     10     90   |    default_value-10
3     10     20   |    30-10
3     30     70   |    default_value-30

In other words: if row of id is last, then compute 100-value, else compute next_value-value_now

Unique. Imagine that there is date column and I order by it. — Marta
– Marta, Commented Jan 21, 2015 at 9:35

user330315 · Accepted Answer · 2015-01-21 09:38:14Z

3

You can access the value of the "next" (or "previous") row using a window function. The concept of a "next" row only makes sense if you have a column to define an order on the rows. You said you have a date column on which you can order the result. I used the column name your_date_column for this. You need to replace that with the actual column name of course.

select id, 
       value, 
       lead(value, 1, 100) over (partition by id order by your_date_column) - value as change
from the_table
order by id, your_date_column

lead(value, 1, 100) says: take the column value of the "next" row (that's the 1). If there is no such row, use the default value 100 instead.

answered Jan 21, 2015 at 9:38

user330315

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

2 Comments

Marta Over a year ago

How about default_value?

user330315 Over a year ago

@WingedPanther: should work as well, because lead(value) is the same as lead(value,1,null)

Mihai · Accepted Answer · 2015-01-21 10:03:20Z

1

Join on a subquery and use ROW_NUMBER to find the last value per group

WITH CTE AS(
SELECT id,value,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY date) rn,
(LEAD(value) OVER (PARTITION BY id ORDER BY date)-value) change FROM t)
SELECT cte.id,cte.value,
(CASE WHEN cte.change IS NULL THEN 100-cte.value ELSE cte.change END)as change FROM cte LEFT JOIN
(SELECT id,MAX(rn) mrn FROM cte
GROUP BY id) as x
ON x.mrn=cte.rn AND cte.id=x.id

FIDDLE

answered Jan 21, 2015 at 10:03

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

2 Comments

user330315 Over a year ago

Why this complicated nesting of queries? Does it offer any advantage over a "simple" access using lead()?

Mihai Over a year ago

@a_horse_with_no_name Not very familiar with full parameters of lead,did it with what I knew.

Collectives™ on Stack Overflow

Postgresql difference between rows

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related