SQL Abfrage über 2 Tabellen in Grafana

Heute ist Stammtischzeit:
Jeden Donnerstag 20:30 Uhr hier im Chat.
Wer Lust hat, kann sich gerne beteiligen. ;)
  • Hallo Zusammen,

    ich benötige kurz eure Hilfe. Mein Ziel ist es die Differenz aus Außentemperatur und Innentemperatur zu erhalten. Die 2 Werte liegen in 2 unterschiedlichen Tabellen.

    Außentemperatur liegt in basiswetterdaten

    Innentemperatur liegt in zusatzwetterdaen

    Eingesetzte Datenbank: PostgreSQL

    Die 2 einzelen Query zur Anzeige habe ich in Grafana schon:

    Query A: (Anzeige der Außentemperatur)

    Query B: (Anzeige der Innentemperatur)

    Zeile 2 ist jweils dafür zuständig, dass die Werte aus dem ausgewählten Zeitbereich, welchen man in Grafana auswählen kann, angezeigt werden. set_intervallist eine eigen erstellte Funktion von mir um den zu gruppierenden Zeitraum variable zu halten in abhängigkeit davon wie groß der Zeitbereich gewählt wurde.

    In Query C soll nun die Differenz von Außentemperatur zu Innentemperatur erstellt werden. Darüber lasse ich mich per Telegram Bot benarichtigen wenn die Temperatur außen zu warm wird, dass die Fenster zu schließen sind.

    Da ich mit SQL (noch) auf Kriegsfuß stehe, allen voran mit den joins so hoffe ich dass mir jemand bei Query C helfen kann.

    Nach den Prüfungen im Mai werd ich mir dann doch mal das hier holen

  • Ich bin kein SQL-Experte und es gibt bestimmt einen besseren Weg, aber da Du, wenn ich Dich richtig verstehe, die Durchschnittswerte über bestimmte Intervalle voneinander abziehen willst, fällt mir nur ein Weg über Zwischenspeichern in Hilfstabellen ein. Du müsstest also Deine beiden SELECTs jeweils in ein SELECT INTO umwandeln und könntest dann sowas wie

    SQL
    SELECT aussen.time, (aussen.temperatur-innen.temperatur) AS differenz
    FROM aussen
    INNER JOIN innen ON aussen.time=innen.time;

    versuchen.

  • SQL
    SELECT(t2.sub1 - t1.sub1) AS sub1, (t2.sub2 - t1.sub2) AS sub2
    FROM table t1 CROSS JOIN
         table t2
    WHERE t1.date = '2014-11-08' AND t2.id = '2014-11-07';

    den sub2 part brauchst Du ja nicht, join über ts

    Zur Inspiration ? geklaut bei stackoverflow.com

    Query war: sql select substract two values

    Optimismus ist nur ein Mangel an Informationen?

  • Vielen Dank euch beiden für die Hilfestellungen,

    Mit cross join kam ich etwas weiter wie mit inner join.

    Bin zumindest soweit dass grafana keinen Syntax Error mehr anzeigt.

    Akueller Code:

    Jedoch kann das so noch nicht passen da die Werte nicht übereinstimmen:

    Durchschnittswerte über bestimmte Intervalle voneinander abziehen willst,

    Ja und nein, Datensätze liegen immer alle 5 Minuten vor, Grafana gruppiert diese über einen Zeitbereich, und von diesem Zeitbereich soll er halt den Durchschnitt verwenden, wenn ich das avg() weglasse:

    pq: column "basiswetterdaten.outtemp" must appear in the GROUP BY clause or be used in an aggregate function

    Deswegen eigentlich überhaupt das avg()

  • Hallo,

    den letzten Teil verstehe ich nicht...

    Also: du hast alle 5 min einen Zeitstempel, einen Wert intemp, einen Wert outtemp. Was gruppiert Grafana denn da und über welchen Zeitbereich?

    Wenn du jetzt einen Mittelwert für z.B. die letzte Stunde (=die letzten 12 Werte) bildest. dann soll das was damit passieren? Der Mittelwert kann ja durchaus vom Delta der letzten Messwerte innen und außen abweichen (bzw. es ist sogar ziemlich wahrscheinlich, dass das so ist). Auch, dass die einen Mittelwert "Differenz" nennst, ist nicht gerade hilfreich für die Nachvollziehbarkeit. Das ist nicht das gleiche...

    Gruß, noisefloor

  • Ich bin mir nicht sicher, ob der Einsatz von PostgreSQL in Zusammenarbeit mit Grafana für das, was du da machen willst, sonderlich optimal ist.

    Hintergrund:

    Für die Aufzeichnung und Verwendung mit Grafana hat sich der Einsatz von Zeitreihen-optimierten DBs bewährt (z.B. Influx).

    Die Daten werden in dieser Art von DBs etwas anders gespeichert (eben als Zeitreihen), so dass eine Verarbeitung (Aggregation, Medium, gleitender Durchschnitt usw) "Kinderleicht" ist. Zudem ist die Performance von Influx sehr hoch.

    Die entsprechenden SQL-Kommandos sind in der Grafana-Syntax für den Einsatz mit Influx vorhanden.

    Auch der gleichzeitige Zugriff auf mehrere Datenreihen ist einfach.

    Ich arbeite seit ca. 2 Jahren mit diesem Duo und bin sehr zufrieden - es gibt nur weniges, was nicht geht (und ich habe noch ein Grafana/Influx von 2016 (ohne Aktualisierung))

    Du siehst ja selbst, wie sehr du dich mit einer reinen SQL-DB verbiegen musst.

    Überlege doch, ob du die Daten nicht einfach in eine Influx-DB einspeist und damit die Darstellung realisierst.

  • Also: du hast alle 5 min einen Zeitstempel, einen Wert intemp, einen Wert outtemp. Was gruppiert Grafana denn da und über welchen Zeitbereich?

    Grafana ist in der Lage (mit dem entsprechenden DB-Unterbau - siehe meinen vorhergehenden Post), dynamisch oder fix Daten zu gruppieren und darzustellen.

    Basis ist der anzuzeigende Zeitbereich und die sich daraus ergebenden Darstellungsabstände der Messwerte: Es wird versucht, die Datenmenge dann an den Darstellungsbereich anzupassen (ggf. mit zeitlicher Aggregation) und ggf. weitere Berechnungen (Mittelwerte, max/min usw.) zu berechnen.

    Gut sichtbar ist das z.B. in meiner öffentlich zugänglichen Grafanasicht meines Sensorparks:

    http://bss135.selfhost.eu:58888/dashboard/db/xiaomi-me-sensorpark

    (Etwas Geduld beim Ersten Zugriff: Der Datenupload ist durch meinen limitierten Upload hier zu Hause etwas begrenzt).

    Wenn man aus der Zeitachse heraus zoomt, wird versucht, die Darstellung noch halbwegs vernünftig aussehen zu lassen.

    Randinfo:

    Die Daten werden alle 15min erhoben, die History geht bin ins letzte Jahr hinein, diesen DB`s enthalten ca. 0,9 mio Datensätze

    • Offizieller Beitrag

    Zentris

    jedesmal wenn ich von deinem Sensorpark lese, nehme ich mir vor mir Grafana mal genauer anzuschauen. Und jedes mal vergess ich es -> Notiz gemacht.

    Der Unterschied zwischen Genie und Wahnsinn definiert sich im Erfolg.

  • Mit cross join kam ich etwas weiter wie mit inner join.

    Interessant. Alle Erklärungen, die ich gefunden habe, sagen, daß sich ein CROSS JOIN mit WHERE exakt wie ein INNER JOIN verhält.

    Jedoch kann das so noch nicht passen da die Werte nicht übereinstimmen:

    Das wundert mich wenig. Du filterst nur auf den Zeitstempel in basiswetterdaten und kombinierst so m.E. den/die entsprechenden Wert/e aus basiswetterdaten jeweils mit allen Werten aus zusatzwetterdaten.

    Vielleicht würde es helfen, wenn Du die Funktion $__timeFilter() doch noch mal näher beschreiben würdest (und evtl. auch die komische Berechnung der "Zeit") – oder Du steigst gleich, wie von Zentris vorgeschlagen, auf eine für Zeitreihen optimierte Datenbank um.

  • $__timeFilter() eine Funktion (Makro) welche von Grafana zur Verfügung gestellt wird:

    Code
    - $__timeFilter(column) ->  extract(epoch from column) BETWEEN 1492750877 AND 1492750877

    Das wundert mich wenig.

    Das mag wohl durchaus meinen tollen SQL Fähigkeiten geschuldet sein.

    Ja Zentris Sensorpark ist super aufgebaut, habe auch seine Seite schon zuvor des öfteren besucht. Ebenso warte ich gespannt auf seine Ergebnisse mit den Smart Meter (nach den Prüfungen will ich das auch angehen). dbv Grafana ist im Grunde echt ne tolle Sache, lohnt sich durchaus anzusehen, jedoch hat es mich schon sehr viele nerven gekostet.

    Überlege doch, ob du die Daten nicht einfach in eine Influx-DB einspeist und damit die Darstellung realisierst.

    Um ehrlich zu sein, ist das ganze schon auf InfluxDB gelaufen und es findet gerade ein Wechsel zu PostgreSQL statt.

    Was ich vergessen habe zu erwähnen, für PostgreSQL ist die Erweiterung Timescale installiert.

    Mit ein Grund für den Wechsel ist, dass es in InfluxDB keine Möglichkeit gibt in einem Diagramm Werte aus verschiedenen Tabellen anzuzeigen. (Worum es sich hier bei diesem Thread wiederrum dreht :lol:)

    Meine eigene oben erwähnte Funktion set_intervall() sieht so aus:

    noisefloor, Zentris hat den Sinn denke ich ganz passend in seinem Beitrag beschrieben, für den Grund der Gruppierung:

    Grafana ist in der Lage (mit dem entsprechenden DB-Unterbau - siehe meinen vorhergehenden Post), dynamisch oder fix Daten zu gruppieren und darzustellen.


    Basis ist der anzuzeigende Zeitbereich und die sich daraus ergebenden Darstellungsabstände der Messwerte: Es wird versucht, die Datenmenge dann an den Darstellungsbereich anzupassen (ggf. mit zeitlicher Aggregation) und ggf. weitere Berechnungen (Mittelwerte, max/min usw.) zu berechnen.

    Er hat das definitiv besser erklährt, als was du von mir bekommen hättest :angel:

  • Das mag wohl durchaus meinen tollen SQL Fähigkeiten geschuldet sein.

    Und wenn Du zusätzlich noch ein AND $__timeFilter(zusatzwetterdaten.ts) einbaust?

    Die ganz Zeitstempelarithmetik kommt mir extrem umständlich vor. Ich kann mir nur schwer vorstellen, daß das nicht einfacher geht – da ich Grafana allerdings überhaupt nicht kenne, kann ich leider keinen konstruktiven Vorschlag machen, wie das sein sollte.

  • Mit ein Grund für den Wechsel ist, dass es in InfluxDB keine Möglichkeit gibt in einem Diagramm Werte aus verschiedenen Tabellen anzuzeigen. (Worum es sich hier bei diesem Thread wiederrum dreht :lol:)

    Nein, das stimmt so nicht:

    Man kann in den Panels Daten aus verschiedenen Tabellen gleichzeitig darstellen:

    Im Bild zu sehen (nur zur Demonstration) Daten aus der "Powermeter" - DB und gleichzeitig unten aus meinem "Sensorpark".

    Die Darstellung ist noch nicht geschliffen (also schön gemacht), aber die Darstellung erfolgt im gleichen Panel:

    Man kann das auch explicite einstellen:

    "-- Mixed --" lässt einem sehr viel Freiheiten

  • Sorry da hab ich mich jetzt ungeschickt ausgedrückt. Zentris

    Was ich meinte war, ich kann nicht die Differenz von 2 Werten bilden, die in unterschiedlichen Tabellen liegen.

    Mein Fehler ^^

    Und wenn Du zusätzlich noch ein AND $__timeFilter(zusatzwetterdaten.ts) einbaust?

    Ok, danke werd ich probieren

  • So da ich der Schuldige an dem ganzen Thema bin (Hofei ist auf meinem Server ansässig), kurz mal ein Kommentar zu dem ganzen von mir nachgeschoben. Da ich die Diskussion (Influx vs. Postgres mit Timescale) bereits vielfach geführt habe und deshalb hier auch noch ein paar Infos für alle Skeptiker ;)

    Die entsprechenden SQL-Kommandos sind in der Grafana-Syntax für den Einsatz mit Influx vorhanden.

    Auch der gleichzeitige Zugriff auf mehrere Datenreihen ist einfach.

    Ich arbeite seit ca. 2 Jahren mit diesem Duo und bin sehr zufrieden - es gibt nur weniges, was nicht geht (und ich habe noch ein Grafana/Influx von 2016 (ohne Aktualisierung))

    Influx ist schön und gut für die Datenspeicherung und einfache Abfrage der Daten als Zeitreihen. Auch die Zusammenarbeit mit Grafana ist bei Influx wirklich gut. Aber sobald es an die Auswertung / Analyse der Daten geht ist Influx einfach vollkommener Müll.

    Ich beschäftige mich sehr viel mit Metereologischen (Regen, Grundwasserstände, Temperatur, ...) und Verbrauchs-Daten (Trinkwasser). Influx mag wunderbar funktionieren, wenn ich wissen will wie im Zeitbereich von t1 bis t2 der Verlauf bezogen auf ein Intervall x ist. Aber sobald komplexere Fragestellungen auftauchen Scheitert Influx (z.B. Differenzwerte aus zwei Tabellen). Innerhalb einer Tabelle würde es funktionieren, aber dies würde dazu führen, dass man einfach alle Daten in eine Tabelle füttern müsste?! Vielleicht wie hier im Beispiel bei Innen- und Außentemperatur noch sinnvoll, bei vielem anderen aber nicht (z.B. Wasserverbrauch und Außentemperatur).

    Des weiteren sind Fragestellungen wie: Gib mir die statistischen Größen für den typischen Trinkwasserverbrauch über den Wochengang aus (für jeden Tag den Mittleren und Gesamten Verbrauch, die Standardabweichung, ...). Hier ist bei InfluxDB immer ein Postprocessing notwendig und dadurch sind die Werte in Grafana nicht darstellbar bzw. nutzbar. Für mich wäre z.B. eine Alarmierung über Grafana bei einer Abweichung von über 15% vom durchschnittlichen typischen Tagesverbrauch des Trinkwassers interessant. Das ganze ist in Abhängigkeit von Temperatur, Tag (Wochentag / Feiertag), Monat, ... Über Timescale (Postgres) ist man in der Lage die Fragestellung mit einer Abfrage zu lösen, mit Influx geht das ganze ohne Postprocessing nicht bzw. müsste man für die Darstellung die temporären Daten (des Trinkwasserverbrauchmodells in Abhänigkeit von Tag, Monat, Temperatur, ...) erst in die Datenbank speichern um die Daten dann wieder auswerten zu können.

    $__timeFilter() eine Funktion (Makro) welche von Grafana zur Verfügung gestellt wird:

    - $__timeFilter(column) -> extract(epoch from column) BETWEEN 1492750877 AND 1492750877

    Wie bereits erwähnt ist diese Funktion zwar implementiert in Grafana allerdings sehr ineffizient.

    vgl. https://github.com/grafana/grafana/issues/11578

    Die ganz Zeitstempelarithmetik kommt mir extrem umständlich vor. Ich kann mir nur schwer vorstellen, daß das nicht einfacher geht – da ich Grafana allerdings überhaupt nicht kenne, kann ich leider keinen konstruktiven Vorschlag machen, wie das sein sollte.

    Es gibt Gründe für die ganze Kompexität der Zeitstempelarithmetik: Da man auf der Web-Oberfläche beliebige Zeitbereiche auswählen und analysieren kann, muss man hier etwas steuernd eingreifen. Die Funktion set_intervall benötigt man aus dem Grund, da der Zeitbereich beliebig gewählt werden kann. Fragt man nun z.B. 24 Stunden ab erhält man bei 15 Minuten (das Messintervall) 96 Datensätze zur anzeige, soweit kein Problem. Sieht man sich allerdings die Werte für ein Jahr an erhält man schon 35 040 Datensätze und so weiter. Aus diesem Grund werden die Daten über ein sinnvoll kleines Intervall gruppiert, so dass die Darstellung nicht darunter leidet, aber die Datenmenge möglichst gering ist.

    Die Funktion haben wir (mal schnell zum testen - daher nur manuelle Werte über IF) von der Grafana-Schnittstelle für InfluxDB agekupfert, da es hier (intern) genau so gemacht wird. So ich muss jetzt arbeiten - ich werde mir das Problem mal am Abend genauer ansehen und mich nochmal melden ;)

  • Danke für die Erklärung! Der Sinn von set_intervall (BTW: warum mit Doppel-ell?) war mir schon klar, mir kommt nur die ganze Berechnung wie gesagt sehr umständlich (und unübersichtlich) vor. Sehe ich es richtig, daß Ihr mit der Division durch 2000 die Intervalle in Doppelsekunden berechnet? Gibt's da einen bestimmten Grund für?

    Liegen denn die Messwerte für innen und aussen mit den exakt gleichen Zeitstempeln vor? Falls nicht würdet Ihr beim INNER JOIN über die Differenzen aller Innentemperaturen im Intervall zu jeweils allen Aussentemperaturen mitteln. Sollte aufs gleiche rauskommen, scheint mir aber ebenfalls umständlich.

    Bei Interesse versuche ich mich gerne mal an einer Variante. Meine SQL-Kenntnisse sind allerdings, wie gesagt, eher bescheiden.

  • Sehe ich es richtig, daß Ihr mit der Division durch 2000 die Intervalle in Doppelsekunden berechnet? Gibt's da einen bestimmten Grund für?

    1920 Pixel hat ein Monitor - also haben wir rund 2000 Datenpunkte als Grenze genommen, die mindestens angezeigt werden sollen.

    Funktion in Worten:

    Wenn 5 min größer als Anzeigezeitbereich geteilt durch 2000 dann setze 5min als Gruppierungseinheit. Damit erreichen wir dass immer mindestens 2000 Gruppen im Zeitbereich liegen und damit haben wir keinen (sichtbaren) Informationsverlust.

    Liegen denn die Messwerte für innen und aussen mit den exakt gleichen Zeitstempeln vor? Falls nicht würdet Ihr beim INNER JOIN über die Differenzen aller Innentemperaturen im Intervall zu jeweils allen Aussentemperaturen mitteln. Sollte aufs gleiche rauskommen, scheint mir aber ebenfalls umständlich.

    Ich habe gerade nachgesehen, die Datensätze haben exakt den selben Timestamp.

    Falls nicht würdet Ihr beim INNER JOIN über die Differenzen aller Innentemperaturen im Intervall zu jeweils allen Aussentemperaturen mitteln. Sollte aufs gleiche rauskommen, scheint mir aber ebenfalls umständlich.

    Ich vermute mal, dass für den INNER JOIN der ON Teil vergessen wurde, hab es gerade getestet und funktioniert einwandfrei. Auch die Perfomance ist verständlicherweise viel besser bei INNER JOIN als bei CROSS JOIN. Es gibt ja defeinitiv nur zwei Pärchen. Eventuell wäre noch ein FULL OUTER JOIN eine Idee, falls mal ein Wert in einer der beiden Tabellen fehlt.

    Ich kann jetzt alleine auch den Fehler nicht ganz nachvollziehen, bei meinem Test gerade in Grafana stimmte alles zusammen...

    Hofei hat gleich Feierabend dann telefonieren wir, Infos folgen.

    Edit-Hinweis: besser ist avg(intemp) - avg(outtemp) da bei ungleichen Datenpunktanzahl sonst das Ergebnis abweicht bzw es sogar zu einem Fehler kommen könnnte....

    2 Mal editiert, zuletzt von Pertl (24. April 2018 um 12:35) aus folgendem Grund: Korrektur am Query,

  • WHERE $__timeFilter(basiswetterdaten.ts) and basiswetterdaten.stationsname = 'Hofei' and (wertname = 'intemp' and zusatzwetterdaten.stationsname = 'Hofei')

    Nach meinem Verständnis musst Du auch noch auf $__timeFilter(zusatzwetterdaten.ts) filtern, sonst kriegst Du den Durchschnitt aller Innentemperaturen.

Jetzt mitmachen!

Du hast noch kein Benutzerkonto auf unserer Seite? Registriere dich kostenlos und nimm an unserer Community teil!