/* Oracle, 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. */
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 Minuten des Tages in die Minutentabelle eintragen */
TRUNCATE TABLE 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;
/* 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 Aufsplittung der Zeitintervalle in einzelne Datensaetze pro Minute.
Dafuer wird ein JOIN aufgebaut, der in der ON-Bedingung keine Gleichheit
abfragt, sondern Ungleichheit. */
TRUNCATE TABLE zeitintervall_testdat_pro_min;
INSERT INTO zeitintervall_testdat_pro_min
(
row_id,
fallnr,
datum_plus_minute
)
SELECT
t1.row_id,
t1.fallnr,
t2.tag + NUMTODSINTERVAL(t3.minute, 'MINUTE') AS datum_plus_minute
FROM zeitintervall_testdaten t1 INNER JOIN zeitintervall_tag t2
ON
TRUNC(t1.beginn) <= t2.tag
AND TRUNC(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 */
TRUNC(t1.beginn) < t2.tag OR (
TRUNC(t1.beginn) = t2.tag
AND 60 * TO_NUMBER(TO_CHAR(t1.beginn,'HH24')) + TO_NUMBER(TO_CHAR(t1.beginn,'MI')) <= 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) */
TRUNC(t1.ende) > t2.tag OR (
TRUNC(t1.ende) = t2.tag
AND 60 * TO_NUMBER(TO_CHAR(t1.ende,'HH24')) + TO_NUMBER(TO_CHAR(t1.ende,'MI')) > t3.minute
)
);
/* ermittelt pro fallnr und Tag die Gesamtminuten */
TRUNCATE TABLE zeitintervall_ergebnis;
INSERT INTO zeitintervall_ergebnis
(
fallnr,
tag,
minuten
)
SELECT
fallnr,
TRUNC(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_testdat_pro_min
GROUP BY fallnr, datum_plus_minute
) abfrage
GROUP BY fallnr, TRUNC(datum_plus_minute);
/* Zeilen mit Minuten = 0 loeschen */
DELETE FROM zeitintervall_ergebnis
WHERE
minuten = 0;
COMMIT;