SQL Hacks angrenzende Daten gruppieren
Jeder kennt die einfach anwendbare Funktion GROUP BY oder die Anweisung DISTINCT hinter dem SELECT einer SQL Query. Allerdings was macht man, wenn man nicht alle Daten auf eine oder mehrere Kategorien gruppieren möchte, sondern dies nur für die angrenzenden Daten gleichen Typs machen möchte? Das möchte ich in diesem Beitrag etwas näher erklären.
Dieser Beitrag ist aus einem aktuellen Projekt von mir entstanden. Dabei war die Kundenanforderung zeitliche Daten zu gruppieren. Dies könnten z.B. Sales Buchungen sein die nach einem Kundensegment gruppiert werden sollen. Allerdings möchte man nicht einfach nur Kundengruppen auf gruppieren. Sondern man sollte dies auch nach angrenzenden zeitlichen Buchungen gruppieren. Wenn ihr Interesse an weiteren SQL Themen habt und wissen wollt wie man z.B. Conditional Joins optimiert dann schaut euch doch diesen Beitrag von mir an. Ich werde auch nicht auf die Erklärung von Select oder FROM Befehlen weiter angehen. Wenn ihr also noch die Grundlagen erklärt bekommen müsst empfehle ich euch vorher ein paar Tutorials durch zu lesen. Ich würde empfehlen auf der Seite von W3Schools die entsprechenden Tutorials durch zu gehen.
Nehmen wir an, wir haben eine Tabelle mit unseren Sales Buchungen je Kundensegment. Dabei wird jede Buchung mit einem Timestamp versehen und der Umsatzgröße wie in dem u.g. Beispiel.
Beschreibung der Daten
Wir haben in unseren Daten folgende Spalten mit den möglichen Werten in einer Tabelle mit dem Namen „Buchungsliste1“:
Bezeichnung | Datentyp | Beschreibung / Inhalt |
---|---|---|
Timestamp | DateTime | Enthält den Zeitpunkt der Buchung |
Kundensegment | Text | Enthält die Bezeichnung des Kundensegments. Hier in den Ausprägungen Youngster und Oldies |
Wert | Integer | Enthält den Euro Wert der jeweiligen Buchung |
Wenn ich einen Select auf die Daten ausführe würden die in etwa ( sortiert nach Timestamp) folgendermaßen aussehen:
So, diese möchte ich jetzt nach Kundensegment gruppieren. Allerdings nur für die Daten im gleichen Kundensegment und welche die zusammenhängend gebucht worden sind. Die normale Group By Funktion würde allerdings nur 2 Zeilen ausgeben und mir die vorhandenen Kundensegmente mit der Gesamtzahl der Buchungen angeben. Die einzelnen Buchungsgruppen lassen sich leider nicht so einfach herausfiltern.
SELECT Kundensegment, SUM(Wert) FROM Buchungen1 GROUP By Kundensegment
Allerdings kann man mit Sub Queries, also Unterabfragen den gewünschten Output erzielen.
Beschreibung der Ziellösung
Um zu verstehen wie das Ergebnis aussehen soll, habe ich bereits vorab den Output der Lösung hier abgebildet. Danach werde ich erläutern wie genau ich dahin gekommen bin.
Als Ergebnis Output wird also eine Tabelle mit 6 Spalten erwartet.
- Kundensegment: Enthält die Kundensegmente Oldies und Youngster
- GrpId: Enthält für jede Gruppen zusammenhängender Buchungen die Gruppen ID
- Wert: Der aufsummierte Wert der Buchungsgruppe
- BuchungsStart: Dieses Feld enthält das Datum der ersten Buchung der jeweiligen Gruppe
- Buchungsende: Das Datum der letzten Buchung der jeweiligen Gruppe
- AnzBuchungen: Die Anzahl der einzelnen Buchungen in der Buchungsgruppe
Der SQL Code für diese Abfrage als ganzes sieht dabei wie folgt aus:
SELECT D.Kundensegment , D.GrpId , SUM(D.Wert) AS Wert , MIN(D.Timestamp) AS BuchungsStart , MAX(D.Timestamp) AS BuchungsEnde , COUNT(D.Kundensegment) AS AnzBuchungen FROM ( SELECT C.Kundensegment , C.Timestamp , C.Wert , C.PrevKundensegment , C.GrpStart , SUM(C.GrpStart) OVER (ORDER BY C.Timestamp ASC) AS GrpId FROM ( SELECT B.Kundensegment , B.Timestamp , B.Wert , B.PrevKundensegment , CASE WHEN B.PrevKundensegment Is NULL THEN 1 WHEN B.PrevKundensegment <> B.Kundensegment THEN 1 ELSE 0 END AS GrpStart FROM ( SELECT A.Kundensegment , A.Timestamp , A.Wert , LAG(Kundensegment) OVER (ORDER BY A.Timestamp ASC) AS PrevKundensegment FROM ( SELECT Kundensegment , Timestamp , Wert FROM Buchungen1 ) AS A ) AS B ) AS C ) AS D GROUP BY D.Kundensegment , D.GrpId
Wie man sieht werden hierfür 4 Sub Queries benötigt. Was diese im Einzelnen machen werde ich im folgenden Abschnitt näher erläutern.
Erklärung der einzelnen Sub Queries / Unterabfragen
Wir werden uns nun von innen nach außen arbeiten und dabei Stück für Stück die einzelnen Abfragen näher erklären. Die erste Abfrage ist dabei noch ziemlich simpel
SELECT Kundensegment , Timestamp , Wert FROM Buchungen1
Diese erste Abfrage holt uns einfach erstmal die Spalten Kundensegment, Timestamp und Wert aus der Tabelle Buchungen. Die zweite ist bereits interessanter, in dieser Abfrage werden die Ergebnisse der ersten Abfrage als Unterabfrage aufgerufen. Diese Unterabfrage kriegt das Alias „A“. Zusätzlich wird nun mit einer Window Function gearbeitet. Window Funktionen erzeugen eine Art Fenster und erlaubt z.B. den Output in diesem Fenster in die Ursprüngliche Analyse einzubauen.
SELECT A.Kundensegment , A.Timestamp , A.Wert , LAG(Kundensegment) OVER (ORDER BY A.Timestamp ASC) AS PrevKundensegment FROM ( SELECT Kundensegment , Timestamp , Wert FROM Buchungen1 ) AS A
So kann ich mit der Funktion „LAG(Feldname) OVER (ORDER BY Feldname ASC)“ mir den Wert aus der Spalte Kundensegment der vorherigen Zeile ausgeben lassen und wenn ich die Datensätze nach dem Timestamp sortiert habe. Das klingt kompliziert? Eventuell wird es deutlicher wenn wir uns das Ergebnis dieser Abfrage ansehen.
Hier sieht man nun, dass in „PrevKundensegment“ der Wert aus der vorherigen Zeile in der Spalte „Kundensegment“ steht.
Mit der dritten Abfrage können wir nun ermitteln, ob die Aktuelle Zeile der Startpunkt einer zusammenhängenden Gruppe ist, oder nicht. Dabei wird die nächste Abfrage nun als Unterabfrage „B“ abgerufen. Wir erzeugen nun noch ein neues Feld „GrpStart“. Wenn die Zeile den Beginn einer Gruppe darstellt wird hier 1 stehen und sonst eine 0. Dazu prüfen wir mit einem Case When Ausdruck ab ob in PrevKundensegment Null steht (Der allererste Eintrag in der Abfrage) oder ob sich PrevKundensegment und Kundensegment von einander unterscheiden. Dann haben wir ebenfalls den Start einer Gruppe. Ansonsten soll 0 in das Feld geschrieben werden.
SELECT B.Kundensegment , B.Timestamp , B.Wert , B.PrevKundensegment , CASE WHEN B.PrevKundensegment Is NULL THEN 1 WHEN B.PrevKundensegment <> B.Kundensegment THEN 1 ELSE 0 END AS GrpStart FROM ( SELECT A.Kundensegment , A.Timestamp , A.Wert , LAG(Kundensegment) OVER (ORDER BY A.Timestamp ASC) AS PrevKundensegment FROM ( SELECT Kundensegment , Timestamp , Wert FROM Buchungen1 ) AS A ) AS B
Hier sieht man, dass die erste Zeile in der Spalte GrpStart eine 1 enthält. die Folge Zeile gehört zur selben Gruppe und hat deshalb in GrpStart eine 0 stehen. Die Folgezeile gehört nun zu Oldies und ist dementsprechend mit 1 Gekennzeichnet.
Die 4. Abfrage erzeugt nun noch eine Spalte. Hier wird nun eine mithilfe der Summen Funktion und der Window Funktion eine kumulierte Summe aus der Spalte GrpStart erzeugt. Da jeder Gruppenbeginn in GrpStart eine 1 stehen hat und eine kumulierte Summe so nun für jede neue Gruppe also einen hochzieht können wir damit unsere finalen Gruppen bilden.
SELECT C.Kundensegment , C.Timestamp , C.Wert , C.PrevKundensegment , C.GrpStart , SUM(C.GrpStart) OVER (ORDER BY C.Timestamp ASC) AS GrpId FROM ( SELECT B.Kundensegment , B.Timestamp , B.Wert , B.PrevKundensegment , CASE WHEN B.PrevKundensegment Is NULL THEN 1 WHEN B.PrevKundensegment <> B.Kundensegment THEN 1 ELSE 0 END AS GrpStart FROM ( SELECT A.Kundensegment , A.Timestamp , A.Wert , LAG(Kundensegment) OVER (ORDER BY A.Timestamp ASC) AS PrevKundensegment FROM ( SELECT Kundensegment , Timestamp , Wert FROM Buchungen1 ) AS A ) AS B ) AS C
Wir sind also nun an einem Punkt wo wir für jede zusammenhängende Gruppe bereits eine eigene Gruppen ID erzeugt haben von 1 bis 4. Und damit können wir nun unsere Finale Query erzeugen und mit MIN(Timestamp) den ersten bzw. Max(Timestamp) den letzten Timestamp einer jeden herausfinden. Die Count() Funktion zählt die Datensätze in jeder Gruppe. Am ende des Selects kommt noch die Group By Funktion zum Einsatz und schon haben wir unseren Finalen Output.
SELECT D.Kundensegment , D.GrpId , SUM(D.Wert) AS Wert , MIN(D.Timestamp) AS BuchungsStart , MAX(D.Timestamp) AS BuchungsEnde , COUNT(D.Kundensegment) AS AnzBuchungen FROM ( SELECT C.Kundensegment , C.Timestamp , C.Wert , C.PrevKundensegment , C.GrpStart , SUM(C.GrpStart) OVER (ORDER BY C.Timestamp ASC) AS GrpId FROM ( SELECT B.Kundensegment , B.Timestamp , B.Wert , B.PrevKundensegment , CASE WHEN B.PrevKundensegment Is NULL THEN 1 WHEN B.PrevKundensegment <> B.Kundensegment THEN 1 ELSE 0 END AS GrpStart FROM ( SELECT A.Kundensegment , A.Timestamp , A.Wert , LAG(Kundensegment) OVER (ORDER BY A.Timestamp ASC) AS PrevKundensegment FROM ( SELECT Kundensegment , Timestamp , Wert FROM Buchungen1 ) AS A ) AS B ) AS C ) AS D GROUP BY D.Kundensegment , D.GrpId
Dies ist natürlich nur ein einzelnes Beispiel. Aber falls ihr mal einen ähnlichen Use Case habt könnt ihr so diese angrenzenden Datensätze gruppieren. Vor allem Dank der Lag und Sum Window Funktionen ist es in SQL Möglich diese Dinge zu realisieren.