MySQL Werte löschen off-off... on-on... aber nicht on-off oder off-on

[supergrobi]

Hallo Forum

gibt es eine Möglichkeit per SQL Abfrage  gleiche aufeinanderfolgende Zustände zu löschen?
Es soll also ein Relais Aktor, der jede Minute on sendet solange gelöscht werden, bis ein Zustandswechsel von on nach off erfolgt. Dieser soll dann sowohl das on, als auch das off erhalten bleiben. Genauso auch bei den off Zustanänden.

Durch meine Dummheit, hab ich wochenlang den Zustand jede Minute in die Datenbank geschrieben und diese damit vollgemüllt. Jetzt hab ich natürlich das Event-on-Change attribut aktiviert. Jedoch hab ich immer noch die alten Werte.

Kann mir jemand helfen?

lg
Thomas

[supergrobi]

hmm schade...

bin ich hier im falschen Unterforum?

[Beta-User]

...keine Ahnung, aber m.E. ist das auch keine wirkliche FHEM-Frage, sondern ein mySQL-Thema...

Ich bin sowas von Datenbank-ahnungslos, aber eventuell hilft das hier: https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows

(Nicht verhauen, wenn das völlig die falsche Richtung ist) .

[Frank_Huber]

was auf jeden Fall geht:
- exportiere und lösche alles vor dem Setzen des event-on-change
- filtere lokal in Excel etc...
- importiere die gefilterten Werte.

[ergerd]

Hallo supergrobi,

ich wüsste auch nur die Methode über die Suche nach Dubletten das evtl. zu erreichen.

Schau mal ob du mit folgendem Statement die zu löschenden Datensätze triffst. Als history.device gibst du deinen Devicenamen an, bei
MAKEDATE den letzten Tag (als Nummer des Tages im Jahr!) vor der Umstellung auf Event-On-Change.
Wenn du sicher bist das die richtigen Datensätze gefunden werden ersetzt die select * mit delete.

Code Auswählen Erweitern


select * from history
left outer join (
  select min(timestamp) as timestamp, device, type, reading, value
    from history
    group by device, type, reading, value
  ) as KeepRows ON history.timestamp = KeepRows.timestamp
               AND history.device = KeepRows.device
               AND history.reading = KeepRows.reading
               AND history.value = KeepRows.value
  WHERE history.device = 'PCA301_boiler_kueche'
     AND DATE_FORMAT(history.timestamp, '%Y-%m-%d') <= MAKEDATE(2017,327 )
     AND KeepRows.timestamp IS NULL
    ;


Ob MAKEDATE den richtigen Tag ausgibt kannst du einfach testen mit:

Code Auswählen Erweitern


select MAKEDATE(2017,327 );


In dem Beispiel sollte 2017-11-23 herauskommen.

Grüße
Rainer

[kadettilac89]

Hallo supergrobi,

ich wüsste auch nur die Methode über die Suche nach Dubletten das evtl. zu erreichen.

Schau mal ob du mit folgendem Statement die zu löschenden Datensätze triffst. Als history.device gibst du deinen Devicenamen an,

Ich denke das löst das Problem nicht, müsste aber selber nochmal testen. In deinem Select ist zwar der Timestamp drin, jedoch wird nicht berücksichtigt, dass bei einem on-off-on Statuswechsel das neuere "on" nicht gelöscht werden darf.

Ich lösche selber ähnliche Dupletten, jedoch per Perl-Script. Ich lese die Sätze ein, und in einer Schleife vergleiche ich immer ob sich der Satz zum Vorgänger geändert hat - Timestamp ausgenommen, da dieser anders ist. Hier ist nur die Sortierung wichtig. Ich schreibe bewusst ohne event-*reduzierugn um viele Plot-Sätze und damit keinen Abriss zu haben. Alles älter als eine Woche wird dann wieder gelöscht.

Select mit Subselect war viel langsamer, ich bin mir nicht sicher ob mein damaliges Select mit 2 Subebenen noch optimiert werden kann. Wenn das SQL-Statement nicht löst was du brauchst kann ich dir ggf. später das Script geben. Müsstest nur etwas anpassen.

[DS_Starter]

Der Ansatz von kadettilac89 hat mich auf die Idee gebracht eine solche Hilfsfunktion in DbRep einzubauen. Dann hätte der User eine toolgestützte Möglichkeit solche Datensätze zu löschen und könte dabei die gewünschten Zeitgrenzen einstellen usw.

Falls daran interesse besteht würde ich das mal mit einplanen und kadettilac89 .... wenn du magst kann ich deine scriptidee mit verwenden.

Gruß,
Heiko

[kadettilac89]

Der Ansatz von kadettilac89 hat mich auf die Idee gebracht eine solche Hilfsfunktion in DbRep einzubauen. Dann hätte der User eine toolgestützte Möglichkeit solche Datensätze zu löschen und könte dabei die gewünschten Zeitgrenzen einstellen usw.

Falls daran interesse besteht würde ich das mal mit einplanen und kadettilac89 .... wenn du magst kann ich deine scriptidee mit verwenden.

Gruß,
Heiko

Hi ich kann dir gerne das Script geben. Ist sowieso in Perl.

[DS_Starter]

Ja gerne, dann würde ich schauen wie ich das Modul eingebaut bekomme.
Aber das dauert wie immer etwas Zeit. Wenn es dann fertig ist nutzt es der Allgemeinheit.

[kadettilac89]

Wenn das SQL-Statement nicht löst was du brauchst kann ich dir ggf. später das Script geben. Müsstest nur etwas anpassen.

Da scheinbar Interesse am Script ist ... hier angehängt.

Was macht es? Es löscht wiederkehrende Einträge eines Devices dessen Wert sich nicht verändert hat.

Beispiel:
"2017-11-24 19:33:08";"HMTempSensor2";"CUL_HM";"humidity: 66";"humidity";"66";"%"
"2017-11-24 19:30:08";"HMTempSensor2";"CUL_HM";"humidity: 64";"humidity";"64";"%"
"2017-11-24 19:25:16";"HMTempSensor2";"CUL_HM";"humidity: 64";"humidity";"64";"%"
"2017-11-24 19:12:00";"HMTempSensor2";"CUL_HM";"humidity: 64";"humidity";"64";"%"
"2017-11-24 19:03:55";"HMTempSensor2";"CUL_HM";"humidity: 64";"humidity";"64";"%"

Zeilen in bold werden gelöscht da sich der Wert nicht geändert hat. Der Wert ganz oben (value 66) wird wieder behalten da der Wert anders ist. Es wird immer der Wert beibehalten der als erstes geschrieben wurde, sprich wann der Zustand eingetreten ist.

Im Script sind folgende Variablen:
  my $age = 14;  <<< Werte älter als xx werden geprüft und ggf. ausgedünnt
  my $age_st = 45; <<< Werte jünger als xxx ....
  my $configfilename= "/opt/fhem/db.conf";  <<< Configfile für DB-Log

In meinem Script werden somit alle Sätze zwischen 10.10. und 10.11. geändert wurden. Jetzt nicht auf den Tag genau festnageln, will jetzt nicht im Kalender zählen. Dieser Zeitraum ist mehr als ausreichend da ich das Ausdünnen einmal die Woche laufen lasse. Lasse es nur überlappend da es sowieso nur ein paar Sekunden läuft.

Datei liegt bei mir in /usr/local/bin/
Definition meines "AT"

Code Auswählen Erweitern


defmod at_dblog_reduce_dupl_14d at *23:30:00 {if ($wday == 1){system ("/usr/local/bin/fhem_db_dupl_14d.pm &")} }
attr at_dblog_reduce_dupl_14d DbLogExclude .*


Initial müsstest du entsprechend die beiden oben genannten Zeilen ändern.

Was gibts noch zu sagen:
- Ich übernehmen keinerlei Haftung. Macht ein Backup und testet.
- Je nach DB-Größe und Aufwand kann die DB oder sogar der Raspberry /PC crashen. Bei mir ist nichts passiert. Habe ohne Scheiben gearbeitet. Sollte es dennoch passieren ... . in Häppchen arbeiten lassen. Z. B. Alter 400 - 450 Tage ... und so bis 50 - 0 Tage abarbeiten
- Bei mir löscht das Script ca. 5000 Sätze pro Lauf in der DB mit ca. 1.500.000 Rows in 7 - 10 Sekunden
- Das Script ist KEIN FHEM Modul sondern ein normales Perl-Script auch wenn die Endung PM ist.
- Script schreibt ins Fhem-Log wie viele Zeilen gelöscht wurden, und wie lange es lief (in sec.)
- Rechte X - Ausführbar setzen
- Script muss für FHEM ausführbar sein
- Script löscht redundante Events, dennoch macht es mehr Sinn mit event-on-* Attributen die Einträge zu reduzieren.
- Keine Einschränkung auf DEvice oder REading. Wenn das gewünscht ist muss das im SQL-Teil angepasst werden.

@DS_Starter, ich kontaktiere dich per PM, habe ein paar Ideen und Kommentare dazu.

[borsti67]

habe mir das Script noch nicht angesehen, möchte aber anregen (falls nicht eh schon so), dass man die "Gleichheit" immer auf genau einen Tag bezieht.
Ich wäre an der Funktion sehr interessiert, möchte aber natürlich keine Log-Abbrüche (lasse mir mit AddLog extra immer um Mitternacht den Status auf den neuen Tag herüberziehen).
Wenn man z.B. einen Schalter loggt, kann es ja durchaus sein, dass der tagelang nicht betätigt wird, dann möchte man aber ja kein leeres Plotfenster sehen, sondern eine Null-Linie.

[kadettilac89]

habe mir das Script noch nicht angesehen, möchte aber anregen (falls nicht eh schon so), dass man die "Gleichheit" immer auf genau einen Tag bezieht.
Ich wäre an der Funktion sehr interessiert, möchte aber natürlich keine Log-Abbrüche (lasse mir mit AddLog extra immer um Mitternacht den Status auf den neuen Tag herüberziehen).
Wenn man z.B. einen Schalter loggt, kann es ja durchaus sein, dass der tagelang nicht betätigt wird, dann möchte man aber ja kein leeres Plotfenster sehen, sondern eine Null-Linie.

Du hast natürlich Recht. Darum lösche ich nur Sätze älter als 14 Tage. Mich interessieren idR ältere Sätze nicht, möchte sie dennoch für eine Weile in der DB lassen. Man (!me) kann natürlich im SQL bestimme Devices ausnehmen. Es muss auch jedem klar sein, dass es sich um ein Ansatz der ein konkretes Problem eines einzelnen Benutzers löst. Der TE hat jedoch genau nach sowas gefragt.

Ich habe das Script geteilt für den Fall dass es jemand für sich nutzen will. Mit allen Einschränkungen.

[DS_Starter]

Hallo zusammen,

nochmal danke an kadettilac89 für die Anregung.
Ich schau mal das ich eine solche Funktionalität in DbRep einbaue. Wer das Modul kennt, weiß ja wie es arbeitet, dass die zu berücksichtigenden Timeselektionen, Devices, Readings usw. eingegrenzt werden können.

Verschiedene Gesichtspunkte/Rahmenbedingungen (borsti67) versuche ich auch zu berücksichtigen.

Wenn ich das Modul weiterentwickelt habe, kommuniziere ich es in diesem DbRep-Thread:
https://forum.fhem.de/index.php/topic,53584.0.html

schönen Abend und Grüße
Heiko

[supergrobi]

Hallo Forum,

es freut mich, das hier leben in den Thread gekommen ist und dass ihr euch meines Problems annehmt. Vielen Dank schon mal.
Jedoch hätte ich eine kleine Anmerkung: Es ist Ähnlich wie Borsti67 schon sagte. Es wäre schön, wenn bei allen aufeinanderfolgenden Werten der erste und der letzte Wert erhalten bleibt. Also z.B. bei Türschaltern OFF, ON,ON,ON,ON,OFF: hier bitte nur zwei ON aus der Mitte löschen, die Übergänge OFF,ON und ON,OFF stehen lassen. So sieht man in einem Plot eben keine "schrägen" Linien.
Ähnlich auch bei Temperaturen. Dann sieht man bei z.B. Sollwerten für die Heizung eine gerade Linie solange wie der Sollwert anstand.

lg
Thomas

[Frank_Huber]

So sieht man in einem Plot eben keine "schrägen" Linien.
Ähnlich auch bei Temperaturen. Dann sieht man bei z.B. Sollwerten für die Heizung eine gerade Linie solange wie der Sollwert anstand.

DAS kriegst aber im Plot mit den Linientypen schon hin.
Nur so nebenbei bemerkt.