Postgres查询当天最新记录和第一条记录之间的差异

Question

Postgres数据类似于：

|   id  |         read_at        | value_1 |
| ------|------------------------|---------|
| 16239 | 2021-11-28 16:13:00+00 |   1509  |
| 16238 | 2021-11-28 16:12:00+00 |   1506  |
| 16237 | 2021-11-28 16:11:00+00 |   1505  |
| 16236 | 2021-11-28 16:10:00+00 |   1501  |
| 16235 | 2021-11-28 16:09:00+00 |   1501  |
| ..... | .......................|   ....  |
| 15266 | 2021-11-28 00:00:00+00 |   1288  |

每分钟增加一个值，并随着时间的推移而增加。

我想得到当天的当前总数，并将其放在Grafana统计面板中。上面是：221(1509-1288(。最新记录减去今天的第一个记录。

SELECT id,read_at,value_1
FROM xyz
ORDER BY id DESC
LIMIT 1;

据此给出了最新记录(A(。

SELECT id,read_at,value_1
FROM xyz
WHERE read_at = CURRENT_DATE
ORDER BY id DESC
LIMIT 1;

据此，给出了当天的第一条记录(B(。

格拉法娜不会计算这个(A-B(。最好是单个查询。

遗憾的是，我的数据库知识水平很低，构建查询的尝试也没有成功，现在已经花了整个下午的时间。

解决这一问题的理论思路：

• 从当前时间范围的最大值中减去最小值
• 使用滞后，将其滞后于今天记录的记录数。从最新值中减去滞后值
• 窗口功能

最佳的前进方式(性能方面(是什么？如何编写这样的查询？

Answer 1

使用窗口函数(这是t子查询(计算当天每个记录的累计总last_value - first_value，然后选择最新的一个。

select current_total, read_at::date as read_at_date 
from
(
select last_value(value_1) over w - first_value(value_1) over w as current_total,
read_at 
from the_table
where read_at >= current_date and read_at < current_date + 1
window w as (partition by read_at::date order by read_at)
) as t
order by read_at desc limit 1;

然而，如果确定CCD_；"随时间增加"；那么简单的分组就可以了，这是迄今为止性能最好的方法：

select max(value_1) - min(value_1) as current_total, 
read_at::date as read_at_date 
from the_table
where read_at >= current_date and read_at < current_date + 1
group by read_at::date;

Answer 2

请检查它是否有效。

由于您打算在Grafana中发布它，因此查询不会强制使用句点过滤器。

https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/3080

create table g (id int, read_at timestamp, value_1 int);
insert into g
values
(16239, '2021-11-28 16:13:00+00', 1509),
(16238, '2021-11-28 16:12:00+00', 1506),
(16237, '2021-11-28 16:11:00+00', 1505),
(16236, '2021-11-28 16:10:00+00', 1501),
(16235, '2021-11-28 16:09:00+00', 1501),
(15266, '2021-11-28 00:00:00+00', 1288);
select date(read_at), max(value_1) - min(value_1)
from g
group by date(read_at);

Answer 3

由于数据包含两个不同时间(16:09和16:10(的多个值，这表明最小值和最大值可能并不总是在时间间隔中增加。保留减少的可能性。那么，你想要最大-最小读数还是最小/最大时间读数的差异。下面的get value difference获取标题中所示的当天第一次和最后一次读数之间的差值。

with parm(dt) as 
( values (date '2021-11-28') )
, first_read (f_read,f_value) as 
( select read_at, value_1 
from test_tbl
where read_at at time zone 'UTC'= 
( select min(read_at at time zone 'UTC') 
from test_tbl
join parm  
on ((read_at at time zone 'UTC')::date = dt) 
)
) 
, last_read (l_read, l_value) as 
( select read_at,value_1 
from test_tbl
where read_at at time zone 'UTC'= 
( select max(read_at at time zone 'UTC') 
from test_tbl
join parm  
on ((read_at at time zone 'UTC')::date = dt) 
)
) 
select l_read, f_read, l_value, f_value, l_value - f_value as "Day Difference"
from last_read 
join first_read on true;