By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE meters
(id int, description varchar(10));
;
CREATE TABLE readings
(id int, meterid int, date date, value int);
INSERT INTO meters (id, description)
VALUES
(1, 'this'),
(2, 'is'),
(3, 'not'),
(4, 'really'),
(5, 'relevant')
;
INSERT INTO readings (id, meterid, date, value)
VALUES
(1, 4, '20081231', 500),
(2, 4, '20090203', 550),
(3, 1, '20090303', 300),
(4, 2, '20090303', 244),
(5, 4, '20090303', 600),
(6, 1, '20090403', 399),
(7, 2, '20090403', 288),
(8, 3, '20090403', 555)
13 rows affected
select m.id, r.date, r.value, r.value - prev_value
from meters m left join
(select r.*,
lag(value) over (partition by meterid order by date) as prev_value,
row_number() over (partition by meterid order by date desc) as seqnum
from readings r
) r
on r.meterid = m.id and seqnum = 1
order by m.id
| id | date | value | (No column name) |
|---|---|---|---|
| 1 | 2009-04-03 | 399 | 99 |
| 2 | 2009-04-03 | 288 | 44 |
| 3 | 2009-04-03 | 555 | null |
| 4 | 2009-03-03 | 600 | 50 |
| 5 | null | null | null |