Schätzung der Famos-Zähler
6.1 MySQL
6.1.1 Datenbanksprache SQL
In relationalen Datenbanksystemen wird je nach Datenbankmanagementsystem ein be-stimmter Dialekt der Datenbanksprache SQL verwendet. Für eine MySQL-Datenbank kann diese z. B. im "MySQL 5.6 Command Line Client" ausgeführt werden. Im Folgenden sollen einige einfache und für die vorliegende Arbeit relevante Abfragen vorgestellt wer-den, für einen umfangreichere Anleitung sei hier auf eines der unzähligen Online-Tutorials verwiesen.
Bei den SQL-Befehlen wird nicht zwischen Groß- und Kleinschreibung unterschieden, zur einfacheren Unterscheidung zwischen Befehl und Namen von Datenbanken, Tabellen und Spalten werden hier alle Befehle in Großbuchstaben geschrieben. Jede Anweisung muss mit einem ";" abgeschlossen werden.
Alle vorhandenen Datenbanken können mit folgender Anweisung angezeigt werden:
SHOW DATABASES;
Auswählen der Datenbank enmolmu in der sich die Tabellen befinden, aus denen eine Abfrage gemacht werden soll:
USE enmolmu;
Anzeigen aller Tabellen, welche sich in der ausgewählten Datenbank befinden:
SHOW TABLES;
Eine neue Datenbank mit dem Namendatenbank1kann mit CREATE DATABASE datenbank1;
angelegt werden, und eine neue Tabelle mit
CREATE TABLE tabelle1 (spalte1 INT, spalte2 VARCHAR(255)) DATA DIRECTORY = 'D:/Tabellenordner';
wobei tabelle1 der Tabellenname, spalte1 undspalte2 die Spaltennamen, INT und VAR-CHAR(255) deren Datentypen und'D:/Tabellenordner'der Ordner, in dem die Daten der Tabelle gespeichert werden, sind. DATA DIRECTORY muss nicht angegeben werden, wenn der voreingestellte Ordner verwendet werden soll.
Wieder gelöscht werden können Tabellen mit DROP TABLE tabelle1, tabelle2;
und Datenbanken mit
DROP DATABASE datenbank1;
Wir gehen im Folgenden von einer Tabelle mit dem Namenoet67aus, welche die Spalten da-tummit dem kompletten Datum und die Spaltenjahr, monat, tag, stundeundminuteals Inte-ger beinhaltet. Jede Zeile der Tabelle entspricht einer Minute.
Ein Minimalbeispiel für eine Abfrage ist SELECT * FROM oet67 limit 6;
wobei nach SELECT die gewünschten Spalten ausgewählt werden und nach FROM die Tabelle, in der sich die Daten befinden. Mit "*" nach SELECT werden alle Spalten der Tabelle ausgewählt. Dieses Beispiel zeigt also die komplette Tabelleoet67an. Fügt man am Schluss der Abfrage LIMIT 6 an, so werden nur die ersten 6 Zeilen der abgefragten Tabelle angezeigt.
Um nur einen Teil der Tabelle anzuzeigen, können nach SELECT die Spalten und nach WHERE die Zeilen in Form von Bedingungen ausgewählt werden
SELECT jahr, monat, tag FROM oet67 WHERE jahr = 2015 and monat >= 11;
Hier werden also von den Spaltenjahr, monatundtagder Tabelleoet67die Zeilen ausge-wählt, bei denen in der Spaltejahr2015 und in der Spaltemonatein Wert größergleich 11 steht, also November und Dezember.
Das folgende Beispiel zeigt eine Abfrage, die die meisten grundlegenden Bausteine enthält (von denen zwar nicht alle vorhanden sein müssen, aber die vorhandenen in dieser Reihenfolge stehen müssen).
SELECT COUNT(*) AS anzahl, MAX(datum) FROM oet67 WHERE jahr = 2014 GROUP BY monat HAVING count(*) <> 4460;
Es wird wieder die Tabelle oet67 verwendet und daraus alle Zeilen (welche in diesem Zusammenhang als "Datensätze" bezeichnet werden) ausgewählt, in denen in der Spal-te jahr 2014 sSpal-teht. Diese Zeilen werden dann durch GROUP BY monatsweise zusam-mengefasst, d.h. für jeden Monat gibt es nur eine Zeile im Ergebnis. da hierfür die ca.
30×24×60 = 4.320 Zeilen (Anzahl der Minuten des jeweiligen Monats) zu einer zu-sammengefasst werden müssen, müssen nach dem SELECT-Statement Funktionen von Spalten stehen, die die Art der Zusammenfassung angeben. Z. B. COUNT(*) zählt die Zeilen des jeweiligen Monates, MAX(datum) gibt den Maximalwert des Datums im je-weiligen Monat an und auf AS folgt der gewünschte Name für die errechnete Spalte. Aus diesen zusammengefassten Zeilen kann dann wiederum eine Teilmenge nach bestimmten Kriterien über HAVING ausgewählt werden. Im obigen Beispiel werden also nur die Monate angezeigt, deren Gesamtanzahl an Zeilen (also Anzahl der Minuten) ungleich 4.460 ist.
Für die Bedingungen nach WHERE oder HAVING gilt:
Operator Bedeutung Beispiel
= gleich tag = 1
< kleiner tag < 10
> größer tag > 10
<= kleinergleich tag <= 15
>= großergleich jahr >= 2014
BETWEEN zwischen zwei Werten monat BETWEEN 3 AND 7 AND Verküpfung mehrerer
Bedingun-gen durch und zugleich
jahr = 2015 AND monat = 1 OR Verknüpfung mehrere
Bedingun-gen durch oder
jahr = 2015 OR monat <> 5
Tabelle 6.1:SQL Verknüpfungen von Bedinungen
Einige Funktionen zum Zusammenfassen von Zeilen:
Funktion Bedeutung Beispiel AVG Durchschnitt AVG(preis)
MAX Maximum MAX(datum)
MIN Minimum MIN(datum)
SUM Summe SUM(preis)
COUNT Anzahl COUNT(*)
Tabelle 6.2:SQL Funktionen zur Datenzusammenfassung
Befinden sich die Spalten in verschiedenen Tabellen, so müssen diese über JOIN verknüpft werden.
SELECT oet67.x7070_01_u138hzg03tem0003mp01, theresienwetter.aussentemperatur FROM oet67 JOIN theresienwetter USING(datum)
WHERE oet67.jahr = 2015 and oet67.monat = 1 and oet67.tag = 3 and oet67.stunde = 15;
Hier werden in SELECT der Tabellenname, in dem sich die jeweilige Spalte befindet und der Spaltenname durch einen Punkt getrennt angegeben. Falls es eine Spalte mit diesem Namen nur in einer Tabelle gibt, ist es nicht nötig, den Tabellennamen vor den Spaltennamen zu schreiben. Die Tabellen, aus denen Spalten, die verwendet werden sollen stammen, werden wieder nach FROM angegeben und mit JOIN verknüpft. Über USING wird festgelegt, anhand welcher Spalte (die in beiden Tabellen vorhanden sein muss) die Tabellen verknüpft werden sollen (d.h. welche Zeile der einen Spalte zu welcher Zeile der anderen gehört).
Über die WHERE-Klausel wird die Stunde 15 des Tages 3 vom Monat 1 des Jahres 2015 ausgewählt, also insgesamt 1 Stunde, was 60 Zeilen entspricht (alle Minuten zwischen 15.00 Uhr und 15.59 des 3.1.2015). Dies geschieht anhand von Spalten, welche aus der Tabelle oet67 kommen.
Statt USING(datum) kann ON(oet67.datum = theresienwetter.datum) verwendet werden, was als einzigen Unterschied zur Folge hat, dass für die Ergebnistabelle beide Datumss-palten zur Verfügung stehen (die aber ohnehin gleich sein müssen). Notwendig ist ON
jedoch, falls die Datumsspalten in den beiden Tabellen unterschiedliche Namen haben, z. B. ON(oet67.date = theresienwetter.datum).
Es gibt unterschiedliche Varianten, wie die beiden Tabellen verknüpft werden können.
JOIN, CROSS JOIN und INNER JOIN sind bei MySQL äquivalent[9] und sorgen dafür, dass nur die Zeilen im Ergebnis vorkommen, für die es in beiden Tabellen eine Entsprechung gibt. LEFT JOIN erzeugt eine Tabelle, in der alle Zeilen, welche in der ersten Tabelle vorhanden sind vorkommen und nur die aus der zweiten, für die es eine entsprechende Zeile in der ersten gibt. RIGHT JOIN verwendet alle Zeilen der zweiten Tabelle. Ein Beispiel mit zwei übersichtlichen Minitabellen kann unter A.2.2 gefunden werden. Für Details zum Thema JOIN-Operationen siehehttps://dev.mysql.com/doc/refman/5.6/
en/join.html
Falls aggregierte Daten aus den Tabellen benötigt werden, so verkürzt sich die Abfrage-dauer deutlich, wenn die beiden Tabellen, wie im folgenden Beispiel, bereits vor dem JOIN aggregiert werden.
SELECT *
FROM (SELECT AVG(x7070_01_u138hzg03tem0003mp01), MAX(datum) AS datum FROM oet67
GROUP BY jahr, monat) X JOIN
(SELECT AVG(aussentemperatur), MAX(datum) AS datum FROM theresienwetter GROUP BY jahr, monat) Y
USING(datum);
In diesem Beispiel werden erst die einzelnen Tabellen nach den Spaltenjahrundmonat gruppiert, wobei aus den einzelnen Monaten immer das jüngste Datum und die Durch-schnittstemperatur ausgewählt wird. Das neu errechnete jüngste Datum bekommt als Spaltennamen jeweils wiederdatum, womit die beiden Tabellen verbunden werden.Xund Ysind Aliase, also Namen für die aggregierten Tabellen. Diese werden zwar in diesem
Bei-spiel nicht verwendet, müssen aber immer festgelegt werden.
Sollen die abgefragten Daten verändert ausgegeben werden, zum Beispiel, um bestimmte Werte oder Bereiche auf NULL zu setzen, falls diese als falsch betrachtet werden, kann IF verwendet werden:
SELECT minute, IF(minute between 1 and 3, NULL, minute) AS minute_neu FROM the46 LIMIT 6;
Es werden zwei Spalten ausgegeben: die ersten 6 Werte der ursprünglichen Spalteminute und die veränderte Spalteminute, bei der alle Werte zwischen 0 (ausgeschlossen) und 3 (eingeschlossen) auf NULL gesetzt wurden. Die Anweisung IF entspricht ungefähr der ifelse-Anweisung in R. Das erste Argument beinhaltet die Bedingung, das zweite, was zurückgegeben werden soll, falls diese erfüllt ist und das dritte die Rückgabe für den Fall, dass sie nicht erfüllt ist. Findet die Abfrage aus R übersqlQuery{RODBC} statt, wird NULL in NA umgewandelt.
Für Zählervariablen werden den Tabellen von EnMoLMU zusätzliche Spalten mit den Differenzen, also den Verbräuchen hinzugefügt. Diese Differenzen können aber auch bei der Abfrage der Daten aus der Datenbank berechnet werden:
SET @diff = NULL;
SET @prevval = -1;
SELECT datum,
(@diff:=IF(@prevval=-1, NULL, x1120_01_021anhv01elz0001zw30 - @prevval)) difference,
(@prevval:=x1120_01_021anhv01elz0001zw30) prevval, diff_x1120_01_021anhv01elz0001zw30
FROM glt_hist_the46
WHERE datum BETWEEN '2015-02-06 14:00:00' AND '2015-02-06 14:10:00';
+---+---+---+---+
| datum | difference | prevval | diff_x1120_01_021anhv01elz0001zw30 | +---+---+---+---+
| 2015-02-06 14:00:00 | NULL | 1574805 | 2 |
| 2015-02-06 14:01:00 | 0 | 1574805 | 0 |
| 2015-02-06 14:02:00 | 2 | 1574807 | 2 |
| 2015-02-06 14:03:00 | 2 | 1574809 | 2 |
| 2015-02-06 14:04:00 | 0 | 1574809 | 0 |
| 2015-02-06 14:05:00 | 2 | 1574811 | 2 |
| 2015-02-06 14:06:00 | 2 | 1574813 | 2 |
| 2015-02-06 14:07:00 | 2 | 1574815 | 2 |
| 2015-02-06 14:08:00 | 0 | 1574815 | 0 |
| 2015-02-06 14:09:00 | 2 | 1574817 | 2 |
| 2015-02-06 14:10:00 | 2 | 1574819 | 2 |
+---+---+---+---+
11 rows in set (0.00 sec)
Wie an der Ausgabe zu erkennen ist, ist die dabei erzeugte Spaltedifferenceidentisch mit der durch EnMoLMU berechnetendiff-Spalte, außer dass der erste Wert fehlt. Es muss somit eine Zeile mehr abgefragt werden.
Falls nicht sichergestellt ist, dass die Tabelle nach Datum geordnet ist, muss erst eine sortierte Version der Tabelle erstellt werden. Es wird nur der Zeitabschnitt der Tabelle sortiert und weiterverwendet, welcher auch benötigt wird, was die Abfragedauer deutlich verkürzen kann:
SET @diff = NULL;
SET @prevval = -1;
SELECT datum,
(@diff:=IF(@prevval=-1, NULL, x1120_01_021anhv01elz0001zw30 - @prevval)) difference,
(@prevval:=x1120_01_021anhv01elz0001zw30) prevval, diff_x1120_01_021anhv01elz0001zw30
FROM (SELECT * FROM glt_hist_the46
WHERE datum BETWEEN '2015-02-06 14:00:00' AND '2015-02-06 14:10:00' ORDER BY datum) the46sortiert;
Allerdings ist es für beide Varianten notwendig, dass der vorherige Wert auch tatsäch-lich vorhanden ist. D.h. es wird nicht überprüft, ob einzelne Minuten fehlen. Dies kann mit einem Join der Tabelle mit sich selber um eine Zeiteinheit verschoben bewerkstel-ligt werden. Dabei verlangsamt sich allerdings die Ausführungsgeschwindigkeit deut-lich:
SELECT x.datum, x.x1120_01_021anhv01elz0001zw30 AS alt, y.x1120_01_021anhv01elz0001zw30 AS neu,
y.x1120_01_021anhv01elz0001zw30 - x.x1120_01_021anhv01elz0001zw30 AS differenz, y.diff_x1120_01_021anhv01elz0001zw30 AS diff_variable
FROM glt_hist_the46 x
LEFT OUTER JOIN glt_hist_the46 y
ON x.datum = date_add(y.datum, INTERVAL -1 MINUTE)
WHERE x.datum BETWEEN '2015-01-01 14:00:00' AND '2015-01-01 14:10:00' ;
+---+---+---+---+---+
| datum | alt | neu | differenz | diff_variable |
+---+---+---+---+---+
| 2015-01-01 14:00:00 | 1521710 | 1521710 | 0 | 0 |
| 2015-01-01 14:01:00 | 1521710 | 1521711 | 1 | 1 |
| 2015-01-01 14:02:00 | 1521711 | 1521712 | 1 | 1 |
| 2015-01-01 14:03:00 | 1521712 | 1521713 | 1 | 1 |
| 2015-01-01 14:04:00 | 1521713 | 1521714 | 1 | 1 |
| 2015-01-01 14:05:00 | 1521714 | 1521714 | 0 | 0 |
| 2015-01-01 14:06:00 | 1521714 | 1521715 | 1 | 1 |
| 2015-01-01 14:07:00 | 1521715 | 1521716 | 1 | 1 |
| 2015-01-01 14:08:00 | 1521716 | 1521717 | 1 | 1 |
| 2015-01-01 14:09:00 | 1521717 | 1521718 | 1 | 1 |
| 2015-01-01 14:10:00 | 1521718 | 1521718 | 0 | 0 | +---+---+---+---+---+
11 rows in set (6.52 sec)
Es ist auch möglich, einfache Berechnungen im "MySQL 5.6 Command Line Client" durch-zuführen. Folgendes Beispiel berechnet die Anzahl der Minuten eines Tages:
SELECT 24*60;
Aktuell laufende Prozesse können mit
show processlist;
+---+---+---+---+---+---+---+---+
| Id | User | Host | db | Command | Time | State | Info |
+---+---+---+---+---+---+---+---+
| 385 | prog | localhost | NULL | Query | 0 | NULL | show processlist |
| 957 | prog | localhost | test | Sleep | 21619 | | NULL | +---+---+---+---+---+---+---+---+
aufgelistet und mit kill Prozessnummer (Id) beendet werden:
kill 957
[14]
Eine nachträgliche Veränderung einer Tabelle kann z. B. mit INSERT INTO, UPDATE oder ALTER TABLE durchgeführt werden. Beispielsweise können weitere Zeilen (INSERT IN-TO) oder Spalten (ALTER TABLE) hinzugefügt werden oder einzelne Einträge (UPDATE) verändert werden.
# neue Tabelle anlegen:
CREATE TABLE tabelle1 (zaehler1 INT, datum DATETIME);
# eine Zeile einfügen:
INSERT INTO tabelle1 SET zaehler1 = 8, datum = '2015-01-01 10:00:00';
# neue Spalte hinzufügen:
ALTER TABLE tabelle1 ADD COLUMN zaehler2 INT;
# einen Werte in die neue Spalte schreiben:
UPDATE tabelle1 SET zaehler2 = 10 WHERE datum = '2015-01-01 10:00:00';
Das Festlegen einer Spalte als Schlüssel (ein für jede Zeile eindeutiger Index, für die Tabellen in EnMoLMU i. d. R. die Spaltedatum) kann eine Reihe von Vorgängen, z. B. das nachträgliche Einfügen größerer Datenmengen für nachträglich hinzugefügte Spalten, drastisch beschleunigen:
ALTER TABLE tabelle1 ADD PRIMARY KEY (datum);