MySQL 02 Daten aufbereiten » Dr. Holger Maaß

/* <-- zurück zu Zeitintervalle in SQL verarbeiten */

/* MySQL: Schritte für die Datenaufbereitung in einem Skript */

/* Dieses Skript ist sehr performant. Es braucht nur wenige Sekunden. */

/* Zahlen von 0 bis 9 in eine Hilfstabelle einfügen.
   Diese können später dafür genutzt werden, die Tage in 
   die Tagestabelle einzutragen. */
   
TRUNCATE TABLE 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 Tage von 2001 bis 2019 in die Tagestabelle eintragen (6939 Tage) */

TRUNCATE TABLE zeitintervall_tag;

INSERT INTO zeitintervall_tag
(tag)
SELECT
  ADDDATE('2001-01-01',1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl) 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
ADDDATE('2001-01-01',1000*t1.zahl + 100*t2.zahl + 10*t3.zahl + t4.zahl) < '2020-01-01';

/* Nun die Zeitintervalle sortieren.
   Dabei müssen diejenigen Zeitintervalle ausgeschlossen werden, die komplett 
   innerhalb von einem anderen liegen. Diese Intervalle zählen für das Ergebnis 
   sowieso nicht, und für die nächsten Schritte brauchen wir pro Fall eine Folge 
   von Zeitintervallen, wo Beginn des Nachfolgers >= Beginn des Vorgängers 
   und auch das Ende des Nachfolgers >= Ende des Vorgängers ist. */

TRUNCATE TABLE zeitintervall_sortiert;

INSERT INTO zeitintervall_sortiert 
(fallnr, beginn, ende)
SELECT 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 
)
ORDER BY fallnr, beginn, ende;

/* Als nächstes werden die Stellen gekennzeichnet, wo der Beginn des
   Nachfolgeintervalls nach dem Ende des Vorgängers liegt, denn bis dorthin
   kann man alle Intervalle einer Fallnr als zusammenhängend betrachten und
   damit diesen Zeitraum vom Anfang bis zum Ende zählen. Mit dem nächsten
   Intervall beginnt ein neuer zusammenhängender Intervall. */

UPDATE zeitintervall_sortiert t1 INNER JOIN zeitintervall_sortiert t2 
ON 
    t1.fallnr = t2.fallnr
AND t1.row_id + 1 = t2.row_id
AND t1.ende < t2.beginn
SET 
t1.next_beginn = t2.beginn;

/* Die Länge der zusammenhängenden Intervalle wird nun nicht direkt ermittelt, 
   sondern indirekt. Dafür wird erstens die Gesamtzeit eines Falles vom ersten
   Beginn bis zum letzten Ende eingetragen. Zweitens werden die Lücken in der
   Gesamtzeit eingetragen, so dass später die Längen der zusammenhängenden
   Intervalle daraus berechnet werden können. */

TRUNCATE TABLE zeitintervall_gesamt_und_luecken;

INSERT INTO zeitintervall_gesamt_und_luecken 
(fallnr, beginn, beginntag, ende, endetag, luecke, faktor)
SELECT 
    fallnr, 
    min_beginn, 
    DATE(min_beginn) AS beginntag,
    max_ende, 
    DATE(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, 
        '' AS luecke, 
        1 AS faktor
    FROM zeitintervall_sortiert
    GROUP BY fallnr

    UNION ALL
    /* danach die Lücken zusätzlich eintragen und mit dem Faktor -1 versehen, 
       damit sie subtrahiert werden können */
    SELECT
        fallnr, 
        ende, 
        next_beginn, 
        'X' AS luecke, 
        -1 AS faktor
    FROM zeitintervall_sortiert
    WHERE
    next_beginn IS NOT NULL
) abfrage;

/* Für das Ergebnis zeigt der Faktor (1 oder -1) an, ob der jeweilige Zeitraum
   positiv oder negativ einfließen 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 TIMESTAMPDIFF(MINUTE, t1.beginn, t1.ende)
            WHEN t1.beginntag = t2.tag THEN 1440 - TIMESTAMPDIFF(MINUTE, t2.tag, t1.beginn)
            WHEN t1.endetag = t2.tag THEN TIMESTAMPDIFF(MINUTE, t2.tag, t1.ende)
            ELSE 1440
        END
    ) AS minuten
FROM zeitintervall_gesamt_und_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 löschen */

DELETE FROM zeitintervall_ergebnis
WHERE
minuten = 0;