Diesmal geht es um ein etwas komplexeres Thema, das ich „Temporale Verdichtung“ genannt habe. Gemeint ist damit die Zusammenfassung verschiedener überlappender oder angrenzender Zeitperioden, um die umfassende Zeitperiode zu ermitteln.
Die Zeiträume oberhalb der Time-Linie stellen die Ausgangsperioden dar. Unterhalb in grün ist dann das erwartete Ergebnis, bei dem alle Überlappungen oder angrenzende Perioden zusammengefasst wurden. Lücken in der zeitlichen Abdeckung bleiben erhalten.
Lösungsansätze
Wie für so viele Problemstellungen gibt es auch hier mehrere Lösungsansätze, die zum Ziel führen. Auf der Db2 User Group (DeDUG) hatten wir Lösungsansätze mit Join gesehen, die die Daten zuerst erweitert und dann auf die relevanten Sätze heruntergefiltert haben – durchaus anspruchsvolle Lösungen.
Im Rahmen eines Kundenprojektes habe ich diese Situation mit Hilfe von OLAP-Funktionen gelöst und diese möchte ich heute zeigen.
Dafür schaffen wir zuerst mal die bereits oben angedeutete Testsituation:
Die Beispieldaten & das SQL stellen wir gerne als Code zum Download zur Verfügung, da es hier im Blog besser als Bild eingebunden wurde.
Folgendes SQL löst das Problem
Im Weiteren werden dann die einzelnen Bausteine im Detail erläutert.
In der ersten Common Table Expression (CTE) werden die Daten um eine Zeilennummerierung (rownumber) und über die LAG-Funktion mit dem vorhergehenden Ende-Zeitpunkt angereichert.
Die zweite CTE cte_temporal_logic enthält die eigentliche Logik, die mittels CASE-Statement die Spalte „FLAG“ auf NULL setzt, wenn eine Überschneidung oder ein direkter Anschluss der nächsten Periode erkannt wurde, ansonsten wird die Zeilennummer übernommen.
Dies stellt sicher, dass nach jeder Lücke eine neue und eindeutige Intervall-Nummer vergeben wird.
Der nächste Schritt füllt alle NULL-Werte aus der Spalte FLAG mittels der last_value OLAP-Funktion auf und erzeugt so das „FLAG_FILLED“. Dieses wird als Gruppierungskriterium im letzten Schritt – dem finalen Select – benötigt.
Der minimale START-Zeitpunkt und der maximale ENDE-Zeitpunkt der Perioden, die das identische FLAG_FILLED haben, bilden das gewünschte Ergebnis:
Einsatzmöglichkeiten
Schön – aber wann braucht man das eigentlich?
Solche Lösungen entstehen nicht aus dem Grund „weil man es kann“, sondern haben immer einen Business-Hintergrund – oder mehrere.
In diesem Fall gab es zwei unterschiedliche Anforderungen, die diese Lösung nötig machten:
An dieser Stelle sei auch nochmal auf meinen Blog-Artikel zu Temporal Tables mit Business Time verwiesen, der ebenfalls sehr gut zu diesem Thema passt und auch mit dieser Lösung kombiniert werden kann.
Mehr Infos zu den OLAP-Funktionen finden sie in diesem Blog-Beitrag.
Schauen Sie einfach mal rein.
Ihr Michael Tiefenbacher
Business and Customer Success Manager & Principal Consultant
Mehr zu unserem Beratungsangebot finden Sie auch auf unserer Datenbank Webseite.
Die Zeiträume oberhalb der Time-Linie stellen die Ausgangsperioden dar. Unterhalb in grün ist dann das erwartete Ergebnis, bei dem alle Überlappungen oder angrenzende Perioden zusammengefasst wurden. Lücken in der zeitlichen Abdeckung bleiben erhalten.
Lösungsansätze
Wie für so viele Problemstellungen gibt es auch hier mehrere Lösungsansätze, die zum Ziel führen. Auf der Db2 User Group (DeDUG) hatten wir Lösungsansätze mit Join gesehen, die die Daten zuerst erweitert und dann auf die relevanten Sätze heruntergefiltert haben – durchaus anspruchsvolle Lösungen.
Im Rahmen eines Kundenprojektes habe ich diese Situation mit Hilfe von OLAP-Funktionen gelöst und diese möchte ich heute zeigen.
Dafür schaffen wir zuerst mal die bereits oben angedeutete Testsituation:
Die Beispieldaten & das SQL stellen wir gerne als Code zum Download zur Verfügung, da es hier im Blog besser als Bild eingebunden wurde.
Folgendes SQL löst das Problem
Im Weiteren werden dann die einzelnen Bausteine im Detail erläutert.
In der ersten Common Table Expression (CTE) werden die Daten um eine Zeilennummerierung (rownumber) und über die LAG-Funktion mit dem vorhergehenden Ende-Zeitpunkt angereichert.
Die zweite CTE cte_temporal_logic enthält die eigentliche Logik, die mittels CASE-Statement die Spalte „FLAG“ auf NULL setzt, wenn eine Überschneidung oder ein direkter Anschluss der nächsten Periode erkannt wurde, ansonsten wird die Zeilennummer übernommen.
Dies stellt sicher, dass nach jeder Lücke eine neue und eindeutige Intervall-Nummer vergeben wird.
Der nächste Schritt füllt alle NULL-Werte aus der Spalte FLAG mittels der last_value OLAP-Funktion auf und erzeugt so das „FLAG_FILLED“. Dieses wird als Gruppierungskriterium im letzten Schritt – dem finalen Select – benötigt.
Der minimale START-Zeitpunkt und der maximale ENDE-Zeitpunkt der Perioden, die das identische FLAG_FILLED haben, bilden das gewünschte Ergebnis:
Einsatzmöglichkeiten
Schön – aber wann braucht man das eigentlich?
Solche Lösungen entstehen nicht aus dem Grund „weil man es kann“, sondern haben immer einen Business-Hintergrund – oder mehrere.
In diesem Fall gab es zwei unterschiedliche Anforderungen, die diese Lösung nötig machten:
- Aus einer historisierten Datenquelle wird eine Dimension erstellt, die sich ebenfalls über die Zeit ändern kann – diese bezeichnet man als SCD-2 Dimension. Für die Dimension werden oft weniger Attribute benötigt als in der eigentlichen historisierten Quelle vorhanden sind. Deshalb werden zeitliche Schnitte vorhanden sein, die eine Zeitscheibe (Periode) erzeugen, ohne dass sich die Attribute, die für die Dimension benötigt werden, geändert haben. Dadurch entstehen – aus Sicht der Dimension – viele Zeitscheiben, die sich nicht unterscheiden. Das ist zwar fachlich nicht falsch, führt aber bei Tests und Verarbeitung zu erhöhtem Aufwand.
Mit der temporalen Verdichtung kann man dies wieder minimieren. - In einem Telefonie-System werden granulare Daten und ein eingestellter Arbeitsmodus geliefert. Dieser Arbeitsmodus wird in jedem Satz mitgeliefert und kann sich ändern. Um für jeden Arbeitsmodus nur eine Zeitscheibe zu erhalten, kann der obige Lösungsansatz ebenfalls verwendet werden.
An dieser Stelle sei auch nochmal auf meinen Blog-Artikel zu Temporal Tables mit Business Time verwiesen, der ebenfalls sehr gut zu diesem Thema passt und auch mit dieser Lösung kombiniert werden kann.
Mehr Infos zu den OLAP-Funktionen finden sie in diesem Blog-Beitrag.
Schauen Sie einfach mal rein.
Ihr Michael Tiefenbacher
Business and Customer Success Manager & Principal Consultant
Mehr zu unserem Beratungsangebot finden Sie auch auf unserer Datenbank Webseite.
