Arbeit(kwh) für Zeiträume durch Abfrage berechnen?

[Pete0815]

Hallo Zusammen,
beitreibe eine Datenbank wo verschiedenste Informationen des SmartHomes einfließen.
Darunter auch die aktuelle Leistung der Photovoltaikanlage auf dem Dach also Watt.
Der Eintrag in die Datenbank erfolgt hier üblicherweise alle 3 Sekunden.

Um jetzt hieraus eine elektrische Arbeit zu berechnen, kann ich mir eine Kombination aus Count und Sum vorstellen um jeden Dateneintrag für feste 3 Sekunden in der Berechnung (Ergebnis-Count x 3 Sekunden) zu berücksichtigen. Also: elektrische Arbeit = SummeDatenpunkte(W) x Ergebnis-Count x 3 Sekunden

Jetzt habe ich aber auch Geräte, die im Normalfall keinen festen zyklischen Datenpunkt erzeugen. Hier ist also eine ergänzende Auswertung für jeden Datenpunkt notwendig um die Differenz des Zeitstempels zum nächsten Datenpunkt zu erfassen.

Lande also bei einer Integralfunktion mit variablen dt

Nun bin ich auf der Suche, wie ich das möglichst optimal in einer QueryAbfrage umsetze. Gibt es evtl. sogar Standardfunktionen die ich dazu nutzen kann? Habe bzgl. Integral leider nichts bisher gefunden.

Danke vorab!

 

[dabadepdu]

Zeitstempel zwischen verschiedenen Datensätzen zu vergleichen ist relativ teuer.
Mit Deiner DB wahrscheinlich besonders (außer es ist eine brandneue(hüstl) 8er Version)

V1: Messwert und Dauer gleichzeitig liefern
V2: moderne SQL-Windows Funktionen nutzen, um die Zeitdifferenz zu berechnen
V3: SQL Krücken benutzen, um es mit einer alten DB hinzubekommen

 

[Pete0815]

Hi
aktuell läuft mariadB 10.3.25
macht das einen Unterschied?

V1: Wird direkt nicht funktionieren. Könnte höchstens einen Cron laufen lassen, der das in eine neue Spalte einträgt durch nachträgliche Berechnung.
V2: Bin auf nem Raspberry dort unterwegs...
V3: siehe oben? Hilft das?

 

[dabadepdu]

Ich hab keine Ahnung, welche Maria Version bezüglich Window Functions kompatibel zu welcher mysql Version ist. Bei mySQL gibt es ja erst eine, die das ansatzweise kann. Oder?
Ich persönlich halte nichts von der Anwendung beider Systeme, ich schaue lediglich über den Tellerrand, das reicht mir aber schon bei mySQL.

Auf dem Raspberry gibt es auch andere Systeme, Postgres ist empfehlenswert.
Wo 'oben'? Was ist 'das'?

Es gibt einen riesen Haufen workarounds für fehlende mySQL / Maria Funktionen. Du bräuchtest eine für Window Functions.

 

[Pete0815]

Ich hab keine Ahnung, welche Maria Version bezüglich Window Functions kompatibel zu welcher mysql Version ist. Bei mySQL gibt es ja erst eine, die das ansatzweise kann. Oder?

Da fragst Du den Richtigen Bin totaler Einsteiger
Bei MariaDB konnte ich Folgendes finden, aber bzgl. Timestamp oder Differenzen sehe ich da nichts. Hast Du eine Spezielle Funktion im Kopf?
Window Functions

Auf dem Raspberry gibt es auch andere Systeme, Postgres ist empfehlenswert.

die mariadB läuft nur eh durch die Anknüpfung an die Autmatisierungsplattform, deswegen wollte ich sie "Anzapfen". Abschaffen wird nicht funktionieren, Ersetzen is mir zu heikel. Kopie in Postgres anlegen ggf. eine Option aber ist halt Doppeltgemoppelt inkl. Speicherplatz.

Wo 'oben'? Was ist 'das'?

Bezog sich auf die mariadB Version von Oktober 2020 und sollte recht aktuell sein.

Es gibt einen riesen Haufen workarounds für fehlende mySQL / Maria Funktionen. Du bräuchtest eine für Window Functions.

Danke jetzt hab ich schon mal einen Ansatz um besser Suchen zu können.

 

[Pete0815]

P.S.: Ist timestampdiff() ein Befehl der für Dich zu den ungünstigen "Krücken zählt und ich damit eher nicht anfangen sollte?

Habe ihn gerade in der mariadB Tutorial Liste gesehen:
MariaDB Date Functions

 

[akretschmer]

das wird Dir nicht helfen, die Differenz zwischen 2 Records zu ermitteln. Was Du suchst sieht eher so aus:

test=*# create table pete0815(id serial primary key, ts timestamp);
CREATE TABLE
test=*#
test=*#
test=*#
test=*# commit;
COMMIT
test=# insert into pete0815 (ts) select now();
INSERT 0 1
test=*# commit;
COMMIT
test=# insert into pete0815 (ts) select now();
INSERT 0 1
test=*# commit;
COMMIT
test=# insert into pete0815 (ts) select now();
INSERT 0 1
test=*# commit;
COMMIT
test=# select * from pete0815;
 id |             ts             
----+----------------------------
  1 | 2020-11-20 14:38:43.035996
  2 | 2020-11-20 14:38:45.196101
  3 | 2020-11-20 14:38:47.204244
(3 rows)

test=*# select *, ts - lag(ts) over (order by id) from pete0815;
 id |             ts             |    ?column?     
----+----------------------------+-----------------
  1 | 2020-11-20 14:38:43.035996 |
  2 | 2020-11-20 14:38:45.196101 | 00:00:02.160105
  3 | 2020-11-20 14:38:47.204244 | 00:00:02.008143
(3 rows)

test=*#

 

[Pete0815]

Vielen Dank! Glaube damit komme ich weiter. Der nächste Schritt ist eher konzeptionell selbst verstehen für mich.
Erzeuge ich eine neue Spalte in der Datenbank und lasse dort zyklisch Zeitdifferenz für neue Datenpunkte reinschreiben, oder versuche ich das in die Abfrage zu integrieren. Mein Bauchgefühl (Anfänger) sagt mir, dass es mehr Sinn macht die neue Spalte zu erzeugen da wir für z.B. Werte(alle 3 Sekunden) über 28.000 Werte je Tag sprechen.

Dies ist nur der Erste Schritt einer Hilfsrechnung, die eigentliche Rechnung der elektrischen Arbeit hat noch gar nicht angefangen. Somit überlege ich dafür dann auch eine Spalte zu erzeugen. Was dann allerdings sinnvoll ist dort einzutragen frage ich ich mich noch.

a) nur die elektrische Arbeit zwischen den beiden Datenpunkten also das Delta
b)eine fortlaufende Summenbildung
c)einen Tageszähler

a) würde wieder die größte Last für die Datenbank+Rechner bedeuten wenn ich mir die Werte anzeigen lasse
b) sehr nah zu einem praktischen Stromzähler
c) eine Mischung aus A und B

mmh.

 

[dabadepdu]

Da fragst Du den Richtigen Bin totaler Einsteiger
..
Hast Du eine Spezielle Funktion im Kopf?
Window Functions
..
Bezog sich auf die mariadB Version von Oktober 2020 und sollte recht aktuell sein.

Ich muss das wie erläutert genauso nachschlagen wie Du, ich bin bezüglich Maria auch Anfänger. Es hindert dich niemand, google zu benutzen bzw. die Maria Doku. Mich interessiert halt Maria nicht.

Die Windows Funktionen sind ja nun geklärt, es geht dabei nicht um besondere Zeitfunktionen, es ist ein genereller Ansatz, gezielte Bezüge zwischen Datensätzen herzustellen. Hier bildet das dann die Grundlage für die Differenzrechnung.

 

[dabadepdu]

Erzeuge ich eine neue Spalte in der Datenbank und lasse dort zyklisch Zeitdifferenz für neue Datenpunkte reinschreiben, oder versuche ich das in die Abfrage zu integrieren. Mein Bauchgefühl (Anfänger) sagt mir, dass es mehr Sinn macht die neue Spalte zu erzeugen da wir für z.B. Werte(alle 3 Sekunden) über 28.000 Werte je Tag sprechen.
...

Kommt auf den Bedarf an. Was Du weiter schreibst klingt ein wenig so, als ob vielleicht weitere Aufbereitungsschritte folgen. Ist das so, könnte man alles in einem Rutsch machen. "Zyklisch" bedeutet eine häufige, wiederkehrende Last. "Integration in die Abfrage" kann alles bedeuten. Wird die Abfrage beliebig aus einem Dashboard aufgerufen, ist die Last hoch, wird die Abfrage nur einmal im Monat benutzt, wäre es sozusagen minimal Impact und sowieso bei der Online Berechnung weniger Speicherplatzbedarf- was aber vielleicht nicht ins Gewicht fällt.

 

[Pete0815]

Der Geschmack kommt beim Essen, deswegen bin ich vorsichtig. Allgemein habe ich derzeit eine Webpage, die mir für ca. 5 Geräte/Datenpunkte eine Auswertung anzeigen soll. Verbrauch heute, gestern, diese/letzte Woche, diesen/letzten Monat und aktuelles Jahr.

Diese angezeigten Werte könnte ich mit einer Datenbankabfrage verknüpfen. Ist aber denke ich nicht sinnvoll. So "schießen" mit einem Refresh der Webpage 35 Datenbankabfragen los. Wenn ich hier die Zeitdifferenzrechnung und Berechnung der elektr. Energie innerhalb der Abfrage mache dauert das sicher ewig, wenn es überhaupt gut geht.

Wenn ich es zyklisch mache, könnte man die Werte in entsprechende Variablen schreiben lassen, die sich die Webpage holt. Die kleinsten Zyklen werden dabei die aktuellen Verbräuche haben. Gestern, letzte Woche und Monat reicht ja entsprechend oft zu triggern wo sich auch Änderungen wirklkich ergeben.

Allgemein geht es mir halt auch darum, wie man so etwas aufbaut um später eine gute, performante Lösung zu haben. Da ist es glaube ich zweitranging welche Datenbank verwendet wird, wenn das Konzept stimmt (ausgenommen ein Hersteller bietet natürlich die TippiTopie-Funktion dafür).

Hier macht es dann sicher auch einen Unterschied wie ich die Vebrauchswerte in eine neue Spalte schreibe. Nehme ich eine fortlaufende Summenbildung bedeuten die Verbrauchabfragen lediglich eine Differenzrechnung vom z.B. ersten Wert des Tages zum letzten Wert des Tages. Schreibe ich dort aber in die Spalte nur Verbräuche von einem Datenpunkt zum Nächsten, dann muß die Abfrage für den ganzen Tag/Woche/Monat/Jahr die Summe bilden. Das kann doch nur mehr Last erzeugen, oder?

 

[Dukel]

Bei Datenpunkt Daten kannst du dir auch Time Series Datenbanken wie Prometheus oder InfluxDB anschauen. Diese sind darauf spezialisiert.

 

[Pete0815]

Bei Datenpunkt Daten kannst du dir auch Time Series Datenbanken wie Prometheus oder InfluxDB anschauen. Diese sind darauf spezialisiert.

InfluxDB bekomme ich recht flott installiert. Hast Du auch einen Tip bzgl. Auswertung der oben genannten Aufgabenstellung?

 

[dabadepdu]

Der Geschmack kommt beim Essen,
..
Allgemein geht es mir halt auch darum, wie man so etwas aufbaut um später eine gute, performante Lösung zu haben. Da ist es glaube ich zweitrangig welche Datenbank verwendet wird, wenn das Konzept stimmt (ausgenommen ein Hersteller bietet natürlich die TippiTopie-Funktion dafür).
..
Hier macht es dann sicher auch einen Unterschied wie ich die Verbrauchswerte in eine neue Spalte schreibe.

Ja, das stimmt. Kommt aber drauf an, wie es schmeckt.
Zweitrangig ist es mit Sicherheit nicht, welche Datenbank verwendet wird. Eine Aufbereitung für eine Visualisierung ist was anderes, als Abrechnungsdaten zu handhaben, Multiuser Datenbearbeitung zu realisieren, konkurrierende Multiuser Datenbearbeitung, Suchsysteme aufzubauen oder offline Systeme zu betreuen. Mit allen daraus folgenden Anforderungen wie Desaster Recovery, Performance, Ressourcenbedarf, Konfliktlösung, ..
Ein mittelmäßiger Ansatz ist wahrscheinlich auch, von Werkzeugen aus zu planen, die man gerade in die Finger bekommt.

Messwerte haben eine Besonderheit, die nicht unbedingt offensichtlich ist, aber eigentlich eine banale Flanke in diesen Fragen bilden. Sie sind einerseits potentiell sehr viele bzw. stark wachsend und sie sind andererseits an sich statisch, im Sinne von unveränderlich bezogen auf den Einzelwert. Sie werden nicht gelöscht oder bearbeitet. Sie werden- einmal gespeichert- nur noch gelesen.

Was ist also die Herausforderung? Große Massen dieser Werte mundgerecht zu betrachten oder zur Betrachtung anzubieten.
Einerseits ist das einfach, weil es naturgemäß keine Änderungen gibt, andererseits ist es komplex, weil trotz sehr großer Datenmasse vertretbare Antwortzeiten im Zugriff erwartet werden.

Der Readonly Aspekt dieser Daten macht dabei im Prinzip wesentliche Funktionen einer klassischen Datenbank obsolet, die aber mit einem hohen Preis kommen. Konkurrierende (Schreib)Zugriffe, Transaktionssicherheit, .. haben einen ganz anderen Impact und Overhead als reine Lesezugriffe. Natürlich kommt vor dem reinen Lesen einmalig das Schreiben, ein bloßes "Append" wenn man so will. Ein System muss als Minimalanforderung alle eingehenden Daten schreiben können und minimal auch einen geringen, gleichzeitigen Lesezugriff aller Daten verkraften und vermutlich hinreichend ausfallsicher sein.

Danach ist man frei, sehr frei. Dann kommt es darauf an, Verfahren zu finden, in denen man ein Optimum für eine Clusterung, Aggregation und Abfragegeschwindigkeit findet.

Für Systeme wie Influx, Grafana und andere bedeutet das schon eine Festlegung, auf die später dargestellten Größen und die visualisierbare Detailtiefe. Natürlich bedeutet es ebenfalls redundante Datenhaltung, wenn eine Datenbank davor liegt. (Das hast Du weiter oben als Negativpunkt für eine weitere DB angeführt).
Grafana und Co bieten ohne Frage großen Komfort für eine sexy Darstellung von Daten, sie arbeiten aber meist bereits auf Aggregaten. Außerdem sind es zusätzliche Systeme, mit zusätzlichem Verwaltungsaufwand und durchaus einer gewissen Instabilität, also z.B. wenig Rückwärtskompatibilität, ... Und wenn man Wert legt auf Performance, muss man sich bewusst sein darüber, dass größere Systeme wiederum in skalierende Infrastruktur eingebettet sind, die bei vielen Anfragen schlicht neue Maschinen hochfährt.

Man kann daraus verschiedene Lösungsansätze ableiten. Z.B. kann man direkt aus der Anwendung (Messwerterfassung) zu Grafana o.ä. verzweigen und diese Systeme mit Daten beschicken, parallel Rohdaten stumpf in einer DB sammeln. Es gibt viele Varianten davon. Bspw. eine Dokument basierte Datenerfassung, noSQL. Das ohne konkurrierende Zugriffe und komplexe Abhängigkeiten voll seine Stärken ausspielen kann.

Eine andere wäre, "klein" anzufangen, mit einem einzigen System, einem guten RDBMS und zu schauen, wo man im Laufe der Zeit größeren Appetit bekommt.

 

[Pete0815]

@dabadepdu vielen Dank für Deine ausführliche Erläuterung, die ich sich noch einige Male lesen werde und muß um sie zu Verinnerlichen und daraus Schlüße zu ziehen.

Ja wie erwähnt, würde ich mich ärgern, wenn ich dieses Projekt fertig stelle und am Ende merke, dass durch ungeschickte Abfragen/Programmierung die Bedienung träge ist und vielleicht der Raspberry nicht mehr ausreicht, wo er es mit einer besseren Umsetzung tun würde. Deswegen bin ich hauptsächlich bedacht darauf zu achten was ich mache. Eine Skalierung wie Du sie erwähnst, auf zB weitere Hardware/Rechenleistung möchte ich vermeiden, da ich Cloud in dem Bezug nicht mag und für dieses Projekt nicht mehr Hardware aber auch Rechenleistung durch Stromkosten investieren mag.
Dies geprägt durch mein Verständnis für die allgemeine Einordnung des Projektes und die Nutzung. Wir sprechen über Heimautomatisierung für einen 3 Personen Haushalt. Die Zugriffe auf die Webpage sind also überschaubar und die Zugriffe auf die Datenbank lassen sich für mein Verständnis stark durch das Konzept der Umsetzung beeinflussen.

Ganz plump gedacht. Kleine Anwendung, die durch ungeschickte Umsetzung mies werden kann. Meine Einschätzung der Datenbanken (Anfängersicht) bezieht sich dabei immer auf diesen Blickwinkel der "kleinen Anwendung" und daraus gefolgert, dass so etwas doch eigentlich viele DB können sollten. Dass sich dieser Punkt mit der guten Konzeption und Wunsch nach Performance nicht gut verträgt zeigt mir auch Dein Beitrag.

Ich muß mir erste Eindrücke zu InfluxDB noch vertiefen um etwas Gefühl zu bekommen wie ich hier Abfragen umsetzen kann. SQL findet man recht gut im Netz, aber habe die Befürchtung bei InfluxDB recht schnell nicht mehr weiter zu kommen. Die Darstellung auf der Webpage sind derzeit Zahlenwerte, ob Grafana hier hilft muß ich testen.
Ergänzend gibt es zu InfluxdB eine implementierte Verbindung zum bestehenden System und somit ist Backuplösung und Kompatibilität gegeben.

Wie und ob es sinnvoll ist 2 Datenbanken laufen zu lassen, Tendenz derzeit ja. Sollte nur gut überlegen was wo gespeichert werden soll. Andererseits ist der Speicherplatz am Raspberry derzeit hoffnungslos überdimensioniert (256GB) und könnte hier ggf. was gröber aggieren. Eine wirkliche Langzeitspeicherung (länger als 1 Jahr) habe ich auf dem System eh nicht vor.