/* SQLite: Loesung 3, Schritte fuer die Datenaufbereitung in einem Skript */
/* Diese Loesung ist nicht sehr performant und braucht auf einem weniger schnellen
Rechner bis zu 1 Minute. */
/* 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. */
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;
/* alle Minuten des Tages in die Minutentabelle eintragen */
DELETE FROM zeitintervall_minute;
INSERT INTO zeitintervall_minute
(minute)
SELECT
1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl 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;
/* 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;
/* Nun Aufsplittung der Zeitintervalle in einzelne Datensaetze pro Minute.
Dafuer wird ein JOIN aufgebaut, der in der ON-Bedingung keine Gleichheit
abfragt, sondern Ungleichheit. */
DELETE FROM zeitintervall_testdaten_pro_minute;
/* Indizes zur Beschleunigung des JOINs erstellen */
DROP INDEX IF EXISTS zeitintervall_tag_tag;
CREATE INDEX zeitintervall_tag_tag ON zeitintervall_tag (tag);
DROP INDEX IF EXISTS zeitintervall_minute_minute;
CREATE INDEX zeitintervall_minute_minute ON zeitintervall_minute (minute);
INSERT INTO zeitintervall_testdaten_pro_minute
(
row_id,
fallnr,
datum_plus_minute
)
SELECT
t1.row_id,
t1.fallnr,
/* UNIX-TIMESTAMP fuer t2.tag + t3.minute in Sekunden, danach in DATETIME konvertieren */
DATETIME(strftime('%s',t2.tag) + 60 * t3.minute,'unixepoch') AS datum_plus_minute
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 /* zunaechst die Minuten komplett als kartesisches Produkt nehmen,
spaeter werden Beginn- und Ende-Tag nachjustiert (WHERE-Klausel) */
WHERE (
/* fuer 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 60 * strftime('%H',t1.beginn) + strftime('%M',t1.beginn) <= t3.minute
)
)
AND (
/* fuer 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 60 * strftime('%H',t1.ende) + strftime('%M',t1.ende) > t3.minute
)
);
/* ermittelt pro fallnr und Tag die Gesamtminuten */
DELETE FROM zeitintervall_ergebnis;
INSERT INTO zeitintervall_ergebnis
(
fallnr,
tag,
minuten
)
SELECT
fallnr,
DATE(datum_plus_minute) AS tag,
COUNT(*) AS minuten
FROM (
/* Pro Fallnr und Minute darf nur ein Datensatz als gueltig betrachtet werden. */
SELECT
fallnr,
datum_plus_minute
FROM zeitintervall_testdaten_pro_minute
GROUP BY fallnr, datum_plus_minute
) abfrage
GROUP BY fallnr, DATE(datum_plus_minute);
/* Zeilen mit Minuten = 0 loeschen */
DELETE FROM zeitintervall_ergebnis
WHERE
minuten = 0;