===== 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 [[https://chatgpt.com/|ChatGPT]] können bei der Erstellung von gezielten Datenbankabfragen eine gute Hilfe sein. ==== 2.4.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. ==== 2.4.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 **[[https://de.wikipedia.org/wiki/Unixzeit|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 [[smart_energy_box:datenbank_struktur]]) === 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. \\ **[[smart_energy_box:datenbank_struktur|<< Zurück]]** **[[smart_energy_box:betrieb|Weiter >>]]** **[[smart_energy_box:start|Inhaltsverzeichnis]]**