/* Oracle, Loesung 1, Schritte fuer die Datenaufbereitung in einem Skript */ /* Dieses Skript ist sehr performant. Es braucht nur wenige Sekunden. */ /* Zahlen von 0 bis 9 in eine Hilfstabelle einfuegen. Diese koennen spaeter dafuer 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 FROM dual UNION SELECT 1 AS zahl FROM dual UNION SELECT 2 AS zahl FROM dual UNION SELECT 3 AS zahl FROM dual UNION SELECT 4 AS zahl FROM dual UNION SELECT 5 AS zahl FROM dual UNION SELECT 6 AS zahl FROM dual UNION SELECT 7 AS zahl FROM dual UNION SELECT 8 AS zahl FROM dual UNION SELECT 9 AS zahl FROM dual; /* Ziel des naechsten 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 gueltigen Bedingung 1 = 1 formuliert wird. Als 4-fach-JOIN wuerde er 10.000 Zeilen erzeugen. Wenn wir eine bestimmte Anzahl von Tagen haben wollen, koennen wir dies mit einer WHERE-Klausel erreichen. Wir schraenken 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 TO_DATE('2001-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl, 'DAY') 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 TO_DATE('2001-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl, 'DAY') < TO_DATE('2020-01-01','YYYY-MM-DD'); /* Als naechstes 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 ( row_id, row_id_plus_1, fallnr, beginn ) SELECT ROW_NUMBER() OVER (ORDER BY fallnr, beginn) AS row_id, 1 + ROW_NUMBER() OVER (ORDER BY fallnr, beginn) AS row_id_plus_1, fallnr, beginn FROM ( SELECT fallnr, beginn FROM zeitintervall_testdaten UNION ALL SELECT fallnr, ende AS beginn FROM zeitintervall_testdaten ) abfrage GROUP 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. Hierfuer kommt ein SELF-JOIN zum Einsatz, der pro Fallnr jede Zeile mit ihrem Nachfolger verknuepft. Wir haben dann fuer jede Fallnr eine zusammenhaengende Folge von Teilintervallen. Die letzten Eintraege pro Fallnr werden geloescht, da sie keine Intervalle mehr enthalten. */ /* Der folgende UPDATE-Befehl funktioniert in Oracle nur dann, wenn der INNER JOIN eindeutige Ergebnisse liefert. Und zwar muss Oracle dies schon aus der Tabellendefinition derjenigen Tabelle herleiten koennen, die die Quelldaten bereitstellt. Es muss also ein UNIQUE KEY (oder PRIMARY KEY) auf beiden JOIN-Spalten zusammen oder auf einer der beiden JOIN-Spalten gesetzt sein, da dann der JOIN ueber zwei Spalten erst recht eindeutige Ergebnisse liefert. Da wir auf row_id einen UNIQUE KEY gesetzt haben, geht es. */ UPDATE ( SELECT t1.ende, t2.beginn FROM zeitintervall_zerlegt t1 INNER JOIN zeitintervall_zerlegt t2 ON t1.fallnr = t2.fallnr AND t1.row_id_plus_1 = t2.row_id ) SET ende = beginn; DELETE FROM zeitintervall_zerlegt WHERE ende IS NULL; /* Der naechste Befehl erzeugt das Ergebnis. Er besteht aus einer Unterabfrage, die dann mit der Tagestabelle per JOIN verknuepft wird. In der Unterabfrage werden zunaechst diejenigen Teilintervalle aus den Testdaten ermittelt, die dort wirklich vorkommen (manche Zerlegungsintervalle sind naemlich Luecken). Dieses Zwischenergebnis wird mit der Tagestabelle verlinkt, wobei jeder Teilintervall auf die einzelnen Tage verteilt wird, die er umfasst. Per Fallunterscheidung (CASE WHEN ...) werden fuer jeden Tagesabschnitt eines Teilintervalls die Minuten berechnet. Das Ganze wird am Ende pro Fallnr und Tag aufsummiert (GROUP BY). */ /* TRUNC() gibt von einem Datum + Uhrzeit nur den Datumswert zurueck. Da Oracle die Differenz aus zwei DateTime-Werten in Tagen als Kommazahl zurueckgibt, kann man durch Multiplikation mit 1440 die Differenz in Minuten berechnen. */ 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 TRUNC(u1.beginn) = TRUNC(u1.ende) THEN (u1.ende - u1.beginn) * 1440 /* Beginntag */ WHEN TRUNC(u1.beginn) = u2.tag THEN 1440 - (u1.beginn - u2.tag) * 1440 /* Endetag */ WHEN TRUNC(u1.ende) = u2.tag THEN (u1.ende - u2.tag) * 1440 /* 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 TRUNC(u1.beginn) <= u2.tag AND TRUNC(u1.ende) >= u2.tag GROUP BY u1.fallnr, u2.tag; /* Zeilen mit Minuten = 0 loeschen, da sie fuer das Ergebnis keine Relevanz haben. */ DELETE FROM zeitintervall_ergebnis WHERE minuten = 0; COMMIT;