MSSQL 01 Daten aufbereiten » Dr. Holger Maaß

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

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

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

/* Im nächsten Schritt werden 20.000 Tage (ca. 54 Jahre) ab 01.01.2000 
   in die Tages-Tabelle eingefügt. Dies tun wir mit nur einem SQL-Befehl.
   Deshalb erzeugen wir aus den Zahlen von 0 bis 9 einen mehrfachen
   CROSS JOIN (kartesisches Produkt), der hier als INNER JOIN mit der immer
   gültigen Bedingung 1 = 1 formuliert wird. Er fügt im Ergebnis
   2*10*10*10*10 = 20.000 Zeilen in der Tages-Tabelle ein. */

TRUNCATE TABLE zeitintervall_tag;

WITH zahlen_0_bis_9 AS 
(
          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
)

INSERT INTO zeitintervall_tag
(tag)
SELECT
DATEADD(DAY, 
        10000 * t1.zahl + 1000 * t2.zahl + 100 * t3.zahl + 10 * t4.zahl + t5.zahl, 
        CONVERT(DATETIME, '2000-01-01',120)
        ) AS tag
FROM (
    SELECT zahl 
    FROM zahlen_0_bis_9
    WHERE
    zahl <= 1
) t1 INNER JOIN zahlen_0_bis_9 t2
ON
1 = 1
INNER JOIN zahlen_0_bis_9 t3
ON
1 = 1
INNER JOIN zahlen_0_bis_9 t4
ON
1 = 1
INNER JOIN zahlen_0_bis_9 t5
ON
1 = 1;

/* Als nächstes 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. */

TRUNCATE TABLE zeitintervall_zerlegt;

INSERT INTO zeitintervall_zerlegt
(
    fallnr,
    beginn
)
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 davon wird nun als einzelner Intervall betrachtet. Der Beginn-Zeitpunkt 
   der Folgezeile wird als Ende-Zeitpunkt in jeder Zeile eingetragen. Hierfür
   kommt ein SELF-JOIN zum Einsatz, der pro Fallnr jede Zeile mit ihrem Nachfolger
   verknüpft. Wir haben dann für jede Fallnr eine zusammenhängende
   Folge von Teilintervallen. Die letzten Einträge pro Fallnr werden gelöscht,
   da sie keine Intervalle mehr enthalten. */

UPDATE t1
SET t1.ende = t2.beginn
FROM zeitintervall_zerlegt t1 INNER JOIN zeitintervall_zerlegt t2
ON
    t1.fallnr = t2.fallnr
AND t1.row_id + 1 = t2.row_id;

DELETE FROM zeitintervall_zerlegt
WHERE
ende IS NULL;

/* Der nächste Befehl erzeugt das Ergebnis. Er besteht aus einer Unterabfrage,
   die dann mit der Tagestabelle per JOIN verknüpft wird. In der Unterabfrage
   werden zunächst diejenigen Teilintervalle aus den Testdaten ermittelt, die
   dort wirklich vorkommen (manche Zerlegungsintervalle sind nämlich Lücken).
   Dieses Zwischenergebnis wird mit der Tagestabelle verlinkt, wobei jeder Teilintervall
   auf die einzelnen Tage verteilt wird, die er umfasst. Per Fallunterscheidung
   (CASE WHEN ...) werden für jeden Tagesabschnitt eines Teilintervalls die Minuten
   berechnet. Das Ganze wird am Ende pro Fallnr und Tag aufsummiert (GROUP BY). */

TRUNCATE TABLE zeitintervall_ergebnis;

INSERT INTO zeitintervall_ergebnis
(fallnr, tag, minuten)
SELECT
    u1.fallnr,
    u2.tag,
    SUM(
        CASE
            /* Beginn und Ende fallen auf denselben Tag */
            WHEN CONVERT(DATETIME, CONVERT(VARCHAR(10), u1.beginn, 120), 120) 
               = CONVERT(DATETIME, CONVERT(VARCHAR(10), u1.ende, 120), 120) 
                THEN DATEDIFF(MINUTE, u1.beginn, u1.ende)
            /* Beginntag */
            WHEN CONVERT(DATETIME, CONVERT(VARCHAR(10), u1.beginn, 120), 120) = u2.tag 
                THEN 1440 - DATEDIFF(MINUTE, u2.tag, u1.beginn)
            /* Endetag */
            WHEN CONVERT(DATETIME, CONVERT(VARCHAR(10), u1.ende, 120), 120)  = u2.tag 
                THEN DATEDIFF(MINUTE, u2.tag, u1.ende)
            /* 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
    CONVERT(DATETIME, CONVERT(VARCHAR(10), u1.beginn, 120), 120) <= u2.tag
AND CONVERT(DATETIME, CONVERT(VARCHAR(10), u1.ende  , 120), 120) >= u2.tag
GROUP BY u1.fallnr, u2.tag;


/* Zeilen mit Minuten = 0 löschen, da sie für das Ergebnis keine Relevanz haben. */

DELETE FROM zeitintervall_ergebnis
WHERE
minuten = 0;