/* SQLite: 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 einzutragen. */ DELETE FROM 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 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 erzeugt er 10.000 Zeilen vom 01.01.2001 bis 18.05.2028. */ DELETE FROM zeitintervall_tag; INSERT INTO zeitintervall_tag (tag) SELECT DATE( /* UNIX-TIMESTAMP fuer den 01.01.2001 */ strftime('%s','2001-01-01') + /* + die Zahlen 0 bis 9999 (die Tage) umgerechnet in Sekunden */ 24*60*60*(1000*t1.zahl + 100*t2.zahl + 10*t3.zahl + t4.zahl), /* diese Sekunden werden am Ende wieder in ein Datum umgewandelt */ 'unixepoch') 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; DROP INDEX IF EXISTS zeitintervall_testdaten_fallnr_beginn_ende; CREATE INDEX zeitintervall_testdaten_fallnr_beginn_ende ON zeitintervall_testdaten (fallnr,beginn,ende); /* Wir bereiten als erstes den Schritt "Zeitintervalle sortieren" vor. Dabei schliessen wir diejenigen Zeitintervalle aus, 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 Ende des Nachfolgers >= Ende des Vorgaengers ist. */ /* Zeitintervalle identifizieren, die vollstaendig innerhalb eines anderen Intervalls liegen. Die Zeilennummern (row_id) dieser Intervalle werden zwischengespeichert. */ DELETE FROM zeitintervall_irrelevant; INSERT INTO zeitintervall_irrelevant (row_id) 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; DROP INDEX IF EXISTS zeitintervall_irrelevant_row_id; CREATE INDEX zeitintervall_irrelevant_row_id ON zeitintervall_irrelevant (row_id); /* Nun werden mit Hilfe eines LEFT OUTER JOIN diejenigen Intervalle ausgewaehlt, die nicht zu den auszuklammernden gehoeren, die also die relevanten Intervalle sind. */ DELETE FROM zeitintervall_sortiert_temp; INSERT INTO zeitintervall_sortiert_temp (fallnr, beginn, ende) SELECT t1.fallnr, t1.beginn, t1.ende FROM zeitintervall_testdaten t1 LEFT OUTER JOIN zeitintervall_irrelevant t2 ON t1.row_id = t2.row_id WHERE t2.row_id IS NULL ORDER BY t1.fallnr, t1.beginn, t1.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. */ DROP INDEX IF EXISTS zeitintervall_sortiert_temp_fallnr_row_id; CREATE INDEX zeitintervall_sortiert_temp_fallnr_row_id ON zeitintervall_sortiert_temp (fallnr,row_id); DELETE FROM zeitintervall_sortiert; INSERT INTO zeitintervall_sortiert (fallnr, beginn, ende, next_beginn) SELECT t1.fallnr, t1.beginn, t1.ende, t2.beginn AS next_beginn /* ist NULL, wenn die ON-Bedingung nicht erfuellt ist */ FROM zeitintervall_sortiert_temp t1 LEFT OUTER JOIN zeitintervall_sortiert_temp t2 ON t1.fallnr = t2.fallnr AND t1.row_id + 1 = t2.row_id AND t1.ende < t2.beginn ORDER BY t1.row_id; /* 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. */ DROP INDEX IF EXISTS zeitintervall_tag_tag; CREATE INDEX zeitintervall_tag_tag ON zeitintervall_tag (tag); /* Fuer das Ergebnis zeigt der Faktor (+1 oder -1) an, ob der jeweilige Zeitraum positiv oder negativ einfliessen muss. */ DELETE FROM zeitintervall_ergebnis; INSERT INTO zeitintervall_ergebnis (fallnr, tag, minuten) SELECT t1.fallnr, t2.tag, /* faktor +1 (Gesamtintervall), faktor -1 (Luecke) */ SUM(t1.faktor * CASE /* Beginn und Ende fallen auf denselben Tag */ WHEN t1.beginntag = t1.endetag THEN (strftime('%s',t1.ende) - strftime('%s',t1.beginn))/60 /* Beginntag */ WHEN t1.beginntag = t2.tag THEN 1440 - (strftime('%s',t1.beginn) - strftime('%s',t2.tag))/60 /* Endetag */ WHEN t1.endetag = t2.tag THEN (strftime('%s',t1.ende) - strftime('%s',t2.tag))/60 /* volle Zwischentage */ ELSE 1440 END ) AS minuten FROM ( /* zuerst den Gesamt-Zeitraum jedes Falles vom ersten Beginn bis zum letzten Ende eintragen */ SELECT fallnr, MIN(beginn) AS beginn, DATE(MIN(beginn)) AS beginntag, MAX(ende) AS ende, DATE(MAX(ende)) AS endetag, '' 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 AS beginn, DATE(ende) AS beginntag, next_beginn AS ende, DATE(next_beginn) AS endetag, 'X' AS luecke, -1 AS faktor FROM zeitintervall_sortiert WHERE next_beginn IS NOT NULL ) t1 INNER JOIN zeitintervall_tag t2 ON t1.endetag >= t2.tag AND t1.beginntag <= t2.tag GROUP BY t1.fallnr, t2.tag; /* Zeilen mit Minuten = 0 loeschen */ DELETE FROM zeitintervall_ergebnis WHERE minuten = 0;