/* Oracle, Loesung 2, 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; /* alle Tage von 2001 bis 2019 in die Tagestabelle eintragen (6939 Tage) */ 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'); /* Nun die Zeitintervalle sortieren. Dabei muessen diejenigen Zeitintervalle ausgeschlossen werden, die komplett innerhalb von einem anderen liegen. Diese Intervalle zaehlen fuer das Ergebnis sowieso nicht, und fuer die naechsten Schritte brauchen wir pro Fall eine Folge von Zeitintervallen, wo Beginn des Nachfolgers >= Beginn des Vorgaengers und auch das Ende des Nachfolgers >= Ende des Vorgaengers ist. */ TRUNCATE TABLE zeitintervall_sortiert; INSERT INTO zeitintervall_sortiert (row_id, row_id_plus_1, fallnr, beginn, ende) SELECT ROW_NUMBER() OVER (ORDER BY fallnr, beginn, ende) AS row_id, 1 + ROW_NUMBER() OVER (ORDER BY fallnr, beginn, ende) AS row_id_plus_1, fallnr, beginn, ende FROM zeitintervall_testdaten WHERE row_id NOT IN ( SELECT DISTINCT t1.row_id FROM zeitintervall_testdaten t1 INNER JOIN zeitintervall_testdaten t2 ON t1.fallnr = t2.fallnr AND t1.beginn > t2.beginn AND t1.ende < t2.ende ); /* Als naechstes werden die Stellen gekennzeichnet, wo der Beginn des Nachfolgeintervalls nach dem Ende des Vorgaengers liegt, denn bis dorthin kann man alle Intervalle einer Fallnr als zusammenhaengend betrachten und damit diesen Zeitraum vom Anfang bis zum Ende zaehlen. Mit dem naechsten Intervall beginnt ein neuer zusammenhaengender Intervall. */ /* 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 JOIN-Spalten gesetzt sein, da dann der JOIN ueber mehrere Spalten erst recht eindeutige Ergebnisse liefert. Da wir auf row_id einen UNIQUE KEY gesetzt haben, geht es. */ UPDATE ( SELECT t1.next_beginn, t2.beginn FROM zeitintervall_sortiert t1 INNER JOIN zeitintervall_sortiert t2 ON t1.fallnr = t2.fallnr AND t1.row_id_plus_1 = t2.row_id AND t1.ende < t2.beginn ) SET next_beginn = beginn; /* Die Laenge der zusammenhaengenden Intervalle wird nun nicht direkt ermittelt, sondern indirekt. Dafuer wird erstens die Gesamtzeit eines Falles vom ersten Beginn bis zum letzten Ende eingetragen. Zweitens werden die Luecken in der Gesamtzeit eingetragen, so dass spaeter die Laengen der zusammenhaengenden Intervalle daraus berechnet werden koennen. */ TRUNCATE TABLE zeitintervall_gesamt_u_luecken; INSERT INTO zeitintervall_gesamt_u_luecken (fallnr, beginn, beginntag, ende, endetag, luecke, faktor) SELECT fallnr, min_beginn, TRUNC(min_beginn) AS beginntag, max_ende, TRUNC(max_ende) AS endetag, luecke, faktor FROM ( /* zuerst den Gesamt-Zeitraum jedes Falles vom ersten Beginn bis zum letzten Ende eintragen */ SELECT fallnr, MIN(beginn) AS min_beginn, MAX(ende) AS max_ende, 'NEIN' AS luecke, 1 AS faktor FROM zeitintervall_sortiert GROUP BY fallnr UNION ALL /* danach die Luecken zusaetzlich eintragen und mit dem Faktor -1 versehen, damit sie subtrahiert werden koennen */ SELECT fallnr, ende, next_beginn, 'JA' AS luecke, -1 AS faktor FROM zeitintervall_sortiert WHERE next_beginn IS NOT NULL ) abfrage; /* Fuer das Ergebnis zeigt der Faktor (1 oder -1) an, ob der jeweilige Zeitraum positiv oder negativ einfliessen muss. */ TRUNCATE TABLE zeitintervall_ergebnis; INSERT INTO zeitintervall_ergebnis (fallnr, tag, minuten) SELECT t1.fallnr, t2.tag, SUM(t1.faktor * CASE WHEN t1.beginntag = t1.endetag THEN (t1.ende - t1.beginn) * 1440 WHEN t1.beginntag = t2.tag THEN 1440 - (t1.beginn - t2.tag) * 1440 WHEN t1.endetag = t2.tag THEN (t1.ende - t2.tag) * 1440 ELSE 1440 END ) AS minuten FROM zeitintervall_gesamt_u_luecken t1 INNER JOIN zeitintervall_tag t2 ON t1.endetag >= t2.tag AND t1.beginntag <= t2.tag GROUP BY t1.fallnr, t2.tag ORDER BY t1.fallnr, t2.tag; /* Zeilen mit Minuten = 0 loeschen */ DELETE FROM zeitintervall_ergebnis WHERE minuten = 0; COMMIT;