Erstellung einer SQL-Tabelle

[ChristianSr]

Hallo,
ich habe bei iobroker den Datenlogger via SQL aktiviert, wodurch die Daten in einer Tabelle der folgenden Form landen:
"timestamp","Feld-ID","Wert". Ein neuer Eintrag erfolgt jeweils, wenn sich der Wert ändern (also zu unregelmäßigen Zeitpunkten).
Ich möchte nun daraus eine Tabelle erzeugen, die einen festen Zeitintervall hat (z.B. alle 5min). Drei Felder (z.B. Feld-ID 1,2 & 3) sollen dabei eine Spalte bekommen. Die Werte sollen aus Werten zeitlich davor und danach interpoliert werden.

Hat schonmal jemand so etwas umgesetzt?

Gruß,
Christian

[haus-automatisierung]

@christiansr Also nur eine View und keine neue Tabelle. Oder willst Du ständig die aggregierten Daten da rein kopieren?

[OliverIO]

@christiansr

daraus würde ich 2 aufeinander aufbauende abfragen machen.

1.abfrage aller werte, aggregiert auf 5 minuten
felder wären

• timestamp (immer auf volle 5 minuten gerundet),
• ID
• summe der werte innerhalb der zeitscheibe für den jeweiligen ID

das habe ich mal bei chatgpt eingegeben:

*---------------------------------
User
ich habe in einer datenbank 3 felder: timestamp, id und wert
diese werte möchte ich nun 5 Minuten aggregiert je id ausgegeben haben

ChatGPT
Um Daten in einer Datenbank aggregiert nach Zeitintervallen und IDs auszugeben, können Sie SQL verwenden, um die gewünschten Ergebnisse zu erhalten. Hier ist ein Beispiel für eine Abfrage, die Daten in 5-Minuten-Intervallen aggregiert:

sql
Copy code

SELECT 
  id,
  DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i') AS time_interval,
  AVG(wert) AS average_wert
FROM 
  deine_tabelle
GROUP BY 
  id, 
  UNIX_TIMESTAMP(timestamp) DIV (5 * 60)
ORDER BY 
  id, 
  time_interval;

Ersetzen Sie "deine_tabelle" durch den tatsächlichen Namen Ihrer Tabelle. Diese Abfrage verwendet die DATE_FORMAT-Funktion, um den Timestamp auf das gewünschte Format zu bringen, und die AVG-Funktion, um den Durchschnittswert für jedes 5-Minuten-Intervall zu berechnen.

Diese Abfrage gruppiert die Daten nach ID und Zeitintervall und gibt den Durchschnittswert für jedes dieser Intervalle aus. Beachten Sie, dass Sie möglicherweise Anpassungen an dieser Abfrage vornehmen müssen, abhängig von der genauen Struktur Ihrer Datenbank und den spezifischen Anforderungen.
*---------------------------------
ob das jetzt exakt funktioniert habe ich nicht ausprobiert, aber das sql sieht schon mal gut aus. chatGPT hat jetzt den durchschnittswert genommen, willst du die summe? dann musst du AVG durch SUM ersetzen. unter umständen heißt die funktion je nach Datenbank auch anders. AVG könnte auch MEAN heißen, aber da muss man dann in der doku schauen.

2. Abfrage

dann eine 2. abfrage, die auch als pivot-abfrage bekannt ist. die schwierigkeit hier ist, das die datenbank die felder anhand der id einsammeln muss

*-------------------------------------------
User
kannst du nun aus dem ergebnis der vorherigen abfrage eine pivot abfrage machen, bei der je timestamp, die werte aller ids in einer zeile aufgeführt werden
ChatGPT
Um eine Pivottabelle zu erstellen, in der die Werte aller IDs für jeden Timestamp in einer Zeile aufgeführt werden, können Sie die CASE WHEN-Anweisung verwenden. Hier ist ein Beispiel:

sql
Copy code

SELECT
  time_interval,
  MAX(CASE WHEN id = 1 THEN average_wert END) AS id_1,
  MAX(CASE WHEN id = 2 THEN average_wert END) AS id_2,
  MAX(CASE WHEN id = 3 THEN average_wert END) AS id_3
  -- Füge hier weitere IDs hinzu, wenn nötig
FROM
  (SELECT 
    id,
    DATE_FORMAT(timestamp, '%Y-%m-%d %H:%i') AS time_interval,
    AVG(wert) AS average_wert
  FROM 
    deine_tabelle
  GROUP BY 
    id, 
    UNIX_TIMESTAMP(timestamp) DIV (5 * 60)) AS aggregated_data
GROUP BY
  time_interval
ORDER BY
  time_interval;

In diesem Beispiel wird die innere Abfrage verwendet, um die Durchschnittswerte für jedes 5-Minuten-Intervall zu berechnen. Die äußere Abfrage verwendet dann die CASE WHEN-Anweisung, um die Werte für jede ID in separaten Spalten zu platzieren. Der MAX-Operator wird verwendet, um den Wert für jede ID in der Zeile zu aggregieren.

Passen Sie die Abfrage entsprechend Ihrer Tabellenstruktur und Anforderungen an. Fügen Sie weitere MAX(CASE WHEN ...)-Klauseln hinzu, wenn Sie mehr IDs haben.
*-------------------------------------------
bei from/deine_tabelle muss dann der name der 1.abfrage rein.
wie üblich muss man das im detail ausprobieren und tüfteln.
das beste man nimmt phpmyadmin für eine mariadb/mysql

[ChristianSr]

@haus-automatisierung said in Erstellung einer SQL-Tabelle:

@christiansr Also nur eine View und keine neue Tabelle. Oder willst Du ständig die aggregierten Daten da rein kopieren?

ich werde die idee mit der view probieren, kann mir aber nicht vorstellen, dass das performance-mäßig funktioniert. 12 Datenpunkte pro Stunde machen 100.000 Einträge pro Jahr und Sensor. Da kommen schnell ein paar mio zusammen. Diese on demand zu interpolieren und zu pivotisieren kommt mir rechenintensiv vor.
Ich hatte also eher daran gedacht neben der ersten Logging-Datenbank eine zweite Historien-Datenbank aufzubauen und diese per Job zu füllen. Die Grundidee:
Sobald ein neuer Eintrag in der Logging-DB erzeugt wird für diese Sensor-ID der letzte Eintrag in der Historien-DB ermittelt. Dann werden alle Intervalle interpoliert und entsprechend eingetragen.

Leider habe ich nicht viel mehr als diese Grundidee zu bieten, daher meine sehr pauschale Eingangsfrage.
Zunächst wäre die Frage JS, Blockly oder NodeRed?
CRON-Job oder Trigger durch Objekt-Überwachung?
Wie baue ich das Script robust genug, dass es auch arbeiten kann, wenn es mal einen Trigger nicht mitbekommen hat? Also den vorletzten und den letzten Eintrag verarbeiten muss?

@oliverio said in Erstellung einer SQL-Tabelle:
die Abfrage läuft bei mir stets in einen 30s-timeout. Ich denke ich werde eine script-Lösung finden müssen.

[haus-automatisierung]

@christiansr Wenn man das so liest, klingt das eigentlich genau wie eine Aufgabe für InfluxDB 2 und Tasks zum reduzieren der Daten alle 5min in ein zweites Bucket. Mit entsprechender retention time wird sogar automatisch im ersten Bucket aufgeräumt.

Würde das Rad da also nicht neu erfinden. Keine Crons, kein manuelles löschen, usw. Alles direkt in der Datenbank mit einem einfachen Task.

[ChristianSr]

@haus-automatisierung
Tatsächlich fing ich mein projekt zunächst mit influxdb an, da ich den gleichen Gedanken hatte.
Die Datenbank-Struktur finde ich grundsätzlich gut und mit Flux bin ich gut klar gekommen, allerdings empfinde ich die Arbeitsumgebung mühsam.
Ich habe keinen unkomplizierten Weg gefunden in die Daten zu schauen. Es gibt einfach keinen guten Abfrage-Editor in dem ich mal ein paar Flux-Abfragen speichern könnte und durch die Tabellen browsen (analog MySQL Workbench). Den Data Explorer finde ich nicht gelungen!
Mir ist es nicht gelungen meine Docker-InfluxDB an Python / Anaconda (https://forum.iobroker.net/topic/68107/python-zugriff-auf-influxdb-im-docker/23) oder Power BI (influxdb-Forum) zu anzudocken um mit den Daten ein wenig mehr zu arbeiten.

Außerdem bin ich unsicher wie die Zukunft von InfluxDB aussieht. Ich habe den Eindruck, dass noch recht viel Entwicklung passiert (und passieren muss). Ob das dann jeweils auch für nicht-zahlende Nutzer zur Verfügung steht ist scheinbar noch offen.