Datenbank mit Messwerten verkleinern/komprimieren

[MichaF]

Hallo Leute,

Ich bin nicht neu in Mysql, glaubte bisher meinen Weg finden zu können. Bisher.

Ich hab nen kleinen Server auf Ubuntu mit einem Server Version: 8.0.34-0ubuntu0.20.04.1

in einer Datenbank gibt es ein paar Tabellen in denen ich Messwerte speichere - Vom Solarertrag bis zu ' Wetterbeobachtung' oder Warmwasserversorgung. Arduinos zeichnen Daten auf und senden die Ergebnisse an de Db. Alles selbst gemacht.
Ich nutze die Tabellen via einfacher php-Seiten um mein Verlangen nach Statistik zu erfüllen - soweit ist alles prima.

Allerdings laufen die Arduinos inzwischen bereits mehrere Jahre und ich nutze eigentlich maximal die letzten 13 Monate für meine Auswertungen im Sub-Stunden-Niveau.

Ich möchte die Tabellen (automatisch) verkleinern, in dem ich zu jedem Monats 1. eine Abfrage startet, die mir von allen Messwerten die älter als 13 Monate sind Tages-summen und/oder Durchschnitte berechnet.
Diese Werte sollen dann in die selbe Tabelle in extra Spalten geschrieben werden geschrieben werden und den Datensatz des letzten Eintrag des Tages überschreiben mit der Uhrzeit 23:59:59.
Anschließend alle Datensätze des Datums vor 23:59:59 mit den Einzelmessungen löschen.


Wie stele ich das an:
Wie ich die Summen/durchschnitte der Einzelwerte des Tages ermittle ist mir klar.
aber: wie ermittle ich die höchste ID aller Datensätze dieses Tages?
Wie passt das in ein Query?
Wie lösche ich alle Datensätze eines Tages, außer die höchste ID?

Wie kriege ich das für mehrere Tage in ein Query?

Fragen über Fragen.
Ich hoffe die Fragestellung ist klar. Wenn nicht - bitte nachfragen.


Mit hoffnungsvollen Grüßen,

Micha

 

[akretschmer]

MySQL und MS-SQL beginnen zwar beide mit M, sind aber sehr unterschiedlich: das eine kostet Geld und das andere taugt nix.

Alles in allem denke ich, die Antwort auf Deine Frage ist Partitioning.

 

[dabadepdu]

die Antwort auf Deine Frage ist Partitioning.

Kann Partitioning auch Aggregation?

wie ermittle ich die höchste ID aller Datensätze dieses Tages?
Wie passt das in ein Query?
Wie lösche ich alle Datensätze eines Tages, außer die höchste ID?

Wie kriege ich das für mehrere Tage in ein Query?

Einige der Fragen kann man nur beantworten, wenn man Deine Speicherstruktur kennt (und ein paar Daten)
> Create Script posten
> Insert Script posten

Höchste, Summe usw. sind allgemein Aggregatabfragen, ermittelt man mit max(id) usw. plus Group by
delete from <table> where tag=xy and id != <ermittelte Max ID>
where tag between <ältesterTag> and <jüngsterTag>

 

[MichaF]

Hallo!

Danke für eure Antworten.
Ich habe mich mal zum Partitioning informiert. ich glaube das ist nicht wirklich was ich brauche, ddurch wird meine Db ja nicht wirklich kleiner, liegt nur woanders ...

dabadepdu, Create-script ist, nehme ich an, das hier: kommt aus der Sicherung der Db:

CREATE TABLE `Solar` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `Datum` date DEFAULT NULL,
  `TIME` time DEFAULT NULL,
  `Wh` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

hier kommen, wenn es hell ist (bestimmt durch den Arduino), aller 10 Minuten die erzeugten Wh rein.

INSERT INTO `Solar` VALUES (29800,'2023-06-16','05:03:32',0),(29801,'2023-06-16','05:13:33',0),(29802,'2023-06-16','05:23:35',0),(29803,'2023-06-16','05:33:36',0),(29804,'2023-06-16','05:43:38',0),(29805,'2023-06-16','05:53:39',1),(29806,'2023-06-16','06:03:41',1),(29807,'2023-06-16','06:13:42',1),(29808,'2023-06-16','06:23:44',2),(29809,'2023-06-16','06:33:45',2),(29810,'2023-06-16','06:43:47',2),(29811,'2023-06-16','06:53:48',3),(29812,'2023-06-16','07:03:50',3),(29813,'2023-06-16','07:13:51',3),(29814,'2023-06-16','07:23:53',4),(29815,'2023-06-16','07:33:54',4),(29816,'2023-06-16','07:43:56',4),(29817,'2023-06-16','07:53:57',4),(29818,'2023-06-16','08:03:59',5),(29819,'2023-06-16','08:14:01',5),(29820,'2023-06-16','08:24:02',6),(29821,'2023-06-16','08:34:04',5),(29822,'2023-06-16','08:44:05',6),(29823,'2023-06-16','08:54:07',8),(29824,'2023-06-16','09:04:08',8),(29825,'2023-06-16','09:14:10',12),(29826,'2023-06-16','09:24:11',23),(29827,'2023-06-16','09:34:13',27),(29828,'2023-06-16','09:44:14',32),(29829,'2023-06-16','09:54:15',35),(29830,'2023-06-16','10:04:17',37),(29831,'2023-06-16','10:14:19',37),(29832,'2023-06-16','10:24:20',46),(29833,'2023-06-16','10:34:22',55),(29834,'2023-06-16','10:44:23',48),(29835,'2023-06-16','10:54:25',57),(29836,'2023-06-16','11:04:26',65),(29837,'2023-06-16','11:14:28',66),(29838,'2023-06-16','11:24:29',69),(29839,'2023-06-16','11:34:31',70),(29840,'2023-06-16','11:44:32',73),(29841,'2023-06-16','11:54:34',75),(29842,'2023-06-16','12:04:35',77),(29843,'2023-06-16','12:14:36',80),(29844,'2023-06-16','12:24:38',81),(29845,'2023-06-16','12:34:39',84),(29846,'2023-06-16','12:44:40',85),(29847,'2023-06-16','12:54:42',86),(29848,'2023-06-16','13:04:43',88),(29849,'2023-06-16','13:14:45',89),(29850,'2023-06-16','13:24:46',91),(29851,'2023-06-16','13:34:47',92),(29852,'2023-06-16','13:44:48',92),(29853,'2023-06-16','13:54:50',94),(29854,'2023-06-16','14:04:51',93),(29855,'2023-06-16','14:14:53',94),(29856,'2023-06-16','14:24:54',94),(29857,'2023-06-16','14:34:55',95),(29858,'2023-06-16','14:44:56',97),(29859,'2023-06-16','14:54:58',99),(29860,'2023-06-16','15:04:59',100),(29861,'2023-06-16','15:15:00',89),(29862,'2023-06-16','15:25:01',43),(29863,'2023-06-16','15:35:02',49),(29864,'2023-06-16','15:45:03',21),(29865,'2023-06-16','15:55:04',88),(29866,'2023-06-16','16:05:05',96),(29867,'2023-06-16','16:15:07',89),(29868,'2023-06-16','16:25:08',97),(29869,'2023-06-16','16:35:09',76),(29870,'2023-06-16','16:45:10',48),(29871,'2023-06-16','16:55:11',62),(29872,'2023-06-16','17:05:12',75),(29873,'2023-06-16','17:15:13',80),(29874,'2023-06-16','17:25:14',79),(29875,'2023-06-16','17:35:15',76),(29876,'2023-06-16','17:45:16',73),(29877,'2023-06-16','17:55:17',72),(29878,'2023-06-16','18:05:18',67),(29879,'2023-06-16','18:15:19',66),(29880,'2023-06-16','18:25:20',63),(29881,'2023-06-16','18:35:21',58),(29882,'2023-06-16','18:45:22',57),(29883,'2023-06-16','18:55:23',52),(29884,'2023-06-16','19:05:23',50),(29885,'2023-06-16','19:15:24',45),(29886,'2023-06-16','19:25:25',41),(29887,'2023-06-16','19:35:26',37),(29888,'2023-06-16','19:45:27',31),(29889,'2023-06-16','19:55:28',24),(29890,'2023-06-16','20:05:29',18),(29891,'2023-06-16','20:15:30',12),(29892,'2023-06-16','20:25:31',9),(29893,'2023-06-16','20:35:32',6),(29894,'2023-06-16','20:45:33',5),(29895,'2023-06-16','20:55:34',3),(29896,'2023-06-16','21:05:35',2),(29897,'2023-06-16','21:15:36',2),(29898,'2023-06-16','21:25:37',2),(29899,'2023-06-16','21:35:38',1),(29900,'2023-06-16','21:45:39',1),(29901,'2023-06-16','21:55:40',0),(29902,'2023-06-16','22:05:41',0);

... ein ' repräsentativer' Tag (eigentlich ein willkürlicher Tag)

Du siehst, wenn es Dunkel ist, gibt es keine Einträge. So gibt es auch Definitiv keine Einträge rund um Mitternacht.

mir schwebt vor:

 alter table Solar ADD SumWh INT;  // extra Spalte für die Tagessumme


select sum(Wh) from Solar where Datum="2023-06-16";  -->  4480
select max(ID) from Solar where Datum="2023-06-16";  -->  29902

// syntax unklar
update Solar SET SumWh = (select Sum(Wh) from Solar where Datum = "2023-06-16") where ID = (select max(ID) from Solar where Datum="2023-06-16");
update Solar SET Wh = NONE where ID = (select max(ID) from Solar where Datum="2023-06-16");
update Solar SET time = "23:59:59" ID = (select max(ID) from Solar where Datum="2023-06-16");
DELETE from Solar where Datum = "2023-06-16" AND ID < (select max(ID) from Solar where Datum="2023-06-16");

So müsste das gehen, finde ich aber sehr unelegant - vor allem dass das Datum da als Text drin steht.
Wie kriege ich das Datum da raus?

ich möchte, wie gesagt, ein mal im Monat pauschal alles Älter als 13 Monate bearbeiten lassen. Ein Großteil der Tage wird dann ja wohl bereits portiert sein, es wird sich dann wohl bei jedem Inkrement um lediglich einen Monat handeln.
Aber aus meiner Überzeugung heraus ist es nicht gut den 14. Monat direkt an zu wählen. .... vor allem da die initiale Query dann anders ist als alle anderen.


------------------------------------------------
Die als Beispiel gezeigte Db ist recht einfach - nur ein Wert.
Aber die hier erlernten Fähigkeiten möchte ich auf meine anderen Tabellen anwenden, die sind (ungleich) umfangreicher, jedoch der grundsätzliche Aufbau unterscheidet sich nicht sehr. Aller (paar) minuten kommt ein Wert rein, ID sowie Zeit sind immer zwangsweise aufsteigend ...



Vielen Dank für die Mühe, die ihr Euch mit mir macht!


Micha

 

[Walter]

Das ist Pfusch. Warum nicht sauber eine Summentabelle, dort die Werte reinsummieren und alte Werte, die man nicht mehr braucht, löschen?

 

[MichaF]

Hallo Walter,

das wäre ja viel zu einfach!

*bonk* @ me, ja du hast recht. das ist wesentlich einfacher für diesen Zweck.

das Problem wird sich dann auf später verlegen, wenn ich für die graphische Auswertung (auch!) auf die Zeiträume in dieser (neuen) Tabelle zugreifen will und dann erst mal zwei Queries aus beiden Quellen vereinigen muss*.

CREATE TABLE `Solar_Sum` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `Datum` date DEFAULT NULL,
  `Wh_Sum` int DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

INSERT INTO Solar_Sum VALUES (Datum, Wh_Sum) ("2023-06-16", (select Sum(Wh) from Solar where Datum = "2023-06-16");

Das löst zwar noch nicht die Frage wo ich stecken bleibe:

Wie kriege ich das "2023-06-16" da raus? - will heißen wie kann ich mich datumsweise dursch Solar graben und wie lege ich für alle in Solar enthaltenen Tage ein Datum und eine Summe in Solar_Sum an?
.... ich schreibe diese Frage und mir fällt auf, dass ich das wissen müsste. Komme aber nicht drauf.

-----------------------------------------------------------------------

* mir schwant, dass diese Query, die ich meine, eine total einfache und einmalige Angelegenheit wird.
Es wird wohl darauf hinaus laufen, dass ich einen täglichen cron-job anlegen werde, der mir die Summe des letzten Tages in die neue Tabelle schreibt und ich, falls ich Summen brauche, auf die Summentabelle zugreifen werde.




Gr. Micha

 

[MichaF]

Got it!

INSERT INTO Solar_Sum select NULL, Datum, Sum(Wh) as Wh from Solar where Datum < CURDATE() GROUP BY Datum;

das NULL hat mir am meisten zu schaffen gemacht, ständig hat er sich über

Column count doesn't match value count at row 1

beschwert.

Ich habe jetzt meine Summen-Tabelle die ich täglich anfüllen kann.


Danke Walter für deinen Kommentar!
Danke dabadepdu für deine Mühe.

 

[akretschmer]

Hallo!

Danke für eure Antworten.
Ich habe mich mal zum Partitioning informiert. ich glaube das ist nicht wirklich was ich brauche, ddurch wird meine Db ja nicht wirklich kleiner, liegt nur woanders ...

Mittels Partitionierung kannst Du einfach die Partition(en) löschen, die Du nicht mehr benötigst. Das ist um einiges eleganter, als immer viele Datensätze zu löschen. Ein DETACH der Partition und ein DELETE dieser ist sehr viel einfacher (aus Sicht der DB) als alle Datensätze älter als XXX zu suchen, finden & löschen.

Ansonsten schließe ich mich vollumfänglich @Walter in #5 an.

 

[MichaF]

Ich glaube da fehlen mir ein paar Jahre Erfahrung mit Datenbanken um es zu schätzen wenn/das es die Db einfach hat.

Auch dir natürlich dankeschön. Das partitionieren klinkt interessant für wirklich große Datenbanken.