Dies ist eine alte Version des Dokuments!
Inhaltsverzeichnis
2.4.4 Abfrage von Daten aus der Datenbank
Mit Hilfe von MySQL-Tools, wie z.B. MySQL Workbench, können Daten direkt aus der Datenbank abgefragt werden. Das kann hilfreich sein, um zu überprüfen, ob die Daten korrekt in die Datenbank geschrieben werden oder ob für einen bestimmten Datenpunkt überhaupt Daten in der Datenbank ankommen.
Hinweis
Die direkte Abfrage von Daten aus der Datenbank ist nur für Administratoren an der Hochschule relevant. Für den normalen Anwender ist die Energiemanagementsoftware völlig ausreichend.
Die Abfrage lässt sich entsprechend der individuellen Bedürfnisse aufbauen.
Tipp
KI-Tools wie ChatGPT können bei der Erstellung von gezielten Datenbankabfragen eine gute Hilfe sein.
3.4.1 Beispiel-Abfrage
Ein vollständiges Beispiel für eine Datenabfrage auf Basis der Kennzeichnung ist im folgenden dargestellt. Sie kann kopiert und an die jeweiligen Bedürfnisse angepasst werden.
SELECT format.*, data.*, ident.*, zone.*, meter.*, from_unixtime(data.timestamp) as datetime
FROM data
LEFT JOIN format ON data.identid = format.identid
LEFT JOIN ident ON format.id = ident.id
LEFT JOIN zone ON ident.zoneid = zone.id
LEFT JOIN meter ON ident.meterid = meter.id
WHERE data.timestamp BETWEEN UNIX_TIMESTAMP('2023-04-18 00:00:00') AND UNIX_TIMESTAMP('2023-04-20 23:59:59')
/*Option 1: Suche anhand der Kennzeichnung*/
And ident.ident = 'HBC-CST-D4-169=420008+D4-172-47P2/MW-Qd-HZK-_-HW-D1_2-M-_'
/*Option 2: Suche definierter Spalteninhalte*/
/*Die Abfragbedingungen Option 2 sind auskommentiert und werden im Beispiel nicht angewendet.
Entfernen Sie die Kommentarzeichen, damit die Abfragebedingungen in der Abfrage angewendet werden.
Vergessen Sie nicht, die Abfragebedingung Option 1 dann auszukommentieren!*/
/*
AND format.component_building_designation = 'B'
AND format.component_room_designation = '112'
AND format.data_point = 'E'
AND format.additional_field_01 = 'ASV'
*/
ORDER BY data.timestamp DESC
LIMIT 1000
Mit der Abfrage in diesem Beispiel werden alle Datensätze des Datenpunkts mit der Kennzeichnung HBC-CST-D4-169=420008+D4-172-47P2/MW-Qd-HZK-_-HW-D1_2-M-_ im Zeitraum von 18.04.2023 00:00:00 Uhr bis 20.04.2023 23:59:59 Uhr ausgegeben.
3.4.2 Erläuterung der Abfrage-Bestandteile
Die einzelnen Bestandteile der Abfrage werden im Anschluss erläutert und individuelle Anpassungsmöglichkeiten aufgezeigt.
Auswahl der Tabellen und Zeitkonvertierung
SELECT format.*, data.*, ident.*, zone.*, meter.*, from_unixtime(data.timestamp) as datetime
SELECT format.*, data.*, ident.*, zone.*, meter.* dient zur Angabe, welche Spalten bei der der Datenbankabfrage ausgegeben werden sollen. Das Sternchen (*) steht dafür, dass alle Spalten der Tabellen data, format, ident, zone und meter zurückgegeben werden.
In der Datenbank wird die Unixzeit verwendet, da diese eine besonders hohe Abfragegeschwindigkeit erlaubt. Mit der Anweisung from_unixtime(data.timestamp) as datetime wird die Unixtime bei der Abfrage von Daten in einen konventionellen Zeitstempel ('yyyy-mm-dd hh:mm:ss') konvertiert.
Auswahl der Basistabelle
FROM data
Gibt die Basistabelle an, die als Ausgangspunkt der Abfrage genutzt wird. In der Tabelle data sind die eigentlichen Messdaten gespeichert.
Verknüpfung der Tabellen
LEFT JOIN format ON data.identid = format.identid LEFT JOIN ident ON format.id = ident.id LEFT JOIN zone ON ident.zoneid = zone.id LEFT JOIN meter ON ident.meterid = meter.id
Verknüpft die Tabelle data nacheinander mit den Tabellen format, ident, zone und meter über jeweils gemeinsame Schlüssel (id) per Left Join, sodass alle relevanten Informationen auch dann aus den verknüpften Tabellen übernommen werden, wenn nicht für alle Datensätze passende Einträge existieren.
Einschränkung des Zeitbereichs
WHERE data.timestamp BETWEEN UNIX_TIMESTAMP('2024-01-01 00:00:00') AND UNIX_TIMESTAMP('2024-01-01 23:59:59')
Es werden nur die Datensätze zurückgegeben, deren timestamp (in Unix-Zeitstempel) innerhalb des angegebenen Datums- und Zeitbereichs liegt. Passen Sie die Zeitstempel entsprechend Ihrer Bedürfnisse an.
Spezifikation der Suchkriterien
Zur Spezifikation der Suche stehen zwei Möglichkeiten zur Verfügung. Es kann entweder die eine oder die andere Variante genutzt werden.
Option 1: Suche anhand der Kennzeichnung
And ident.ident = 'HBC-CST-D4-169=420008+D4-172-47P2/MW-Qd-HZK-_-HW-D1_2-M-_'
Diese Bedingung filtert die Ergebnisse weiter. Es werden nur Datensätze zurückgegeben, deren ident-Kennzeichnung in der Tabelle ident dem angegebenen Kennzeichnungsschlüssel entspricht. Geben Sie hier Ihre individuelle Kennzeichnung an.
Option 2: Suche anhand definierter Spalteninhalte
AND format.component_building_designation = 'B' AND format.component_room_designation = '112' AND format.data_point = 'E' AND format.additional_field_01 = 'ASV'
Diese Bedingungen filtern die Ergebnisse weiter nach spezifischen Werten in den entsprechenden Spalten der Tabelle format. Die Suchfilter können Sie nach Bedarf anpasssen und z.B. zusätzliche Spalten als Ausgabebedingung hinzufügen. Beachten Sie hierzu unbedingt die Struktur der Tabelle format (siehe 2.4.3 Aufbau der Datenbankstruktur)
Ausgabe und Limit
ORDER BY data.timestamp DESC LIMIT 1000
ORDER BY data.timestamp DESC sortiert die Ergebnisse in absteigender Reihenfolge nach dem Zeitstempel (data.timestamp), wobei die neuesten Datensätze zuerst erscheinen.
LIMIT 1000 begrenzt die Anzahl der zurückgegebenen Datensätze auf maximal 1000. Dies ist nützlich, um große Datenmengen zu kontrollieren und die Abfrage effizient zu halten.
Löschen von Daten in der Datenbank
SELECT * FROM data WHERE identid = 119 AND value < 0 ORDER BY id DESC
Diese Anweisung löscht hier sämtliche Werte des Datenpunktes mit der identid=119 aus dem Modul „Synchronize to MySQL“ und Werten kleiner als 0 in der SQL Datenbank endgültig. Im EDL- Protal muss aber der Sensor nochmal manuell gelöscht werden.
