Helo, this is my query:
MariaDB [PV_Anlage]>
SELECT created_at, verbrauch_ges
FROM pv_metrics
group by DATE(created_at)
ORDER BY ID DESC limit 20;
+---------------------+---------------+
| created_at | verbrauch_ges |
+---------------------+---------------+
| 2025-03-23 00:00:04 | 5628.79 |
| 2025-03-22 00:00:06 | 5622.54 |
| 2025-03-21 00:00:11 | 5615.44 |
| 2025-03-20 00:00:13 | 5609.49 |
| 2025-03-19 00:00:17 | 5605.4 |
| 2025-03-18 00:00:00 | 5600.45 |
| 2025-03-17 00:00:03 | 5591.36 |
| 2025-03-16 00:00:09 | 5585.2 |
| 2025-03-15 00:00:16 | 5578.96 |
| 2025-03-14 00:00:04 | 5571.1 |
| 2025-03-13 00:00:07 | 5560.34 |
| 2025-03-12 00:00:10 | 5550.54 |
| 2025-03-11 00:00:13 | 5540.12 |
| 2025-03-10 00:00:16 | 5534.81 |
| 2025-03-09 00:00:18 | 5528.48 |
| 2025-03-08 00:00:17 | 5521.71 |
| 2025-03-07 00:00:11 | 5515.27 |
| 2025-03-06 00:00:14 | 5510.17 |
| 2025-03-05 00:00:06 | 5504.08 |
| 2025-03-04 00:00:09 | 5497.21 |
+---------------------+---------------+
This is the result, as I'm writing values every 20sec. I've selected the very firste value of the day, so now I need the diffrence between the days. I've tried with LAG() function but I dont understand how to include the function into the general query.
best regardas Frank
In simple case,
if we consider the value of verbrauch_ges to be monotonously increasing, the smallest (initial) value for the day can be obtained as follows
SELECT min(created_at)created_at
,min(verbrauch_ges) verbrauch_ges
,lag(min(verbrauch_ges))over(order by created_at) prev_verbauch_ges
,min(verbrauch_ges)-coalesce(lag(min(verbrauch_ges))over(order by created_at),0.0) as day_diff
FROM pv_metrics
group by DATE(created_at)
ORDER BY ID DESC limit 20;
for LAG function we use value min(verbrauch_ges) because LAG function in SELECT clause will be executed after GROUP BY.
This is semantically equivalent to
SELECT created_at, verbrauch_ges
,lag(verbrauch_ges)over(order by created_at) prev_verbauch_ges
FROM (
SELECT min(created_at) as created_at, min(verbrauch_ges) as verbrauch_ges
FROM pv_metrics
group by DATE(created_at)
ORDER BY ID DESC limit 20
)sq
;
MariaDb allows use order by created_at and order by date(created_at) in LAG function.
Common case is select target rows from table and INNER JOIN table to this selection
SELECT created_at, verbrauch_ges
,lag(verbrauch_ges)over(order by created_at) prev_verbauch_ges
FROM (
select min(id) id
from pv_metrics
group by DATE(created_at)
ORDER BY min(id) DESC limit 20
) s
inner join pv_metrics m on m.id=s.id
ORDER BY s.ID DESC ;
OR
SELECT created_at, verbrauch_ges
,lag(verbrauch_ges)over(order by created_at) prev_verbauch_ges
FROM (
select min(created_at) created_at
from pv_metrics
group by DATE(created_at)
ORDER BY min(id) DESC limit 20
) s
inner join pv_metrics m on m.created_at=s.created_at
ORDER BY s.ID DESC ;