Postgres query for difference between latest and first record of the day-CodePudding

Postgres data alike this:

|   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  |

A value is added every minute and increases over time.

I would like to get the current total for the day and have this in a Grafana stat panel. Above it would be: 221 (1509-1288). Latest record minus first record of today.

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

With this the latest record is given (A).

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

With this the first record of the day is given (B).

Grafana cannot do math on this (A-B). Single query would be best.

Sadly my database knowledge is low and attempts at building queries have not succeeded, and have taken all afternoon now.

Theoretical ideas to solve this:

Subtract the min from the max value where time frame is today.
Using a lag, lag it for the count of records that are recorded today. Subtract lag value from latest value.
Window function.

What is the best way (performance wise) forward and how would such query be written?

CodePudding user response：

Calculate the cumulative total last_value - first_value for each record for the current day using window functions (this is the t subquery) and then pick the latest one.

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;

However if it is certain that value_1 only "increases over time" then simple grouping will do and that is by far the best way performance wise:

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;

CodePudding user response：

Please, check if it works.

Since you intend to publish it in Grafana, the query does not impose a period filter.

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);