mehrere Teilzeiträume zu einem Komplettzeitraum zusammenfassen

[pali99]

Guten Tag,

ich habe eine Tabelle mit ID, Begin und Ende. Zu jeder ID kann es verschiedene Zeiträume geben, welche aufeinander aufbauen oder aber Lücken dazwischen haben. Diese Teilzeiträume möchte ich in eine temporäre Tabelle laden und dann dort "zusammenfassen"

Hier ein Bsp zur Erläuterung:
ID - Start - Ende
99 - 1.1.2010 - 31.12.2010
99 - 1.1.2011 - 31.12.2011
99 - 1.1.2012 - 30.6.2012
99 -1.1.2013 - 30.7.2013


Als Ergebnis möchte ich
ID - Start - Ende
99 - 1.1.2010 - 30.6.2012
99 - 1.1.2013 - 30.07.2013

Wie kann ich dies mit SQL Management 2005 umsetzen? Vielen Dank!

 

[akretschmer]

ich habe eine Tabelle mit ID, Begin und Ende. Zu jeder ID kann es verschiedene Zeiträume geben, welche aufeinander aufbauen oder aber Lücken dazwischen haben. Diese Teilzeiträume möchte ich in eine temporäre Tabelle laden und dann dort "zusammenfassen"

Mal so als grobe Idee, keine fertige Lösung (ich hab kein M$SQL).

Ich denke, das sollte über Window-Funktionen machbar sein. Prüfen, ob der Vorgänger-Zeitraum mit dem jetzigen in Verbindung ist. Mal ganz grob als Ansatz, allerdings in PostgreSQL und unter Verwendung von DATERANGE-Datentypen:

test=*# select * from pali99;
 id |  dr
----+-------------------------
 99 | [2010-01-01,2011-01-01)
 99 | [2011-01-01,2012-01-01)
 99 | [2012-01-01,2012-07-01)
 99 | [2013-01-01,2013-08-01)
(4 rows)

So, nicht vollständig:

test=*#  select *, case when lag(dr) over (order by lower(dr)) -|- dr then true else false end  from pali99;
 id |  dr  | case
----+-------------------------+------
 99 | [2010-01-01,2011-01-01) | f
 99 | [2011-01-01,2012-01-01) | t
 99 | [2012-01-01,2012-07-01) | t
 99 | [2013-01-01,2013-08-01) | f
(4 rows)

In der case-Spalte siehst Du 2 mal ein t für TRUE, diese kann man zusammenfassen. Der Operator -|- auf Ranges prüft, ob beide an ihren Grenzen aufeinanderfolgen.

Nun addieren wir 2 Ranges:

test=*# select *, case when (lag(dr) over (order by lower(dr)) -|- dr) then lag(dr) over (order by lower(dr)) + dr else null end from pali99 ;
 id |  dr  |  case
----+-------------------------+-------------------------
 99 | [2010-01-01,2011-01-01) |
 99 | [2011-01-01,2012-01-01) | [2010-01-01,2012-01-01)
 99 | [2012-01-01,2012-07-01) | [2011-01-01,2012-07-01)
 99 | [2013-01-01,2013-08-01) |
(4 rows)

Also, wenn der Vorgänger und der aktuelle Datensatz unmittelbar hintereinander kommen, werden diese nun addiert, in der dritten Spalte. Zu prüfen wäre nun noch, ob auch der Vor-Vorgänger mit zu addieren ist. Dazu müßte man die lag() - Funktion entsprechend erweitern.

Ein anderer Ansatz: man versucht Gruppen zu bilden:

test=*# select * from (select *, row_number() over (partition by id order by lower(dr)), case when lag(dr) over (order by lower(dr)) -|- dr then true else false end as x  from pali99) foo;
 id |  dr  | row_number | x
----+-------------------------+------------+---
 99 | [2010-01-01,2011-01-01) |  1 | f
 99 | [2011-01-01,2012-01-01) |  2 | t
 99 | [2012-01-01,2012-07-01) |  3 | t
 99 | [2013-01-01,2013-08-01) |  4 | f
(4 rows)

Hier kannst Du nun die zusammenfassen, die entweder row_number = 1 haben oder aber ein t in der x-Spalte haben.


Wie gesagt, nur als grobe Idee.

 

[pali99]

Hallo,

Danke für den groben Vorschlag akretschmer. So etwas mit prüfen auf Vorgänger Zeitraum, hab ich mir in der Theorie auch schon gedacht....aber an der Praxis hapert es

Leider hab ich auf Arbeit aktuell nur SQL-Management Studio. Die Lösung sollte dort passieren. Hat noch jemand eine Idee? Danke!

 

[ukulele]

Bis auf lag() sollte der Code auch in MSSQL 2005 laufen, allerdings hast du nicht gesagt wie du die Daten zusammen fassen willst. Sollen nur Zeiträume "zusammen gefasst" werden, die nahtlos in einander übergehen oder auch welche, die Lücken aufweisen?

 

[Distrilec]

Vllt. mal ein kleiner Denkanstoss aus der Oracle-Ecke
Ich denke nicht dass das Ergebnis für alle möglichen Konstellationen passt... Aber es ist ein Anfang

SQL>
SQL> Select *
  2  From   t;
        ID BEGIN       END
---------- ----------- -----------
         2 07.11.2015  06.11.2016
         2 07.11.2016  06.11.2017
         2 07.11.2017  06.11.2018
         2 07.11.2013  06.11.2014
         1 07.11.2014  06.11.2015
         1 07.11.2015  06.11.2016
         1 07.11.2016  06.11.2017
         1 07.11.2017  06.11.2018
         1 07.11.2013  06.11.2014
         2 07.11.2014  06.11.2015
10 rows selected

SQL> Select t.id, connect_by_root(t.begin), t.end
  2  From
  3  (Select id, begin, end
  4  From   t
  5  order by id, begin, end) t
  6  Where connect_by_isleaf = 1
  7  Connect by t.begin = prior t.end +1
  8  And     t.id = prior t.id
  9  Start with (Select min(begin) from ifsapp.fps_max_temp where id = t.id) = t.begin;
        ID CONNECT_BY_ROOT(T.BEGIN) END
---------- ------------------------ -----------
         1 07.11.2013               06.11.2018
         2 07.11.2013               06.11.2018

 

[ukulele]

Also ein Join der an bestehende Zeiträume einen Nachfolgezeitraum anhängt wäre sicher das einfachste. Dieser Join müsste allerdings solange widerholt werden, wie Zeiträume hintereinander erkannt werden, das würde ich tatsächlich mal in einer Schleife abbilden.

SELECT    t1.id,
        t1.start,
        (    CASE
            WHEN    t2.ende IS NOT NULL
            THEN    t2.ende
            ELSE    t1.ende
            END ) AS ende
FROM    tabelle t1
INNER JOIN tabelle t2
ON        t1.id = t2.id
AND        t1.ende = t2.start - 1
WHERE NOT EXISTS (    SELECT    *
                    FROM    tabelle
                    WHERE    tabelle.id = t1.id
                    AND        tabelle.ende = t1.start - 1 )

Schwer wirds wenn Zeiträume überlappen, kann das passieren?

 

[pali99]

Hallo Ukulele,

das sieht schon ganz gut aus, werde ich morgen früh gleich mal testen auf Arbeit.
Eine Überlappung sollte es nicht geben dürfen, es kann nur "Lücken" dazwischen geben, welche ja dann auffallen sollten.
Danke schon mal für die vielen Lösungsvorschläge.

 

[pali99]

Hallo Ukulele,

ich habe ja nur eine Tabelle mit (t1_von, t1_bis) als Start und Ende Zeiträume. Und die Felder (t1_id1, t1_id2, t1_id3) welche als Keys übereinstimmen müssen.
In deinem Beispiel sprichst du von einer zweiten Tabelle t2. Was meinst du damit? Wie kann ich das in meinem Fall umsetzen?

Danke!

 

[ukulele]

t1 und t2 sind nur Aliase für die selbe Tabelle. Du joinst deine Tabelle mit sich selbst, insgesamt greifst du sogar dreimal darauf zu (nochmal im Subselect bei EXISTS.

 

[pali99]

Hallo Ukulele,

Vielen Dank, das hat funktioniert!!!

 

[pali99]

Hallo Ukulele,

leider hat es doch noch nicht ganz funktioniert. Es hat zwar 2 Zeiträume zusammengehangen, aber sobald es mehr als 2 sind, klappt es nicht mehr. Dann tut er so es gäbe es Zeit3 + Zeit4 + Zeitx nicht.

Kann mann das noch erweitern? Danke!!!

 

[ukulele]

Das ist mir bewusst.

Dieser Join müsste allerdings solange widerholt werden, wie Zeiträume hintereinander erkannt werden, das würde ich tatsächlich mal in einer Schleife abbilden.

Willst du die Daten dauerhaft zusammen führen (also einmalig bereinigen) oder soll das nur eine Sicht auf die weiterin fragmentierten Ursprungsdaten werden?

 

[pali99]

Hallo Ukulelel, Danke für die schnelle Antwort!!!

Nein die Fälle sollen nicht dauerhaft bereinigt werden. Ja es geht nur um eine Sicht (temp.Tabelle) der Teilzeiträumen.

Vielleicht noch als Erklärung dazu. Der Teil den ich hier angefragt habe, ist nur ein erster Teil meiner eigentlichen Selektion.
Meine Frage bezieht sich auf eine Tabelle "Zugelassene Zeiten". Dort sind alle Zeiträume drin, wo eine Vertrag zulässig ist. Aber eben immer in Teilzeiträumen. (VertragsID, Start,Ende) Mit dieser hier gefragten Zusammenfassung der Daten, möchte ich zur Vereinfachung erstmal die Teilzeiträume zusammenfassen und "Lücken" identifizieren.

Ausserdem haben wir eine Tabelle "Programmteilnehmer" (mit TeilnehmerID, VertragsID,Begin,Ende).
Als Ergebnis ist zu prüfen ob es Programmteilnehmer gibt (TeilnehmerID), welche für einen Zeitraum Programmteilnehmer sind, aber für diese VertragsID welche sie Teilnehmer sind, es gar keinen Eintrag in "Zugelassene Zeiten" gibt.

 

[ukulele]

Also es geht natürlich auch "Live" und in voller pracht. Fällt in die Kategorie "Spaß mit CTE" und kann für Verwirrung sorgen:

CREATE TABLE tabelle(
    id INT,
    start DATETIME,
    ende DATETIME
    );
INSERT INTO tabelle(id,start,ende) VALUES(99,cast('2010-01-01' AS DATETIME),cast('2010-31-12' AS DATETIME));
INSERT INTO tabelle(id,start,ende) VALUES(99,cast('2011-01-01' AS DATETIME),cast('2011-31-12' AS DATETIME));
INSERT INTO tabelle(id,start,ende) VALUES(99,cast('2012-01-01' AS DATETIME),cast('2012-30-06' AS DATETIME));
INSERT INTO tabelle(id,start,ende) VALUES(99,cast('2013-01-01' AS DATETIME),cast('2013-30-07' AS DATETIME));

WITH test(id,start,ende) AS (
SELECT    id,
        start,
        ende
FROM    tabelle t1
WHERE NOT EXISTS (    SELECT    1
                    FROM    tabelle
                    WHERE    tabelle.id = t1.id
                    AND        tabelle.ende = t1.start - 1 )
UNION ALL
SELECT    test.id,
        test.start,
        t2.ende
FROM    tabelle t2
JOIN    test
ON        t2.id = test.id
AND        t2.start = test.ende + 1
)
SELECT    id,
        start,
        max(ende) AS ende
FROM    test
GROUP BY id,start

 

[Hony%]

"Spaß mit CTE" und kann für Verwirrung sorgen

Spaß ist das richtige Wort. Denn Spaß machen CTE eigentlich immer.

Für Verwirrung sorgt der Code häufig nur deshalb, weil nicht sofort ersichtlich ist, dass diese CTE rekursiv arbeitet. Bei MS-SQL wurde auf dieses Schlüsselwort leider verzichtet. Allerdings sind CTE mit UNION ALL immer rekursiv.

Vereinfacht ausgedrückt erzeugst du mit dem SQL vor UNION ALL eine Menge auf die das SQL danach solange angewendet wird bis sich eine Abbruchbedingung erreicht wurde oder sich die Menge nicht mehr ändert. Erst danach wird dann der SELECT nach dem WITH-Block ausgeführt um die gerade erzeugte Menge nach Wunsch anzuzeigen.