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

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

• mariadb
• row
• lag
• difference

• The query is illogical. It contains incomplete grouping. Enable ONLY_FULL_GROUP_BY then fix your query. – Akina Commented Mar 23 at 17:47

3 Answers 3

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 ;

Examples

Your query exploits the notorious MySQL extension to GROUP BY. It means this baffling pile of uncertainty.

SELECT created_at, ANY_VALUE(verbrauch_ges)
  FROM pv_metrics
 group by DATE(created_at)
 ORDER BY ANY_VALUE(ID) DESC limit 20

ANY_VALUE)_ tells MySQL it can arbitrarily choose some value from the group. You get unpredictable values from that.

This makes it hard to guess your intent from your example.

It sounds like you want a result set with one row per calendar day, showing the difference between the verbrauch_ges values with the earliest created_at datestamp for that day

Here's how you do that.

First you want a result set with the earliest created_at time on each day.

SELECT MIN(created_at) created_at
  FROM pv_metrics
 GROUP BY DATE(created_at)

Then you want to extract the values, one per day.

SELECT DATE(pv_metrics.created_at) tag, AVG(verbracht_ges) erste_verbraucht_ges 
  FROM pv_metrics
  JOIN (
        SELECT MIN(created_at) created_at
         FROM pv_metrics
        GROUP BY DATE(created_at)
       ) firsts ON pv_metrics.created_at = firsts.created_at
  GROUP_BY DATE(pv_metrics.created_at) 

The AVG() is there to do something reasonable if you have more than one observation at precisely the same created_at time. You could also use MAX() or MIN().

Finally, now that you have a virtual table with columns tag and erste_verbraucht_ges you can do a LAG select on it to get your desired result.

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;

That's the answer or query I've needed.

Thanks