DBLog - Historische Werte ausdünnen

[rapster]

Ich möchte das ganze möglichst wenig invasiv auf einzelne Devices beschränken und mir rantasten. Prüfen wie lange es dauert, etc.
Deshalb ist der der Include Parameter für mich interessant:

Ich stehe auf dem Schlauch, was eine Database-deviceRegExp sein soll, bzw. warum ich mich als Anwender damit auseinander setzen soll.
Die Regex Syntax unterscheidet sich nach meinem Stand nicht zwischen SQL und Perl.
Für sqlite kann das Paket "Perl regular expressions"

Code Auswählen Erweitern

sqlite3-pcre für sqlite installiert werden.

Wird das im Moment verwendet, wenn es installiert ist? Das Keyword REGEXP habe ich im Quellcode nicht finden können. Demnach nehme ich an, es handelt sich hier um gar keine Regexp sondern um etwas anderes.

Das war glaub ich nur schwierig zu Dokumentieren

Include wird direkt bei der DB-Abfrage ausgewertet:

Code Auswählen Erweitern

SELECT TIMESTAMP,DEVICE,'',READING,VALUE FROM history WHERE ($a[-1] =~ /^INCLUDE=(.+):(.+)$/i ? "DEVICE like '$1' AND READING like '$2' AND " : '') TIMESTAMP < $cmd ORDER BY TIMESTAMP ASC
WHERE ($a[-1] =~ /^INCLUDE=(.+).+)$/i

Exclude wird im Perl-Code erledigt

Exclude kann auch als Include verwendet werden, einfach die RegExp entsprechend umstellen.

Include bleibt aber Include

[Sidey]

Das war glaub ich nur schwierig zu Dokumentieren

Ok verstanden. Ich kenne das unter Like operator und Wildcard operators:

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Mehr geht da ja nicht, somit verwirrend dass von REGEX in der Doku geschrieben wir.

Include bleibt aber Include
[/quote]

Das Exclude auch die Funktion eines Includes erfüllt verstehe ich. Jedoch müssen dann erst mal alle Datensätze aus der Datenbank geladen werden.

Ich habe mir die Abfrage mal genauer angesehen und ein paar Versuche angestellt:

Code Auswählen Erweitern


SELECT TIMESTAMP,DEVICE,READING,VALUE FROM history WHERE DEVICE like 'MYSENSOR_106' AND READING like 'wz.temperature' AND   TIMESTAMP < 'NOW() - INTERVAL 30 DAY' ORDER BY TIMESTAMP ASC;

Das dauert in etwa um die 75 Sekunden bei mir. Manchmal auch um die 65.

Eine kleine Optimierung der Abfrage reduziert die Dauer erheblich auf 5-10 Sekunden:

Code Auswählen Erweitern

SELECT TIMESTAMP,DEVICE,READING,VALUE FROM history WHERE DEVICE = 'MYSENSOR_106' AND READING ='wz.temperature' AND   TIMESTAMP < 'NOW() - INTERVAL 30 DAY' ORDER BY TIMESTAMP ASC;


Ist schon klar, da ja ein genauer Vergleich durchgeführt wird und ich den Like Operator eleminiert habe. Könnte man die Abfrage nicht so anpassen, dass der Like Operator nur genutzt wird, wenn ein % oder _ Operator im Include auch verwendet wird?


Ich hab dann noch ein bisschen weiter experimentiert und auch festgestellt, dass INTERVAL auf eine SQLIte DB nicht unterstützt wird. Aber das ist für meine Tests eher unerheblich gewesen.

Den Mittelwert eines Readings pro Stunde habe ich über Group by und strftime herstellen können:

Code Auswählen Erweitern


SELECT TIMESTAMP,DEVICE,READING,VALUE,AVG(VALUE) as AVG_VAL from history WHERE (DEVICE="MYSENSOR_106" and READING="wz.temperature" and TIMESTAMP < datetime('now', '-300 days')) GROUP BY strftime("%d",TIMESTAMP),strftime("%H",TIMESTAMP) ORDER BY TIMESTAMP ASC;


Die Abfrage hat nun unter 3 Sekunden gedauert.Einzige Haken ist, dass wohl immer der Zeitstempel des letzten Readings der Stunde übernommen wird.

Das sieht dann so aus:

Code Auswählen Erweitern


2016-01-13 18:57:27|MYSENSOR_106|wz.temperature|21.4|21.5573033707865
2016-01-13 19:56:29|MYSENSOR_106|wz.temperature|21.5|21.4174603174603
2016-01-13 20:56:50|MYSENSOR_106|wz.temperature|21.6|21.5702127659574
2016-01-13 21:45:25|MYSENSOR_106|wz.temperature|21.5|22.2723684210526
2016-01-13 22:18:22|MYSENSOR_106|wz.temperature|21.7|21.6769230769231


Besser wäre natürlich, wenn es eher zum Anfang der Stunde übernommen wird. Da habe ich aber gerade keine Idee, sich das filtern lässt.
Andere Zeitabstände sollten mittel Abfrage auf den timestamp und round Funktion eigentlich auch machbar sein...


Grüße Sidey

[rapster]

Ich habe mir die Abfrage mal genauer angesehen und ein paar Versuche angestellt:

Code: [Auswählen]

SELECT TIMESTAMP,DEVICE,READING,VALUE FROM history WHERE DEVICE like 'MYSENSOR_106' AND READING like 'wz.temperature' AND   TIMESTAMP < 'NOW() - INTERVAL 30 DAY' ORDER BY TIMESTAMP ASC;

Das dauert in etwa um die 75 Sekunden bei mir. Manchmal auch um die 65.

Eine kleine Optimierung der Abfrage reduziert die Dauer erheblich auf 5-10 Sekunden:
Code: [Auswählen]

SELECT TIMESTAMP,DEVICE,READING,VALUE FROM history WHERE DEVICE = 'MYSENSOR_106' AND READING ='wz.temperature' AND   TIMESTAMP < 'NOW() - INTERVAL 30 DAY' ORDER BY TIMESTAMP ASC;


Ist schon klar, da ja ein genauer Vergleich durchgeführt wird und ich den Like Operator eleminiert habe. Könnte man die Abfrage nicht so anpassen, dass der Like Operator nur genutzt wird, wenn ein % oder _ Operator im Include auch verwendet wird?

Das sollte sich machen lassen um schonmal etwas schneller zu sein, schaue ich mir an

Ich hab dann noch ein bisschen weiter experimentiert und auch festgestellt, dass INTERVAL auf eine SQLIte DB nicht unterstützt wird. Aber das ist für meine Tests eher unerheblich gewesen.

Bei sqlite wird

Code Auswählen Erweitern

DATE_SUB(CURDATE(),INTERVAL $a[2] DAY)  statt

Code Auswählen Erweitern

NOW() - INTERVAL '$a[2]' DAY verwendet.


Das Ausmisten über DB eigene Funktionen klingt interessant, die Frage ist nur wer sich dafür opfert das für die verschiedenen DB-Typen zu implementieren

[Sidey]

Das Ausmisten über DB eigene Funktionen klingt interessant, die Frage ist nur wer sich dafür opfert das für die verschiedenen DB-Typen zu implementieren

Für mysql finden sich ja viele Beispiele wie z.B. ein 5 oder 15 Minuten Mittelwert errechnet wird.
Ich eine kleine Demo für sqlite3 und 15 Minuten Intervalle erstellt:

SQLITE Fiddle (15 Minuten Intervalle)

Das Feld EVENT. Da es sich um ein Textfeld handelt, lässt sich mithilfe von Replace aktualisieren.
Grüße Sidey

[Sidey]

Ich habe noch ein bisschen weiter darüber nachgedacht.

Folgende Randbedingungen sind mir dabei aufgefallen:

Eine WHERE Abfrage sollte immer so gewählt sein, dass sie den Index nutzt.
Das sind DEVICE, READING und TIMESTAMP

Ablauf des Zusammenfassens
1: SELECT auf die Werte, Gruppieren und Liefern des Mittelwertes

Ich habe meine Beispiel Abfrage dazu um group_concat(VALUE) erweitert. Dadurch werden alle Werte ausgegeben, welche als Mittelwert zusammen gefasst werden. Außerdem habe ich einen Filter auf Device, Reading und Timestamp gesetzt.

Code Auswählen Erweitern


SELECT DEVICE,READING, min(strftime(TIMESTAMP)), avg(VALUE) as AVG_VALUE, replace(EVENT,VALUE, avg(VALUE)) as AVG_EVENT, group_concat(VALUE)
FROM history
WHERE DEVICE="MYSENSOR_106" and READING="wz.temperature" and TIMESTAMP>"2016-11-05 00:00:00"
GROUP BY strftime("%d",TIMESTAMP), strftime('%s', TIMESTAMP) / (15 * 60);
 

Das liefert dann so etwas wie:

Code Auswählen Erweitern

MYSENSOR_106|wz.temperature|2016-11-05 20:17:41|20.7666666666667|wz.temperature: 20.7666666666667|20.6,20.9,20.8


2: INSERT der berechneten Werte

Zwecks einfacherer Analyse, habe ich die Werte in eine Tabelle mit dem Namen History2 geschrieben.
Kann man die Werte vielleicht auch einfach in die Tabelle current schreiben? Gibt es einen Prozess der Daten von current nach history verschiebt oder wie läuft das ab?

Code Auswählen Erweitern


INSERT INTO history2 ( TIMESTAMP , DEVICE , TYPE , EVENT , READING , VALUE , UNIT  )
SELECT  min(strftime(TIMESTAMP)), DEVICE, TYPE, replace(EVENT,VALUE, printf("%.1f",avg(VALUE))) as AVG_EVENT, READING, printf("%.2f",avg(VALUE)) as AVG_VALUE, UNIT
FROM history
WHERE DEVICE="MYSENSOR_106" and READING="wz.temperature" and TIMESTAMP>"2016-11-05 00:00:00"
GROUP BY strftime("%d",TIMESTAMP), strftime('%s', TIMESTAMP) / (15 * 60);


3: Die alten Einträge müssen gelöscht werden. An diesem Punkt ist mir aufgefallen, dass wir keinen Primärschlüssel oder eine eindeutige ID haben, mit der wir die Einträge Identifizieren können. Anhand eines Flags können wir auch nicht ausschließen.

Passend zu meinem Beispiel würde das so funktionieren, da ich ja in history2 geschrieben habe:

Code Auswählen Erweitern


DELETE FROM history
WHERE DEVICE="MYSENSOR_106" and READING="wz.temperature" and TIMESTAMP>"2016-11-05 00:00:00";


Mit temporären Tabellen bin ich unsicher, ob die nicht zu viel Speicher belegen könnten, der vielleicht nicht vorhanden ist.

Transactions helfen uns meiner Meinung nach auch nicht. Sämtliche Werte in perl zu speichern wollte ich eher vermeiden. Dann können wir es auch in eine Temp Tabelle stecken.

4: Damit die aggregierten Werte wieder in die normale History Tabelle gelangen, reicht ein einfacher Import:

Code Auswählen Erweitern


INSERT INTO history ( TIMESTAMP , DEVICE , TYPE , EVENT , READING , VALUE , UNIT)
SELECT  * FROM history2;



Grüße Sidey

[persching]

so, nachdem ich jetzt die Ganzen 17 Seiten durchgelesen habe und keinen Hinweis auf mein Problem gefunden habe poste ich mal:

Wenn ich beispielsweise

Code Auswählen Erweitern

set logdb reduceLog 200 average

durchführe, dann bekomme ich immer (egal bei welcher Zahl) zurück:

Code Auswählen Erweitern

reduceLog executed. Rows processed: 0, deleted: 0, updated: 0, time: 0.76sec

Meine Datenbank ist mittlerweile rund 1GB groß und beinhaltet Daten von einem Jahr. Es ist eine sqlite3 Datenbank auf einem BananaPi M2+ mit Armbian.

Hat jemand eine Idee was ich falsch mache??

[rapster]

In deiner Datenbank sind keine so alten Daten drin.
Hast du eine Möglichkeit mal reinzuschauen?

Taste dich mal von 200 runter...

[persching]

Bei 1 ging es dann...

Aber ich versteh nicht genau warum. Ich hatte zwar noch einen Fehler, weil ich alle Filelogs auf DBLogs umgestellt hatte. Also hatte ich rund 40 defines. Das hab ich am Wochenende geändert und nur noch eine logdb mit allen Einträgen definiert. Die Werte waren auch alle noch da. Ist ja auch irgendwie logisch, weil es gibt ja nur ein Datenbankfile und jedes Device schreibt seinen Eintrag dort rein, egal wie das define in FHEM heißt. Naja, jedenfalls hatte ich für die anderen alten defines auch mal den reduceLog Befehl ausgeführt und es hat auch nicht funktioniert. Und die Abfrage nach dem Min-Datum war 15.11.15, also mehr als 300 Tage.

Jetzt hatte ich jedenfalls die Ausgabe:

Code Auswählen Erweitern

reduceLog executed. Rows processed: 235891, deleted: 118774, updated: 21459, time: 57.05sec

Also alles OK.

[McUles]

Ich habe bei mir inzwischen das Problem das ich mir keine Plots mehr anlegen kann da die Liste der Readings zu groß wird.
Bei über 7400 Readings kapituliert der Browser. Das sollte man mit zwei Abfragen machen vielleicht, zuerst die Devices und nach Auswahl dessen erst die Readings auslesen. Das sollte die Liste um einiges abspecken.

Eine kleine Änderung habe ich bei mir schon mal vorgenommen, das speckt die Liste auf ca. 500 readings ab.

93_DbLog.pm, Zeile 1995:

Code Auswählen Erweitern

my $query = "select device,reading,value from current where device <> '' group by device, reading order by device,reading";

Schaue mir das noch ein wenig weiter an, vielleicht bekomme ich noch etwas optimiert.
Mit Perl hab ich noch nichts gemacht bisher, also mal schauen

[JoeALLb]

deaktiviere die current - tabelle, dann musst du das reading halt von hand eintragen, dafür gehts immernoch problemlos.

[McUles]

Im Script deaktivieren oder wie meinst du?
Sollte aber jetzt auch nicht umbedingt die Lösung sein. Wenn man die Dropdowns nutzen kann ist doch viel schöner

[JoeALLb]

Sollte aber jetzt auch nicht umbedingt die Lösung sein. Wenn man die Dropdowns nutzen kann ist doch viel schöner

Bei 7400 Readings? Ich denke, da wird dir niemand helfen können

Ich nutze dafür:

Code Auswählen Erweitern

attr DBLdevice DbLogType History

[McUles]

Mit der Änderung die ich oben geschrieben habe, sind es nur noch etwas über 500.
Da sind irgendwie ein paar Dubletten enthalten, warum auch immer.

[DerFrickler]

Aus der Referenz zu reduceLog: "Reduziert historische Datensaetze, die aelter sind als <n> Tage auf einen Eintrag pro Stunde (den ersten) je device & reading."

Besteht auch die Möglichkeit die Datensätze auf 2 pro Tag zu reduzieren? Den Ersten und den Letzten Eintrag?

Gruß und Danke!

[rapster]

Du kannst noch das Schlüsselwort "average" dranhängen, dann wird der Durchschnitt aller numerischen Werte einer Stunde berechnet.

Erster und letzter Wert einer Stunde ergibt für mich nicht wirklich Sinn, was ist da der Hintergrund?