/* <-- zurück zu Zeitintervalle in SQL verarbeiten */ /* MySQL: Schritte für die Datenaufbereitung in einem Skript */ /* Dieses Skript ist sehr performant. Es braucht nur wenige Sekunden. */ /* Zahlen von 0 bis 9 in eine Hilfstabelle einfügen. Diese können später dafür genutzt werden, die Tage in
die Tagestabelle einzutragen. */ TRUNCATE TABLE zeitintervall_zahlen_0_bis_9; INSERT INTO zeitintervall_zahlen_0_bis_9 (zahl) SELECT 0 AS zahl UNION SELECT 1 AS zahl UNION SELECT 2 AS zahl UNION SELECT 3 AS zahl UNION SELECT 4 AS zahl UNION SELECT 5 AS zahl UNION SELECT 6 AS zahl UNION SELECT 7 AS zahl UNION SELECT 8 AS zahl UNION SELECT 9 AS zahl; /* Ziel des nächsten Schritts ist es, eine bestimmte Anzahl von Tagen in die Tagestabelle einzutragen. Dies wollen wir in nur einem SQL-Befehl tun. Deshalb erzeugen wir aus der Tabelle mit den Zahlen von 0 bis 9 einen mehrfachen CROSS JOIN (kartesisches Produkt), der hier als INNER JOIN mit der immer gültigen Bedingung 1 = 1 formuliert wird. Als 4-fach-JOIN würde er 10.000 Zeilen erzeugen. Wenn wir eine bestimmte Anzahl von Tagen haben wollen, können wir dies mit einer WHERE-Klausel erreichen. Wir schränken die Anzahl hier auf 6.939 Tage ein, was genau die Jahre 2001 bis 2019 ergibt. */ TRUNCATE TABLE zeitintervall_tag; INSERT INTO zeitintervall_tag (tag) SELECT ADDDATE('2001-01-01',1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl) AS tag FROM zeitintervall_zahlen_0_bis_9 t1 INNER JOIN zeitintervall_zahlen_0_bis_9 t2 ON 1 = 1 INNER JOIN zeitintervall_zahlen_0_bis_9 t3 ON 1 = 1 INNER JOIN zeitintervall_zahlen_0_bis_9 t4 ON 1 = 1 WHERE ADDDATE('2001-01-01',1000*t1.zahl + 100*t2.zahl + 10*t3.zahl + t4.zahl) < '2020-01-01'; /* Als nächstes zerlegen wir die Zeitintervalle so in Teilintervalle, dass sich pro Fallnr keine zwei Teilintervalle mehr gegenseitig schneiden. Dazu werden pro Fallnr alle Beginn- und Endezeitpunkte sortiert in einer Tabelle gesammelt. */ TRUNCATE TABLE zeitintervall_zerlegt; INSERT INTO zeitintervall_zerlegt ( fallnr, beginn ) SELECT DISTINCT fallnr, beginn FROM ( SELECT fallnr, beginn FROM zeitintervall_testdaten UNION ALL SELECT fallnr, ende AS beginn FROM zeitintervall_testdaten ) abfrage ORDER BY fallnr, beginn; /* Jede Zeile davon wird nun als einzelner Intervall betrachtet. Der Beginn-Zeitpunkt der Folgezeile wird als Ende-Zeitpunkt in jeder Zeile eingetragen. Hierfür kommt ein SELF-JOIN zum Einsatz, der pro Fallnr jede Zeile mit ihrem Nachfolger verknüpft. Wir haben dann für jede Fallnr eine zusammenhängende Folge von Teilintervallen. Die letzten Einträge pro Fallnr werden gelöscht, da sie keine Intervalle mehr enthalten. */ UPDATE zeitintervall_zerlegt t1 INNER JOIN zeitintervall_zerlegt t2 ON t1.fallnr = t2.fallnr AND t1.row_id + 1 = t2.row_id SET t1.ende = t2.beginn; DELETE FROM zeitintervall_zerlegt WHERE ende IS NULL; /* Der nächste Befehl erzeugt das Ergebnis. Er besteht aus einer Unterabfrage, die dann mit der Tagestabelle per JOIN verknüpft wird. In der Unterabfrage werden zunächst diejenigen Teilintervalle aus den Testdaten ermittelt, die dort wirklich vorkommen (manche Zerlegungsintervalle sind nämlich Lücken). Dieses Zwischenergebnis wird mit der Tagestabelle verlinkt, wobei jeder Teilintervall auf die einzelnen Tage verteilt wird, die er umfasst. Per Fallunterscheidung (CASE WHEN ...) werden für jeden Tagesabschnitt eines Teilintervalls die Minuten berechnet. Das Ganze wird am Ende pro Fallnr und Tag aufsummiert (GROUP BY). */ TRUNCATE TABLE zeitintervall_ergebnis; INSERT INTO zeitintervall_ergebnis (fallnr, tag, minuten) SELECT u1.fallnr, u2.tag, SUM( CASE /* Beginn und Ende fallen auf denselben Tag */ WHEN DATE(u1.beginn) = DATE(u1.ende) THEN TIMESTAMPDIFF(MINUTE, u1.beginn, u1.ende) /* Beginntag */ WHEN DATE(u1.beginn) = u2.tag THEN 1440 - TIMESTAMPDIFF(MINUTE, u2.tag, u1.beginn) /* Endetag */ WHEN DATE(u1.ende) = u2.tag THEN TIMESTAMPDIFF(MINUTE, u2.tag, u1.ende) /* volle Zwischentage */ ELSE 1440 END ) AS minuten FROM ( SELECT t2.fallnr, t2.beginn, t2.ende FROM zeitintervall_testdaten t1 INNER JOIN zeitintervall_zerlegt t2 ON t1.fallnr = t2.fallnr AND t1.beginn <= t2.beginn AND t1.ende >= t2.ende GROUP BY t2.fallnr, t2.beginn, t2.ende ) u1 INNER JOIN zeitintervall_tag u2 ON DATE(u1.beginn) <= u2.tag AND DATE(u1.ende) >= u2.tag GROUP BY u1.fallnr, u2.tag; /* Zeilen mit Minuten = 0 löschen, da sie für das Ergebnis keine Relevanz haben. */ DELETE FROM zeitintervall_ergebnis WHERE minuten = 0;