DBLog - Historische Werte ausdünnen

[DerFrickler]

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

ganz einfach, die Differenzbildung. Oder im Fall von Zählerdaten die Berechnung des Tagesverbrauchs.

[McUles]

@DerFrickler
dich findet man doch auch überall oder?

[rapster]

sry

ganz einfach, die Differenzbildung. Oder im Fall von Zählerdaten die Berechnung des Tagesverbrauchs.

Sry, evtl. bin ich in dem Thema nicht wirklich drin, aber verstehe den Sinn immer noch nicht

Bsp. Logdaten nach reduLog mit 2 Werten je Stunde:
..
12:59:59 - 10000,00 kWh
13:00:01 - 10000,01 kWh
13:59:59 - 11000,00 kWh
14:00:01 - 11000,01 kWh
14:59:59 - 12000,00 kWh

Welchen Vorteil habe ich wenn der erste und der letzte Wert einer Stunde im Log ist?
Der Stundenverbrauch lässt sich auch nur durch den ersten Wert einer Stunde berechnen...

[DerFrickler]

sry
Sry, evtl. bin ich in dem Thema nicht wirklich drin, aber verstehe den Sinn immer noch nicht

Bsp. Logdaten nach reduLog mit 2 Werten je Stunde:
..
12:59:59 - 10000,00 kWh
13:00:01 - 10000,01 kWh
13:59:59 - 11000,00 kWh
14:00:01 - 11000,01 kWh
14:59:59 - 12000,00 kWh

Welchen Vorteil habe ich wenn der erste und der letzte Wert einer Stunde im Log ist?
Der Stundenverbrauch lässt sich auch nur durch den ersten Wert einer Stunde berechnen...

Es geht hier um den ersten und den letzten Wert des Tages. Anhand dieser beiden Daten kann man z.B. den Tagesverbrauch ermitteln. Die ganzen Zwischenwerte sind lediglich an dem Tag interessant an dem sie gemessen werden, am Folgetag aber nicht mehr.

Bleiben wir bei Deinem Beispiel:

..
12:59:59 - 10000,00 kWh
13:00:01 - 10000,01 kWh
13:59:59 - 11000,00 kWh
14:00:01 - 11000,01 kWh
14:59:59 - 12000,00 kWh

Bei diesen Daten mag es am selben Tag um 14:00:01 noch interessant sein dass der aktuell gemessene Wert 11000,01 kWh beträgt, am Tag danach sind aber nur die Werte 12:59:59 - 10000,00 kWh und 14:59:59 - 12000,00 kWh von Interesse. Diese ergeben dann den Tagesverbrauch von 12000,00 - 10000,00 = 2000,00 kWh. Wenn man das ganze über zwei Jahre verfolgt (die Vorjahresdaten als Vergleichswert nutzt), dann kommt da schon einiges an Daten zusammen. Und da ich für vergangene Tage lediglich an Tageswerten interessiert bin, reichen der erste und der letzte Wert am Tag.

Im Grunde könnte man jetzt auch weitergehen und den ersten und letzten Wert eines Monats beibehalten um dann den Vormonatsverbrauch zu bestimmen.

[DerFrickler]

@DerFrickler
dich findet man doch auch überall oder?

oder eher nicht... ich bin nicht der von der-frickler.net

[rapster]

Okay, jetzt weiß ich was du vorhast

Aber den Tagesverbrauch kannst du doch auch berechnen wenn du jeweils nur die ersten Werte hast, oder spielt da z.B. delta-d in SVG nicht mit?

07.12.2016 - 00:00:01 - erster Wert 07.12
08.12.2016 - 00:00:01 - erster Wert 08.12

08.12 - 07.12 = Tagesverbrauch.

Was genau ist der Mehrwert, wenn in der DB jetzt noch ein Eintrag am 07.12.2016 - 23:59:59 zu finden ist?

[DerFrickler]

Ich kann es dann in Verbindung mir DbRep und diffValue nutzen.

[rapster]

OK schaue ich mir an, evlt. optional mit Parameter damit reduceLog nach Bedarf den letzten Wert der Stunde bzw. lezten Wert des Tages unangetastet lässt.

Nach Weihnachten aber

[DerFrickler]

eilt nicht, danke!

[Xcoder]

Hallo,
In meinem Datenhaufen gibt es einige Devices welche oft gleiche Werte haben um dann gelegentlich auf andere Werte wechseln. Z.B. Stromverbrauch ist für einige Zeit 0 und wechselt dann auf irgend einen Wert.

Ich suche eine verlustfreie Methode um die Daten zu reduzieren indem für ein bestimmtes Device alle Konstanten Werte, ausser der erste und letzte gelöscht werden. Noch schicker wäre, wenn bei langen konstanten Phasen, z.B. ein Wert pro Stunde erhalten bleibt.

Da gibt es sicher ein SQL Trick mit JOIN oder ähnlich, aber da bin ich nicht so fit.

Aktuelle Daten begrenze ich mit DbLogExclude  und minInterval auf 3600 sec. Schick wäre das aber auf ältere Daten anwenden zu können. Kann mir da jemand auf die Sprünge helfen?

Gruss, Xcoder

[AB1970]

Nachdem ich mich durch diesen Thread gekämpft hatte, war für eigentlich nur noch eins von Interesse:
Wie lange braucht denn mein RPI2?

Hier nun einen Anhaltspunkt:
4,4 Millionen Datensätze in 60 min:

2017.01.10 23:17:25 3: DbLog logdb: reduceLog executed. Rows processed: 4402660, deleted: 4371573, updated: 188917, time: 3289.00sec

Hoffe es hilft dem einen oder anderen

Danke an alle die hierzu beigetragen haben, ein tolles Feature!

[Sailor]

Hallo Frickler

Und da ich für vergangene Tage lediglich an Tageswerten interessiert bin, reichen der erste und der letzte Wert am Tag.
Im Grunde könnte man jetzt auch weitergehen und den ersten und letzten Wert eines Monats beibehalten um dann den Vormonatsverbrauch zu bestimmen.

Hast Du dir schon mal das Modul "ElectricityCalculator" oder "GasCalculator" und dessen Readings angeschaut?

Gruß
    Sailor

[AB1970]

Hallo,
ein ähnlicher Usecase wie der Stromverbrauch: Meine Fritzbox meldet per TR064 die Internetnutzung (Volumen) in minütlichen Abständen. Aber nur als Tageswerte um Mitternacht wird resetted.
Hier wäre es toll, wenn ich per reduceLog den Max täglichen Wert oder aber stündlichen Wert behalten könnte.
Mein Verständnis ist das dies im Moment nicht geht, oder?

Grüsse
AB1970

   

[rapster]

Code Auswählen Erweitern

set DBLOG reduceLog 10 include=FritzBoxDevice:FritzBoxReading
set DBLOG reduceLog 10 average=day exclude=FritzBoxDevice:FritzBoxReading


So würdest du dein Internetnutzungs Reading auf den ersten Wert jeder Stunde reduzieren und alle anderen Werte in der db auf den Tagesmittelwert. (Ältere Readings als 10 Tage)
Bei der Internetnutzung würdest du die Zeit (readings) seit dem ersten reading nach 23:00 Uhr bis Mitternacht verlieren.

EDIT: nur "average", nicht "average=day" reduziert auf den Mittelwert je Stunde, statt den Tagesmittelwert.

[Sidey]

Ich habe mir das Thema Logaggregierung ohne Verarbeitung der Daten in Perl noch mal vorgenommen und das ganze in eine Funktion gepackt.

Vorüberlegungen dazu stehen in meinen Beitrag vom November:
https://forum.fhem.de/index.php/topic,41089.msg522330.html#msg522330

• Ich habe die Abfragen für SQLite erstellt. Die SQL Abfragen sind nicht mit MySQL oder Posrgress kompatibel. Für diese Datenbanken müsste noch etwas erweitert werden.
  
• 2. Ich habe eine eigene Funktion erstellt, da im DBLog Modul derzeit einige Änderungen laufen und das ganze noch eher in einer Testphase ist.
  
• 3. Bei mir hat die Funktion exakt das gemacht, was ich wollte. Dennoch solltet ihr das ganze nur an Testdaten ausprobieren.
  
• 4. Die Funktion avgDBlog erwartet die folgenden 4 Parameter: 
  
  
  
  • Die Definition des dblog Gerätes. Von hier wird der Datenbanktyp und die Anmeldedaten geholt
  • Eine reguläre Expression um sowohl das Gerät und auch das Reading zu definieren, welches verarbeitet werden soll, gefolgt von einem : nachdem die Zeitspanne der Aggregation angegeben wird(1-59 Minuten derzeit möglich)
  • Der Zeitstempel, ab dem aggregiert werden soll
  • Der Zeitstempel, bis zu dem aggregiert werden soll
• Die Funktion schreibt ein paar Debug Meldungen in das globale FHEM Log

Beispiel eines Aufrufes um alle Geräte, die mit dem Namen THN beginnen, für das Reading, welches mit temp beginnt alle 15 Minuten einen Mittelwert bilden. Beim Aufruf müssen alle Parameter angegeben werden. Wird einer weg gelassen, wird das zu fehlern führen.

Code Auswählen Erweitern


{ avgDBlog('LoggingDB','THN.*:temp.*:15','2017-01-15 00:00:00','2017-01-15 23:59:59') };



Anbei auch die Funktion:

Code Auswählen Erweitern



# Test reload 99_myUtils; { avgDBlog('LoggingDB','THN.*:temp.*:10','2017-01-15 00:00:00','2017-01-15 23:59:59') };
sub avgDBlog
{
  # <device-regexp>:<reading-regexp>:<aggregations zeitraum>
  my ($dblogdevice,$arg,$tsmin,$tsmax) = @_;


  my ($deviceregexp, $readingregexp, $aggregation) = split (":",$arg);
  my @devices=devspec2array("$deviceregexp");
  if ( InternalVal($dblogdevice,'DBMODEL','unknown') ne 'SQLITE')
  {
  Log3 "DB AVG:", 3, "Database model from device $dblogdevice is not supported now";
  return NULL;
  }
 
  my $dbconn=InternalVal($dblogdevice,'dbconn','');
  my $dbuser=InternalVal($dblogdevice,'dbuser','');
 
  Log3 "DB AVG:", 3, "params: $deviceregexp, $readingregexp, $aggregation";

#  my $dbh = DBI->connect_cached("dbi:SQLite:dbname=/opt/fhem/fhemtest.db", "", "", { PrintError => 0 });
  my $dbh = DBI->connect_cached("dbi:$dbconn", $dbuser, '', { PrintError => 0 });
 
  Log3 "DB AVG:", 3, "Connection to db $dbconn established for pid $$";
  $dbh->do("CREATE TABLE IF NOT EXISTS history2 (TIMESTAMP TIMESTAMP, DEVICE varchar(64), TYPE varchar(64), EVENT varchar(512), READING varchar(64), VALUE varchar(128), UNIT varchar(32))");

  #  Schritt 1  - alle Devices abholen, welche $arg ensprechen
  my $avg_get=$dbh->prepare(
     "INSERT INTO history2 (TIMESTAMP , DEVICE , TYPE , EVENT , READING , VALUE , UNIT)" 
    ." SELECT min(strftime(TIMESTAMP)) as newTS, DEVICE, TYPE, replace(EVENT,VALUE, printf('%.1f',avg(VALUE))) as AVG_EVENT, READING, printf('%.1f',avg(VALUE)) as AVG_VALUE, UNIT"
          ." FROM history WHERE DEVICE=? and READING=? and TIMESTAMP>=? and TIMESTAMP<=? "
          ." and NOT EXISTS (SELECT 1 FROM history2 WHERE DEVICE=? and READING=? and TIMESTAMP>=? and TIMESTAMP<=?)"
          ." GROUP BY strftime('%d',TIMESTAMP),strftime('%s', TIMESTAMP)/ (?  * 60)"
        );
  my $hist_del=$dbh->prepare("DELETE from history WHERE DEVICE=? and READING=? and TIMESTAMP>=? and TIMESTAMP<=?");
  my $hist_add=$dbh->prepare("INSERT INTO history SELECT * from history2 WHERE DEVICE=? and READING=? and TIMESTAMP>=? and TIMESTAMP<=? ");
  my $hist2_del=$dbh->prepare("DELETE from history2 WHERE DEVICE=? and READING=? and TIMESTAMP>=? and TIMESTAMP<=?");
   
 
  #my $tsmin='2017-01-15 00:00:00';
  #my $tsmax='2017-01-15 23:59:59';

  # Schleife über alle gefunden devices um readings zu finden
  foreach my $device (@devices)
  {
  Debug "matched device : ".Dumper($device);
if (defined $defs{$device})
{
   foreach my $dreading (keys %{$defs{$device}{READINGS}}) {
         Debug "compairing readings for $device reading: $dreading";
        if ($dreading =~ $readingregexp) {
          Debug " readings $dreading matches";
        # Valides Reading gefunden, Daten abfragen und in neue Tabelle einfügen
        $avg_get->execute($device,$dreading,$tsmin,$tsmax,$device,$dreading,$tsmin,$tsmax,$aggregation);
        Debug "rows added to history2:".$avg_get->rows;
        # Datensätze in history löschen
        $hist_del->execute($device,$dreading,$tsmin,$tsmax);
        Debug "rows deleted from history:".$hist_del->rows;
        # Aggregierte Datensätze aus history2 in history eintragen
        $hist_add->execute($device,$dreading,$tsmin,$tsmax);
        Debug "rows added to history:".$hist_add->rows;
        # Datensätze aus history2 entfernen
        $hist2_del->execute($device,$dreading,$tsmin,$tsmax);
          Debug "rows removed from history2:".$hist2_del->rows;
        }
       }
}
  } 
}



Nun zum Unterschied zum aktuellen reduceLog:

1. Es können nur Einträge in der Datenbank zusammengefasst werden, die auch in FHEM existieren. Gleiches gilt für Readings.
Da ich die Geräte und Readings aus FHEM abfrage, benötigt die Funktion wesentlich weniger CPU und IO Ressourcen. Dafür entfällt jedoch die Möglichkeit Daten zu aggregieren, zu denen es keine Gerätedefinition in FHEM gibt.

2. Reducelog verarbeitet alle Datensätze von Begin bis -x Tagen. Es versucht also immer Daten zu aggregieren, die es bereits aggegiert hat.
In dieser Funktion, kann start und ende angegeben werden. Daten die man also schon mal aggregiert hat, braucht man erst gar nicht mehr einlesen.

3. Reducelog lädt alle Einträge von der Datenbank in Perl, verarbeitet die Daten dort, und schreibt diese einzeln wieder in die Datenbank. Die Beigefügte Funktion benötigt immer 4 Datenbank Aufrufe, egal wie viel Daten aggregiert werden. Die Aufrufe werden dann von der Datenbank ohne Perl Beteiligung verarbeitet.

4. Die Zeitspanne für die Aggregation kann von 1-59 Minuten angegeben werden

5. Die Funktion läuft wesentlich schneller ab und kann auch problemlos in einen eigenen Prozess verlagert werden, was FHEM dann nicht mehr blockiert.


Was haltet ihr von diesem Ansatz nun?