/* SQLite: 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 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;
/* 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. */
DELETE FROM zeitintervall_sortieren;
INSERT INTO zeitintervall_sortieren
(
fallnr,
zeitpunkt
)
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 der sortierten Zeitpunkte wird nun als einzelner Intervall betrachtet.
Der Zeitpunkt jeder Zeile wird als Beginn betrachtet und der 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 fallen beim JOIN weg, da es dafuer keine Entsprechung
mehr auf der rechten JOIN-Seite gibt. */
/* INDEX setzen zur Beschleunigung des folgenden JOINs */
DROP INDEX IF EXISTS zeitintervall_sortieren_fallnr_row_id;
CREATE INDEX zeitintervall_sortieren_fallnr_row_id ON zeitintervall_sortieren (fallnr, row_id);
DELETE FROM zeitintervall_zerlegt;
INSERT INTO zeitintervall_zerlegt
(
fallnr,
beginn,
ende
)
SELECT
t1.fallnr,
t1.zeitpunkt AS beginn,
t2.zeitpunkt AS ende
FROM zeitintervall_sortieren t1 INNER JOIN zeitintervall_sortieren t2
ON
t1.fallnr = t2.fallnr
AND t1.row_id + 1 = t2.row_id
ORDER BY t1.row_id;
/* Die naechsten Befehle erzeugen das Ergebnis. In der inneren SELECT-Abfrage werden zunaechst
diejenigen Teilintervalle aus den Testdaten ermittelt, die dort wirklich
vorkommen (manche Zerlegungsintervalle sind naemlich Luecken). Das GROUP BY sorgt dabei dafuer,
dass keine Intervalle doppelt in das Zwischenergebnis eingehen.
Dieses Zwischenergebnis wird in der aeusseren SELECT-Abfrage 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 per GROUP BY pro Fallnr und Tag aufsummiert. */
/* Um den folgenden JOIN zu beschleunigen, legen wir einen Index auf
die Spalten fallnr,beginn,ende der Tabelle zeitintervall_zerlegt */
DROP INDEX IF EXISTS zeitintervall_zerlegt_fallnr_beginn_ende;
CREATE INDEX zeitintervall_zerlegt_fallnr_beginn_ende ON zeitintervall_zerlegt (fallnr,beginn,ende);
/* Um den naechsten JOIN zu beschleunigen, legen wir einen Index auf die Spalte tag
in der Tabelle zeitintervall_tag */
DROP INDEX IF EXISTS zeitintervall_tag_tag;
CREATE INDEX zeitintervall_tag_tag ON zeitintervall_tag (tag);
/* Minuten pro Fallnr und Tag aufsummieren */
DELETE FROM 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 (strftime('%s',u1.ende) - strftime('%s',u1.beginn))/60
/* Beginntag */
WHEN DATE(u1.beginn) = u2.tag THEN 1440 - (strftime('%s',u1.beginn) - strftime('%s',u2.tag))/60
/* Endetag */
WHEN DATE(u1.ende) = u2.tag THEN (strftime('%s',u1.ende) - strftime('%s',u2.tag))/60
/* 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 loeschen, da sie fuer das Ergebnis keine Relevanz haben. */
DELETE FROM zeitintervall_ergebnis
WHERE
minuten = 0;