MSSQL 03 Daten aufbereiten » Dr. Holger Maaß

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

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

/* Diese Lösung ist nicht sehr performant und ist bei großen Datenmengen
   nicht mehr einsetzbar. */

/* alle Minuten des Tages in die Minutentabelle eintragen */

TRUNCATE TABLE zeitintervall_minute;

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_minute
(minute)
SELECT
    1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl AS minute
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
WHERE
1000 * t1.zahl + 100 * t2.zahl + 10 * t3.zahl + t4.zahl < 1440;

/* Dieser Befehl fügt 20.000 Tage (ca. 54 Jahre) ab 01.01.2000 in die
   Tages-Tabelle ein. Er braucht dafür weniger als 0,5 Sekunden. */

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;

/* Nun Aufsplittung der Zeitintervalle in einzelne Datensätze pro Minute.
   Dafür wird ein JOIN aufgebaut, der in der ON-Bedingung keine Gleichheit 
   abfragt, sondern Ungleichheit. */

TRUNCATE TABLE zeitintervall_testdaten_pro_minute;

INSERT INTO zeitintervall_testdaten_pro_minute
(
    row_id,
    fallnr,
    datum_plus_minute,
    duplikat
)
SELECT
    t1.row_id,
    t1.fallnr, 
    DATEADD(MINUTE, t3.minute, t2.tag) AS datum_plus_minute, 
    0 AS duplikat
FROM (
    SELECT
        row_id,
        fallnr,
        beginn,
        CONVERT(DATETIME, CONVERT(VARCHAR(10), beginn, 120), 120) beginntag,
        ende,
        CONVERT(DATETIME, CONVERT(VARCHAR(10), ende, 120), 120) endetag
    FROM zeitintervall_testdaten
) t1 INNER JOIN zeitintervall_tag t2
ON
    t1.beginntag <= t2.tag
AND t1.endetag   >= t2.tag
INNER JOIN zeitintervall_minute t3
ON 1 = 1 /* zunächst die Minuten komplett als kartesisches Produkt nehmen,
            später werden Beginn- und Ende-Tag nachjustiert */
WHERE (
   /* für den Tag des Intervallbeginns muss sichergestellt werden, 
      dass nur die Minuten nach der Beginnzeit genommen werden */
   t1.beginntag < t2.tag
   OR 
   (t1.beginntag = t2.tag AND DATEDIFF(MINUTE,t1.beginntag,t1.beginn) <= t3.minute)
)
AND (
   /* für den Tag des Intervallendes muss sichergestellt werden, 
      dass nur die Minuten vor der Endezeit genommen werden
      (dabei muss die Grenzminute ausgeschlossen werden) */
   t1.endetag > t2.tag 
   OR 
   (t1.endetag = t2.tag AND DATEDIFF(MINUTE, t1.endetag, t1.ende) > t3.minute)
);

/*
- Kennzeichnung der Duplikate pro Fallnr und Minute
- nur ein Datensatz wird als gültig behalten
- die anderen werden als Duplikate gekennzeichnet
- damit der JOIN einigermaßen performant ist, muss der Index der Tabelle 
  die Spalten "fallnr", "datum_plus_minute", "row_id" genau in der gleichen Reihenfolge 
  enthalten, wie sie dann in der ON-Bedingung abgefragt werden
-> bei mehreren Millionen Datensätzen ist sonst kein JOIN mehr möglich
*/

UPDATE t1
SET t1.duplikat = 1
FROM zeitintervall_testdaten_pro_minute t1 INNER JOIN zeitintervall_testdaten_pro_minute t2
ON
    t1.fallnr = t2.fallnr
AND t1.datum_plus_minute = t2.datum_plus_minute
AND t1.row_id < t2.row_id;

/* ermittelt pro fallnr und Tag die Gesamtminuten */

TRUNCATE TABLE zeitintervall_ergebnis;

INSERT INTO zeitintervall_ergebnis
(
    fallnr,
    tag,
    minuten
)
SELECT
    fallnr,
    CONVERT(DATETIME, CONVERT(VARCHAR(10), datum_plus_minute, 120), 120) AS tag,
    COUNT(*) AS minuten
FROM zeitintervall_testdaten_pro_minute
WHERE
duplikat = 0
GROUP BY 
fallnr, CONVERT(DATETIME, CONVERT(VARCHAR(10), datum_plus_minute, 120), 120);

/* Zeilen mit Minuten = 0 löschen */

DELETE FROM zeitintervall_ergebnis
WHERE
minuten = 0;