SQL-Adapter, Werte nach Alter verdichten

[tomrey]

Hi all,
als Nicht-SQL-Spezialist suche ich eine Möglichkeit, die per SQL-Adapter in Mysql geloggten Werte nach Alter zu verdichten. Also z.B. die Außentemperatur pro Tag im laufenden Monat laufend bei Änderung, nach 1 Monat nur noch Höchst-/Niederst- und Durchschnittstemperaturen pro Tag und von Vorjahren ggf. diese nur noch pro Woche oder Monat vorzuhalten. Dabei sollten die Intervalle und Verdichtungstypen allgemein und je Adapter und Datenpunkte parametrierbar sein.
Hat so etwas jemand schon gemacht?
Wäre dies eine Anregung für die Weiterentwicklung des SQL-Adapters?
Dank & Gruß

[OliverIO]

@tomrey

Generell ist das machbar
Datenbanken und SQL sind in der Regel darauf ausgelegt kontinuierliche Aufzeichnungen zu haben und dann bei der Abfrage die entsprechenden Aggregation durchzuführen.
In deinem Fall wären das zwei Abfragen, einmal für den laufenden Monat beziehungsweise meinst du sicherlich für die letzten X Tage
Und einmal für die Höchst und Tiefstwerte für die Zeit davor.
Wenn das beispielsweise alles in einem Diagramm angezeigt werden soll, kann man diese beiden Abfragen auch mit einem UNION zusammenfassen.

Wenn du eine echte Bereinigung der Tabellen haben möchtest, könnte man die Höchst und Tiefstwerte mit einer anfüge Abfrage hinzufügen (insert into) und die dann überflüssigen Einzelwerte löschen.

Wenn Festplatten Speicherplatz nicht unbedingt so die Rolle spielt und die Tabellen richtig indiziert sind, würde ich die erste Möglichkeit ohne löschen empfehlen. Lieber dann irgendwann mal alte Daten, wenn sie wirklich nicht mehr benötigt werden dann löschen. Beispielsweise nach einem Jahr

Wenn du dich für das Löschen entscheidest, würde ich die Werte aber in eigenen Tabellen vorhalten

[Azrael42]

@tomrey
Ich gruppiere regelmäßig die geloggten Werte in der MariaDB.
Ich zeichne viele Werte im 20 Sekunden Takt auf und habe die Historie der letzten Jahre in der DB gesammelt. Die ich natürlich auch ungern verlieren möchte. Aktuell laufen pro Jahr ca 30 Millionen Datensätze auf, Tendenz steigend. Da mein winziger DB Server damit überfordert ist, aggregiere ich die Werte ab und an.
Das mache ich über einen GROUP BY auf eine Zeitperiode. Im ersten Jahr sind es bei mir 10 Min, dann immer länger.
Da das Skript nicht so häufig läuft, mache ich das per Hand und für alle ID's.

[tomrey]

Danke für eure Infos.
Ich werde mit einer zusätzlichen, verdichteten Datenbank arbeiten und die Originale in der iobroker-SQL (nach Verdichtung) vor einem bestimmten Zeitpunkt löschen.
Beispiel Außentemperatur:
Da interessieren mich Tages-/Wochen-/Monats-Höchst- und Niedrigstwerte, sowie die Durchschnitte pro Stunde.
Bei der Verdichtung muß ich auch neue timestamps generieren.
Da ich kein SQL-Erfahrungen habe, suche ich ein tool für die Konfiguration der SQL-Befehle.
Grüße

[OliverIO]

@tomrey

ChatGPT.
Beschreibe die tabellenstruktur und erkläre dein Ziel.
Dann wird dir ChatGPT einen vorschlagfür die neue Tabelle machen und auch die Befehle für das anlegen und die Abfragen erstellen.
Versuche aber auch selbst die Befehle zu verstehen. Es ist nicht so schwer.
Schau in die Dokumentation von Marias/mysql, kannst dir aber auch alles von ChatGPT erklären lassen.
Wenn du keine Ahnung hast füge
ELI5
Hinzu. Das bedeutet "explain like I'm 5"
Das ist so eine Art Standard geworden für super Basic Erklärung.

[Azrael42]

@tomrey

SELECT 
	id,
	ROUND((ts/100000),0)*100000 AS ts,
	-- FROM_UNIXTIME(TS/1000) AS CTS,
	-- Date(FROM_UNIXTIME(TS/1000)) AS Datum,
	round(Avg(Val),2) AS val,
	1 AS ack,
	1 AS _from,
	0 AS q		
FROM iobroker.ts_number
	  WHERE val is not null
		  and id = 163
GROUP BY id, round(ts/100000, 0)*100000
ORDER BY 2 DESC

Als Starthilfe hier mal mein Script, das mir den 10 minütigen Durchschittswert eines Temperatursensors ausgibt. Hab auch mal die Hilfsspalten drin gelassen, die verwende ich beim Gruppieren in andere Perioden.