查找最后和第二个最后日期和相应的值

Question

考虑以下模式(fiddle):

CREATE TABLE meters
(
id int, 
description varchar(10)
);
CREATE TABLE readings
(
id int, 
meterid int, 
date date, 
value int
);

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);
INSERT INTO meters (id, description)
VALUES
(1, 'this'),
(2, 'is'),
(3, 'not'),
(4, 'really'),
(5, 'relevant');

对于每个meter.id，我需要找到最近的读取日期，值以及与先前读取的值的差异。

对于示例数据，我的输出看起来像这样(加上meters中的一些其他列):

tbody> <<tr>245

meterid	最新	value	增量值
1	20090403	399	99
	20090403	288	44
3	20090403	555	空
	20090303	600	50
	空	空	空

如果我理解正确的话，您可以使用窗口函数:

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;

不需要聚合。这是一个数据库<>小提琴

使用LEAD获得下一个反向值，使用ROW_NUMBER获得第一行。

SELECT *
FROM
(SELECT *,
delta_value = value - LEAD(value) over (PARTITION BY r.meterid ORDER BY date DESC),
RowN = Row_Number() over(PARTITION BY r.meterid ORDER BY date DESC)
FROM readings AS r
) a
WHERE RowN = 1
ORDER BY p.meterId