Postgres update with previous row

Question

I have a table that records values from electrical meters

The table looks like this

SELECT created_at, value, delta 
FROM metervalues 
ORDER BY created_at

2013-05-28 07:59:00 | 752105,6 | null
2013-05-28 08:14:00 | 752156,0 | null
2013-05-28 08:29:00 | 752207,2 | null
2013-05-28 08:44:00 | 752259,2 | null
2013-05-28 08:59:00 | 752312,8 | null
2013-05-28 09:14:00 | 752366,4 | null
2013-05-28 09:29:00 | 752417,2 | null

now I want to calculate the consumption so

delta(current record) = value(current record) - value(previous record)

It could happen that records are added later so the record id's don't necessarily follow the "created_at" order.

Currently I'm loading the data using a ruby script then loop and update the records. This is very slow.

Can this be solved by SQL direct ? I tried some samples with cursors but did not really find a solution.

Gordon Linoff · Accepted Answer · 2013-06-13 16:31:31Z

3

SQL tables are inherently unordered, so there is no "previous" record. The question doesn't make sense, unless you have a column that specifies the ordering.

I think the question is saying that created_at is not such a column, but RecordId is. If so, you can use the lag() function:

select created_at, value,
       (value - lag(value) over (order by recordId)) as delta
from metervalues

If you don't have such an id, then you need to find a way to specify the ordering of the values.

answered Jun 13, 2013 at 16:31

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

2 Comments

Alexander Melle Over a year ago

Hi, I know, that's why there is a "order by created_at" in the select statement in the sample above

Gordon Linoff Over a year ago

@AlexanderMelle . . . Then just use created_at instead of recordId in the above query.

Ismail Khan · Accepted Answer · 2017-09-08 14:22:43Z

1

SELECT
    created_at,
    "value",
    "value" - lag("value", 1, 0) over(order by created_at) as consumption
FROM metervalues
ORDER BY created_at

EDIT: Missing as before consumption -- now corrected

edited Sep 8, 2017 at 14:22

Ismail Khan

9523 gold badges10 silver badges20 bronze badges

answered Jun 13, 2013 at 16:32

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

Alexander Melle Over a year ago

Hi,great, this works sofar, I only had to change the default (0) to 0.0 as value is a numeric field. How do I get the consumption updated to the records "delta" field

Alexander Melle Over a year ago

made it work! with new_values as ( SELECT id, created_at, "value", "value" - lag("value", 1, 0.0) over (order by created_at) consumption FROM metervalues ORDER BY created_at) update metervalues as mv set delta = new_values.consumption from new_values where mv.id = new_values.id;

Collectives™ on Stack Overflow

Postgres update with previous row

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