/* <-- zurück zu Zeitintervalle in SQL verarbeiten */ /* MySQL: Schritte für die Datenaufbereitung in einem Skript */ /* Diese Lösung ist nicht sehr performant und braucht auf einem weniger schnellen Rechner bis zu 5 Minuten. */ /* Zahlen von 0 bis 9 in eine Hilfstabelle einfügen. Diese können später dafür genutzt werden, die Tage in die Tagestabelle und die Minuten in die Minutentabelle 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; /* alle Minuten des Tages in die Minutentabelle eintragen */ TRUNCATE TABLE zeitintervall_minute; INSERT INTO zeitintervall_minute (minute) SELECT MAKETIME( FLOOR((1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl) / 60), (1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl) % 60, 0 ) AS minute FROM ( SELECT zahl FROM zeitintervall_zahlen_0_bis_9 WHERE zahl <= 1 ) 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 1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl < 1440; /* alle Tage von 2001 bis 2019 in die Tagestabelle eintragen (6939 Tage) */ 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'; /* Nun Aufsplittung der Zeitintervalle in einzelne Datensätze pro Minute. Dafür wird ein JOIN aufgebaut, der in der ON-Bedingung keine Gleichheit abfragt, sondern Ungleichheit. */ TRUNCATE TABLE zeitintervall_testdaten_pro_minute; INSERT INTO zeitintervall_testdaten_pro_minute ( row_id, fallnr, datum_plus_minute, duplikat ) SELECT t1.row_id, t1.fallnr, ADDTIME(t2.tag, t3.minute) AS datum_plus_minute, 0 AS duplikat FROM zeitintervall_testdaten t1 INNER JOIN zeitintervall_tag t2 ON DATE(t1.beginn) <= t2.tag AND DATE(t1.ende) >= t2.tag INNER JOIN zeitintervall_minute t3 ON 1 = 1 /* zunächst die Minuten komplett als kartesisches Produkt nehmen, später werden Beginn- und Ende-Tag nachjustiert */ WHERE ( /* für den Tag des Intervallbeginns muss sichergestellt werden, dass nur die Minuten nach der Beginnzeit genommen werden */ DATE(t1.beginn) < t2.tag OR (DATE(t1.beginn) = t2.tag AND TIME(t1.beginn) <= t3.minute) ) AND ( /* für den Tag des Intervallendes muss sichergestellt werden, dass nur die Minuten vor der Endezeit genommen werden (dabei muss die Grenzminute ausgeschlossen werden) */ DATE(t1.ende) > t2.tag OR (DATE(t1.ende) = t2.tag AND TIME(t1.ende) > t3.minute) ); /* - Kennzeichnung der Duplikate pro Fallnr und Minute - nur ein Datensatz wird als gültig behalten - die anderen werden als Duplikate gekennzeichnet - damit der JOIN einigermaßen performant ist, muss der Index der Tabelle die Spalten "fallnr", "datum_plus_minute", "row_id" genau in der gleichen Reihenfolge enthalten, wie sie dann in der ON-Bedingung abgefragt werden -> bei mehreren Millionen Datensätzen ist sonst kein JOIN mehr möglich */ UPDATE zeitintervall_testdaten_pro_minute t1 INNER JOIN zeitintervall_testdaten_pro_minute t2 ON t1.fallnr = t2.fallnr AND t1.datum_plus_minute = t2.datum_plus_minute AND t1.row_id < t2.row_id SET t1.duplikat = 1; /* ermittelt pro fallnr und Tag die Gesamtminuten */ TRUNCATE TABLE zeitintervall_ergebnis; INSERT INTO zeitintervall_ergebnis ( fallnr, tag, minuten ) SELECT fallnr, DATE(datum_plus_minute) AS tag, COUNT(*) AS minuten FROM zeitintervall_testdaten_pro_minute WHERE duplikat = 0 GROUP BY fallnr, DATE(datum_plus_minute); /* Zeilen mit Minuten = 0 löschen */ DELETE FROM zeitintervall_ergebnis WHERE minuten = 0;