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:

SQL Select von Buchungsliste

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

SQL Output Group by

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.

SQL Grouping

Als Ergebnis Output wird also eine Tabelle mit 6 Spalten erwartet.

  1. Kundensegment: Enthält die Kundensegmente Oldies und Youngster
  2. GrpId: Enthält für jede Gruppen zusammenhängender Buchungen die Gruppen ID
  3. Wert: Der aufsummierte Wert der Buchungsgruppe
  4. BuchungsStart: Dieses Feld enthält das Datum der ersten Buchung der jeweiligen Gruppe
  5. Buchungsende: Das Datum der letzten Buchung der jeweiligen Gruppe
  6. 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.

SQL Output 2

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

SQL Query Output 3

 

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

SQL Query Output 4

 

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.