How to: SQL rekursive Abfragen erstellen

Die Frage wie man in SQL rekursive Abfragen erstellen kann kommt zwar nicht besonders häufig vor. Aber vor kurzem hatte ich tatsächlich eine Projektanfrage, genau so etwas zu realisieren. Rekursive Abfragen sind dabei Abfragen die sich selbst wieder aufrufen. Was das genau ist, wozu das verwendet werden kann und ganz wichtig, wie das genau funktioniert werden wir in dem Artikel untersuchen.

Also für diesen Artikel werden wir die folgenden Punkte erklären.

  • Was bedeutet Rekursiv?
  • Wozu kann ich rekursive Funktionen oder Abfragen verwenden?
  • Wie kann ich in SQL eine Rekursive Abfrage erstellen?



Was bedeutet Rekursiv?

Rekursiv kann auch als auf sich selbst bezogen oder rückführend bezeichnet werden. In Der Programmierung geht es also um Abfragen oder Funktionen die sich selbst wieder aufrufen bzw. auf sich selbst bezogen sind. Das heißt also, dass beim ausführen einer Funktion diese sich selber wieder aufruft und das so lange bis eine Abbruch Bedingung erreicht ist.

Wozu kann ich rekursive Funktionen oder Abfragen verwenden?



Die Einsatzmöglichkeiten von Rekursiven Funktionen sind sehr breit. Einer der häufigsten ist zum Beispiel für die Implementierung von verschiedenen Sortierfunktionen wie Quicksort oder bei der Suche nach bestimmten Daten. Viele nutzen als Beispiel die Fakultätsfunktion. Ein sehr praxisorientiertes Beispiel für den Einsatz einer rekursiven Funktion ist die Suche nach den verschiedenen Bestandteilen eines Produktes über eine variierende Anzahl an Fertigungsstufen hinweg.

Also ich habe ein Produkt das aus verschiedenen Modulen zusammengebaut wird. Diese Module wiederum werden aus weiteren Material und Modulen zusammengebaut. Daraus entsteht eine Fertigungstiefe von N-Ebenen. Diese möchte ich nun durchsuchen und alle Objekte die ich benötige auflisten. Wenn ich die Anzahl der Fertigungsebenen nicht kenne, eignet sich eine rekursive Funktion besonders gut, um diese Informationen aus den Daten zu extrahieren. Genau so einen Use Case hatte ich letztens bei einem Kunden von mir. Dieses Beispiel werden wir im folgenden diskutieren.

Wie kann ich in SQL rekursive Abfragen erstellen?



Manchmal kommt es vor, dass man für die Lösung nicht eine Python Umgebung oder ähnliches zur Verfügung hat und die rekursive Abfrage in SQL durchführen muss. Dies war in dem letzten Projekt von mir der Fall. Der Kunde brauchte eine Übersicht zu einem Produkt aus dem eigenen ERP System. Diese Übersicht sollte direkt auf der Datenbank ausgeführt werden und dann in der Nutzeroberfläche angezeigt werden.

Das Ergebnis der SQL Abfrage sollte wie folgt aussehen.

Die erste Spalte enthält die Artikelnummer des fertigen Produkts. die Nächste Spalte enthält die Bezeichnung des aktuellen Produkts / Produktteils. Unter Stueckname sind die untergeordneten Produktgruppen, der jeweils untergeordneten Fertigungsebene enthalten. Unter StckUnterArtikelNummer ist die Artikelnummer eingefügt. Diese ist nur befüllt, wenn es sich um eine Produkt-Teil-Gruppe handelt, die wiederum aus mehreren Materialien besteht. Die letzte Spalte ist die Spalte Ebene. Diese gibt die Fertigungsebene an auf der sich diese Produktgruppe oder das Material befindet.

Als nächstes schauen wir uns den gesamten Code für diese rekursive Abfrage an und werden im Anschluss die Teile auseinander nehmen und erklären.

/*********
Starting the WITH Statement
*********/
WITH recStat ( InterneArtikelNummer  ,Artikelbezeichnung,Stueckname, StckUnterArtikelNummer, EBENE) AS
(	
    /*********
    Creating the baseline on Ebene /Lvl 1
    *********/	
    SELECT 
          A.InterneArtikelNummer
        , A.Artikelbezeichnung
        , S.Stueckname
        , S.StckUnterArtikelNummer
        , 1 AS EBENE
    FROM dbo.Artikel AS A
    INNER JOIN dbo.stueckliste AS S ON 1=1
        AND A.InterneArtikelNummer = S.InterneArtikelNummer
    WHERE 1=1 
        AND A.Artikelbezeichnung = 'Tisch'

    UNION ALL
    
    /*********
    Building the recursive query on the data
    *********/
    SELECT 
          B.InterneArtikelNummer
        , recq.Artikelbezeichnung
        , recQ.Stueckname
        , recQ.StckUnterArtikelNummer
        , B.EBENE + 1 AS EBENE
    FROM (
        SELECT A.InterneArtikelNummer, A.Artikelbezeichnung, S.Stueckname , S.StckUnterArtikelNummer
        FROM dbo.Artikel AS A
        INNER JOIN dbo.stueckliste AS S ON 1=1
            AND A.InterneArtikelNummer = S.InterneArtikelNummer
        ) AS recQ
    INNER JOIN recStat AS B ON recQ.InterneArtikelNummer = B.StckUnterArtikelNummer
)


SELECT * FROM recStat;

Der SQL Code besteht aus zwei bis drei Teilen.

  1. Das With Statement hier drin wird die rekursive Funktion definiert.
  2. Der Select Befehl. Dieser bildet die erste Abfrage Ebene und durch das Union Statement ruft sich die Abfrage immer wieder selber auf.
  3. Der eigentliche Select Aufruf der recStat Abfrage. Hiermit wird das With Statement tatsächlich ausgeführt.



SELECT * FROM recStat;

 

Während der dritte Teil selbst erklärend sein sollte, man ruft einfach in einem SELECT Befehl die Abfrage „recStat“ auf, schauen wir uns den WITH Part einmal genauer an. Ein WITH Statement ist an sich einfach eine Sub Query Definition, mit der eine Sub Query Definiert werden kann und später wieder aufgerufen wird.

WITH recStat ( InterneArtikelNummer  ,Artikelbezeichnung, Stueckname, StckUnterArtikelNummer, EBENE) AS
(	
-- Hier die Abfrage schaffen
)

In der ersten Zeile des WITH Statements stehen die Output Felder dieser so erzeugten Subquery Abfrage. Das sind auch die Felder die wir im Output nachher tatsächlich sehen. Wie bei einer Sub Query üblich müssen diese Felder auch in dem WITH Statement vorkommen bzw. verfügbar sein, sonst würde die Abfrage auf Fehler laufen. In unserem Fall müssen also für die Subquery „recStat“ folgende Felder im Bauch vorhanden sein:

  • InterneArtikelNummer
  • Artikelbezeichnung
  • Stueckname
  • StckUnterArtikelNummer
  • EBENE

Schauen wir uns nun den SELECT Befehl innerhalb des WITH Statements an. Dieser verursacht den rekursiven Aufruf selbst. Aber zuerst ein Blick auf den Teil oberhalb des UNION Befehls.

SELECT 
      A.InterneArtikelNummer
    , A.Artikelbezeichnung
    , S.Stueckname
    , S.StckUnterArtikelNummer
    , 1 AS EBENE
FROM dbo.Artikel AS A
INNER JOIN dbo.stueckliste AS S ON 1=1
    AND A.InterneArtikelNummer = S.InterneArtikelNummer
WHERE 1=1 
    AND A.Artikelbezeichnung = 'Tisch'

Sieht soweit ganz normal aus. Aus der Tabelle „Artikel“ werden die Artikelinformationen wie Artikelnummer und Artikelbezeichnung geholt . Aus der Tabelle „stueckliste“ kommen nun die Module und Materialien die für die Herstellung benötigt werden. Die letzte Spalte enthält einfach nur eine 1 und heißt EBENE. Da wir hier uns die Basis Daten holen macht das auch Sinn. Damit haben wir die erste Fertigungsebene definiert. Aber wir wollen ja noch die weiteren N Fertigungsebenen finden. Dafür müssen wir uns den zweiten Teil unterhalb des UNION Statements anschauen.



 

SELECT 
      B.InterneArtikelNummer
    , recq.Artikelbezeichnung
    , recQ.Stueckname
    , recQ.StckUnterArtikelNummer
    , B.EBENE + 1 AS EBENE
FROM (
    SELECT A.InterneArtikelNummer, A.Artikelbezeichnung, S.Stueckname , S.StckUnterArtikelNummer
    FROM dbo.Artikel AS A
    INNER JOIN dbo.stueckliste AS S ON 1=1
        AND A.InterneArtikelNummer = S.InterneArtikelNummer
    ) AS recQ
INNER JOIN recStat AS B ON recQ.InterneArtikelNummer = B.StckUnterArtikelNummer

Diese besteht ebenfalls aus zwei Teilen. Der äußere Teil hat als Ergebnis ebenfalls die 5 Spalten. Allerdings gibt es ein paar Änderungen. die Felder Artikelbezeichnung, Stueckname und StckUnterartikelNummer kommen  nun aus einer Unter-Abfrage „recQ“. Diese wiederum sieht genau so aus wie die erste Query vor dem Union. Nur die Spalte „EBENE“ fehlt. Dieser Diese Unter-Abfrage ist über ein INNER JOIN mit recStat verbunden.

Aber Moment?! Ist das nicht der Name den wir für unser WITH Statement gegeben haben??

KORREKT ! genau hier ruft sich die Funktion nun immer wieder selbst auf und zwar über das Ergebnis aus dem ersten Abfrageabteil und sucht für alle StckUnterartikelNummern die hier gefunden worden, alle Artikelinformationen auf und hängt die über die Union an das Ergebnis ran, bzw. ruft sich wieder selbst auf. Dies passiert so lange bis alle Verbindungen gefunden sind. Wenn man eine Abbruchbedingung definieren wollte könnte man das über ein WHERE Statement integrieren. Um zum Beispiel nur bis zu einer bestimmten Ebene zu suchen, da diese mit jedem rekursiven Abfrage um 1 erhöht wird. Dadurch das wir die „InterneArtikelNummer“ immer aus der vorhergehenden Iteration übernehmen, bleibt diese über alle Ebenen gleich und bildet deshalb die Klammer um alle Iterationen.

Fazit

Ich hoffe ich konnte euch die Idee von rekursiven Abfragen in SQL aufzeigen. Im Prinzip läuft es über die Definition einer Unter Abfrage mithilfe eines WITH Statements, welche sich immer wieder selbst aufruft und die Informationen zusammenfügt.