Group By liefert nur eine beschränkte Zahl von Zeilen

[diedelie]

Ich versuche Daten aus einer SQL Datenbank mit Meßwerten von Energiezählern auszuwerten. Als Beispiel verwende ich die Daten aus Data Platform – Open Power System Data
Meine Datenbank ist MariaDB 11.5.2. Darin ist eine Tabelle mit 15min Zeitwerten und Meßergebnissen.

CREATE TABLE energydata.residential2ts (
 `utc_timestamp` TIMESTAMP NOT NULL,
 cet_cest_timestamp TIMESTAMP NOT NULL,
 DE_KN_residential2_circulation_pump DOUBLE NULL,
 DE_KN_residential2_dishwasher DOUBLE NULL,
 DE_KN_residential2_freezer DOUBLE NULL,
 DE_KN_residential2_grid_import DOUBLE NULL,
 DE_KN_residential2_washing_machine DOUBLE NULL,
 interpolated varchar(10000) NULL,
 CONSTRAINT residential2_pk PRIMARY KEY (`utc_timestamp`)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4;

Die Tabelle enthält ca. 40.000 Datensätze über ein Jahr von 2015-04-15 and 2016-06-10, also ca. 400 Tage.
Ich versuche über folgende Abfrage jeweils den letzten Eintrag für jeden Tag zu erhalten.

# timestamps are between 2015-04-15 and 2016-06-10 11:30:00
select max(cet_cest_timestamp) maxtime
from residential2ts
group by day(cet_cest_timestamp)
order by maxtime;

Dabei erhalte ich statt 400 Tagen nur die letzten 31 Tage, d.h. alle Tage zwischen 2015-04-15 und 2016-05-11 fehlen.

2016-05-11 23:45:00
2016-05-12 23:45:00
...
2016-06-09 23:45:00
2016-06-10 11:30:00

Kann mir jemand erklären, warum ich nicht alle ca. 400 Werte von 2015-04-15 bis 2016-05-11 erhalte?
Ist das eine MariaDB Einstellung?

 

[castorp]

day() liefert den Tag des Monats zurück (also z.b. 13 wenn heute der 13.02.2025 ist). Das GROUP BY erzeugt also 31 Gruppen.

Wenn ich richtig verstehe was Du willst, musst Du nach dem Datum (ohne Uhrzeit) gruppieren. Also GROUP BY date(cet_cest_timestamp)

 

[diedelie]

Jeden Tag werden im 15min Abstand Meßwerte erfasst, es kann aber vorkommen, dass dabei Lücken entstehen. Um den End-Zählerstand für jeden Tag zu ermitteln, möchte ich den letzten Wert für jeden Tag ermitteln.
Den letzten Wert erhalte ich mit "max(cet_cest_timestamp)".
Das funktioniert auch soweit, aber aus irgendeinem Grund ist das Ergebnis unvollständig.
Das Beispiel oben habe ich mit der mysql cli ausgewertet.
Wenn ich die gleiche SQL Abfrage von golang ausführe, erhalte ich z.B. 20 Zeilen statt 30 Zeilen. Ich kann kein System dahinter erkennen.

 

[dabadepdu]

Das Aggregat Max() hat nichts mit der Gruppierung zu tun.

Du gruppierst nach day() was vermutlich 1 bis 31 ergibt, Monat und Jahr werden dabei ignoriert. Das sind nicht notwendigerweise die "letzten Tage" sondern die mit dem Maximalwert an irgendeinem der 31 Tage gemäß der Funktion

day(cet_cest_timestamp)

, z.B. 4 oder 27 oder 31, für die ein Messwert vorliegt.
Diese Tage 1-31 tauchen in den Daten pro Monat und Jahr auf, nach ihnen wird gruppiert, davon wird dann das max() berechnet, von allen 31 originalen Werten, nicht nach dem Groupwert.

Der Ansatz von Castorp scheint mir richtig.

 

[BerndB]

Nimm einfach DATE() zum gruppieren

GROUP BY DATE(cet_cest_timestamp);

 

[diedelie]

Danke für die vielen Antworten, sehr nett.
Jetzt habe ich es kapiert.
date function gruppiert korrekt, wenn man mehr als ein Jahr betrachtet, da der ganze Datumsanteil betrachtet wird.
Getestet und funktioniert bei mir.

SELECT DATE('2013-07-18 12:21:32');
+-----------------------------+
| DATE('2013-07-18 12:21:32') |
+-----------------------------+
| 2013-07-18                  |
+-----------------------------+

DAY() is a synonym for dayofmonth.
Wenn also mehr als ein Jahr betrachtet wird, werden nur 31 Werte zurückgeliefert.

SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
|                        3 |
+--------------------------+

Damit habe ich auch gleich gelernt, warum
GROUP BY MONTH(cet_cest_timestamp);
nur 12 Werte zurückliefert, wenn man mehr als ein Jahr betrachtet.

 

[ukulele]

Und wenn du jetzt noch nach mehreren Werten gruppierst wird es dir den Atem verschlagen ;-)
GROUP BY YEAR(cet_cest_timestamp),MONTH(cet_cest_timestamp),DAY(cet_cest_timestamp);

 

[diedelie]

Ich bin noch bei Atem ;-) aber überrascht, weil ich noch nicht wusste, dass man bei group by auch mehrer Funktionen angeben kann.
GROUP BY YEAR(cet_cest_timestamp),MONTH(cet_cest_timestamp),DAY(cet_cest_timestamp);
liefert jedenfalls auch alle 422 Werte über die 2 Jahre genauso wie
GROUP BY DATE(cet_cest_timestamp);
Again what learned...
Vielen Dank!

 

[diedelie]

Mit
GROUP BY YEAR(cet_cest_timestamp),MONTH(cet_cest_timestamp);
kann ich also den Zählerstand am Ende jeden Monats ermitteln. Super.

 

[BerndB]

Und wenn du jetzt noch nach mehreren Werten gruppierst wird es dir den Atem verschlagen ;-)
GROUP BY YEAR(cet_cest_timestamp),MONTH(cet_cest_timestamp),DAY(cet_cest_timestamp);

Entspricht DATE(cet_cest_timestam)