Hierarchische Daten und wie man sie in SQL abfragt

Wie erkennt man hierarchische Daten und wie fragt man sie ab? Zwei häufige SQL-Fragen werden hier beantwortet.

Die Abfrage hierarchischer Daten ist eine recht häufige Situation, wenn Sie mit SQL und relationalen Datenbanken arbeiten. Nicht, dass hierarchische Daten etwas Mystisches und Seltenes wären, im Gegenteil, sie sind überall zu finden. Warum sind hierarchische Daten also so schwierig, wenn es um relationale Datenbanken geht? Die Schwierigkeiten liegen vor allem darin, hierarchische Daten auf die Prinzipien relationaler Datenbanken zu übertragen.

Bei der Arbeit mit hierarchischen Daten besteht der erste Schritt darin, sie zu erkennen. Ich werde zunächst definieren, was hierarchische Daten sind, und Ihnen einige alltägliche Beispiele dafür geben. Dann erkläre ich Ihnen, wie hierarchische Daten normalerweise in Datenbanken gespeichert werden. Schließlich lernen Sie, wie Sie solche Daten mit SQL abfragen können.

Was Sie in diesem Artikel lernen, können Sie in unserem Kurs Rekursive Abfragen üben.

Was sind hierarchische Daten?

Hierarchische Daten sind eine bestimmte Art von Daten, die durch eine hierarchische Beziehung zwischen den Datensätzen gekennzeichnet sind. Woran denken Sie, wenn Sie an Hierarchie denken? Wahrscheinlich an verschiedene Ebenen: etwas befindet sich über, unter oder auf der gleichen Ebene wie etwas anderes. In relationalen Datenbanken wird eine hierarchische Beziehung auch als Eltern-Kind-Beziehung bezeichnet. Das bedeutet, dass die untergeordneten Daten nur ein Elternteil haben, während die übergeordneten Daten ein oder mehrere "Kinder" haben.

Man sagt, dass hierarchische Daten an ihrer baumartigen Struktur zu erkennen sind. Warum das so ist, werden wir gleich sehen, wenn wir uns gängige Beispiele für hierarchische Daten ansehen.

Hierarchische Daten - Beispiele

Mitarbeiterhierarchien

Eines der typischen Beispiele zur Erläuterung hierarchischer Daten sind Mitarbeiterhierarchien. Sie werden durch Organigramme wie dieses dargestellt:

Wie Sie sehen können, ist diese Struktur an der Spitze schmal und wird weiter unten breiter - ähnlich wie ein Tannenbaum. An der Spitze steht der Präsident. Ihm untergeordnet sind zwei Vorstandsmitglieder. Diese beiden Vorstandsmitglieder haben auch ihre Untergebenen. Im Fall von Jacqueline Managerovicz sind dies Diane Drinkalot, die Personalleiterin, und Rashawn Mangarello, der Leiter der Buchhaltung. Aber auch sie sind der Präsidentin unterstellt, wenn auch nur indirekt.

Die anderen direkten Untergebenen des Vorstandsmitglieds sind Tony Workaholio, der Verkaufsleiter, und Cassandra Ninetofiver, die IT-Leiterin. Paul Bossenheim, der Präsident des Unternehmens, ist auch ihr indirekter Vorgesetzter.

Stammbaum

Ein Stammbaum ist ein weiteres gängiges Beispiel für hierarchische Daten. Seine Struktur ermöglicht es uns, Vorfahren und deren Nachkommen zu finden. Ein Stammbaum könnte etwa so aussehen:

In diesem Beispiel heiratete Mike Strongbow Victoria Stromboli. Sie hatten zwei Kinder, Florence und Claudio. Florence hatte ein Kind (Valerie), und Claudio hatte zwei Kinder (Art und Michelle). Alle drei sind Kinder ihrer Eltern, aber auch Enkelkinder von Mike und Victoria. Mike und Victoria sind auch Urgroßeltern; ihre Enkelin hatte zwei Kinder, Judy und James.

Getränkekarte

Wenn Sie in eine Bar gehen, sind Sie wahrscheinlich schon einmal auf eine hierarchische Struktur gestoßen. Ich spreche von derjenigen, die Sie in der Regel sofort ansehen, nachdem Sie Platz genommen haben: die Getränkekarte, oder Karte, oder wie immer Sie sie nennen. Wenn Sie zum Beispiel die Panthelya Bar besuchen, werden Sie feststellen, dass es sich um eine sehr primitive Bar handelt, die nur Biere und Weine anbietet.

Die Bar bietet zwei Biersorten an: Ale und Weizenbier. Aus jeder Kategorie können Sie viele Biere bestellen. Die Weinkategorie ist in Rot- und Weißweine unterteilt, wobei es in jeder Kategorie vier Weine gibt.

Taxonomie der lebenden Dinge

Auch Sie, die Person, die diesen Artikel liest, sind ein Datensatz in hierarchischen Daten. Als Mensch haben Sie eine bestimmte Position in der Hierarchie der Taxonomie der Lebewesen.

Das obige Diagramm zeigt die Hierarchie für den Menschen, oder Homo sapiens. Es wäre zu kompliziert, die gesamte Taxonomie der Lebewesen darzustellen. Das Prinzip ist jedoch das gleiche wie in allen obigen Diagrammen. Alle Menschen gehören zur Spezies Sapiens, die Teil der Gattung Homo ist. Diese Gattung ist Teil der Familie der Hominidae, einer Familie in der Ordnung der Primaten. Die Primaten gehören zur Klasse der Säugetiere (Mammalia), die dem Stamm, dem Königreich und schließlich dem Bereich untergeordnet ist.

Ordner auf Ihrem Computer

Wenn Sie diesen Artikel lesen, ist die Wahrscheinlichkeit groß, dass Sie dies auf Ihrem Computer tun. Daher ist es durchaus möglich, dass die Ordner auf Ihrem Computer etwa so aussehen:

Alle Ihre Ordner in diesem Beispiel (Lernen und Freiberuflichkeit) befinden sich auf Ihrem Laufwerk D:. Der Ordner Lernen hat zwei Unterordner: SQL, wo Sie alle interessanten SQL-bezogenen Artikel wie diesen hier ablegen, und Python. Ihr Freelancing-Ordner enthält drei Unterordner: Aufträge, Rechnungen und Andere Dokumente.

Nachdem Sie nun wissen, wie Sie hierarchische Daten erkennen können, sehen wir uns nun an, wie sie in der Datenbank gespeichert werden und wie man sie abfragt.

Hierarchische Daten in einer Datenbank speichern

Ein Problem mit hierarchischen Daten tritt in der Regel auf, wenn Sie versuchen, solche Daten in einer Datenbank zu speichern. Dazu müssen Sie all diese mehrstufigen Daten in ein relativ flaches Format packen: eine Tabelle. Wie konvertiert man hierarchische Daten in einfache Datenzeilen?

Um die hierarchischen Daten in einer Datenbank zu speichern, gibt es normalerweise eine Spalte, die auf dieselbe Tabelle verweist. Was bedeutet das? Am besten ist es wohl, wenn ich Ihnen ein Beispiel zeige. Die Mitarbeiterhierarchie scheint dafür sehr geeignet zu sein!

Abfrage hierarchischer Daten mit einem Self-Join

Ich zeige Ihnen, wie Sie eine Mitarbeiterhierarchie abfragen können. Nehmen wir an, wir haben eine Tabelle namens employee mit den folgenden Daten:

• employee_id - Die ID des Mitarbeiters und der Primärschlüssel (PK) der Tabelle.
• first_name - Den Vornamen des Mitarbeiters.
• last_name - Der Nachname des Mitarbeiters.
• reports_to - Die ID des direkten Vorgesetzten oder Managers dieses Mitarbeiters.

reports_to ist nichts anderes als die Spalte employee_id, die als Hilfsmittel dient, um zu zeigen, welcher Mitarbeiter welchem Mitarbeiter unterstellt ist. Wenn die ID des Mitarbeiters in der Spalte reports_to erscheint, ist dieser Mitarbeiter der Vorgesetzte von (zumindest einigen) anderen Mitarbeitern. Ich möchte Ihnen zeigen, wie das funktioniert:

Wir sehen, dass Sharon Simon der Mitarbeiterin unterstellt ist, die employee_id = 6 hat, Martina Novak. In ihrem Fall ist der reports_to Wert NULL. Das bedeutet, dass Martina Novak niemandem unterstellt ist. Daraus können wir schließen, dass sie an der Spitze der Angestelltenhierarchie steht.

Dies ist der Teil, in dem Sie die Tabelle selbst verknüpfen werden. Sie wissen nicht, was ein Self-Join ist? Sie können es leicht lernen, indem Sie diesen Artikel mit sieben Self-Join-Beispielen lesen. Erinnern Sie sich, dass ich bereits erwähnt habe, dass hierarchische Daten in einer Datenbank in der Regel eine Spalte haben, die sich auf dieselbe Tabelle bezieht. Dies ist ein solches Beispiel. Um die direkten Unterordner aus der Tabelle employeezu erhalten, müssen Sie die folgende Abfrage schreiben:

SELECT	
sub.employee_id AS subordinate_id,
sub.first_name AS subordinate_first_name,
	sub.last_name AS subordinate_last_name,
	sup.employee_id AS superior_id,
	sup.first_name AS superior_first_name,
	sup.last_name AS superior_last_name
FROM employee sub 
JOIN employee sup 
ON sub.reports_to = sup.employee_id
ORDER BY superior_id;

Diese Abfrage verknüpft die employee Tabelle mit sich selbst. Lassen Sie mich erklären, wie das funktioniert. Wenn Sie eine Tabelle mit sich selbst verknüpfen, müssen Sie eindeutige Aliasnamen verwenden, damit SQL weiß, welche Daten aus welcher Tabelle stammen - und damit Sie wissen, welche Daten aus welcher Tabelle stammen. In der obigen Abfrage lautet ein Tabellen-Alias sub. Das bedeutet, dass es sich um die Tabelle mit den Daten der untergeordneten Tabellen handelt. Der andere Alias lautet supalso die Tabelle mit den Daten der Vorgesetzten. Obwohl es sich um dieselbe Tabelle handelt, behandeln wir sie so, als wären es zwei verschiedene Tabellen.

Die obige Abfrage wählt also zunächst die Spalten employee_id, first_name und last_name aus der sub Tabelle. Dann entnimmt sie die gleichen Daten aus der Tabelle sup. Somit wird die Tabelle employee mit sich selbst verbunden, indem die beiden Aliase verwendet werden. Der Self-Join wird durchgeführt, wenn die Spalte reports_to der Tabelle sub gleich der Spalte employee_id aus der Tabelle sup. Die Daten werden schließlich nach der Spalte superior_id geordnet.

Hier ist das Ergebnis:

Die Tabelle zeigt, dass Kim Magnus, Sharon Simon und Vincent Trafalgar Martina Novak direkt unterstellt sind. Victor Fonseca berichtet an Patricia Rooney. Patricia Rooney wiederum ist Sharon Simon direkt unterstellt, ebenso wie James Palin.

Verwendung von Rekursive Abfragen bei tiefen hierarchischen Daten

In dem obigen Beispiel habe ich Ihnen gezeigt, wie Sie direkte Vorgesetzte/Untergebene finden können. Das bedeutet, dass Sie gelernt haben, wie man nur eine Ebene höher oder tiefer sucht. Das ist zwar sehr nützlich, aber Hierarchien können sehr tief sein und eine immense Anzahl von Ebenen haben. Bevor Sie solche Daten abfragen können, müssen Sie sich mit rekursiven Abfragen vertraut machen. Lassen Sie uns zunächst über rekursive Abfragen sprechen; dann werde ich Ihnen anhand von ein oder zwei Beispielen zeigen, wie sie funktionieren.

Was sind Rekursive Abfragen?

Wenn Sie rekursive Abfragen kennenlernen möchten, müssen Sie zunächst etwas über Common Table Expressions (CTEs) erfahren.

Ein CTE ist ein temporärer Datensatz, der von einer Abfrage zurückgegeben und dann von einer anderen Abfrage verwendet wird. Er ist temporär, weil das Ergebnis nirgendwo gespeichert wird; es existiert nur, wenn die Abfrage ausgeführt wird. CTEs können nicht-rekursiv und rekursiv sein. Ich habe bereits über (nicht rekursive) CTEs geschrieben und darüber, wann sie zu verwenden sind; weitere Informationen finden Sie in diesem Artikel.
Eine rekursive Abfrage ist eine Abfrage, die auf sich selbst verweist. Dabei gibt sie das Teilergebnis zurück und wiederholt den Vorgang, bis sie das Endergebnis liefert. Nach dieser Logik ist eine rekursive CTE eine CTE, die sich selbst referenziert.

Die rekursive CTE-Syntax

Die allgemeine rekursive CTE-Syntax sieht wie folgt aus und kann in drei Teile unterteilt werden:

WITH RECURSIVE cte_name AS (
   cte_query_definition

   UNION ALL

   cte_query_definition
   )


SELECT *
FROM cte_name;

CTEs werden auch "WITH-Abfragen" genannt. Wenn Sie sich die obige Syntax ansehen, werden Sie sehen, warum: Die CTE beginnt immer mit der Klausel WITH. Wenn Sie wollen, dass Ihr CTE rekursiv ist, müssen Sie das Wort RECURSIVE folgen lassen. Danach definieren Sie den CTE-Namen.

Dann müssen Sie die CTE-Abfragedefinition schreiben. Dieser Teil der Abfrage wird als Ankerelement bezeichnet . Er ist mit dem anderen CTE über UNION ALL"verbunden". Diese zweite CTE-Abfragedefinition wird als rekursives Element bezeichnet und verweist auf den CTE selbst.

Am Ende steht die Anweisung SELECT, die die Daten aus dem CTE abruft. Dieser Teil der Abfrage wird als Aufruf bezeichnet.

Die Syntax lernt man immer am besten, wenn man sie an einem Beispiel sieht. Hier ist also Ihr erstes rekursives CTE-Beispiel!

Abfrage der Mitarbeiterhierarchie

Ich möchte auf dem Beispiel aufbauen, in dem Sie gelernt haben, wie man die Tabelle Self-Join employee Tabelle. Jetzt verwende ich dieselbe Tabelle, aber dieses Mal verwenden wir eine rekursive Abfrage. Die Aufgabe besteht darin, den direkten und indirekten Vorgesetzten eines jeden Mitarbeiters zu finden. Diese Beziehung zwischen den Mitarbeitern wird als Pfad dargestellt, der vom Chef an der Spitze (dem Eigentümer) zu jedem Mitarbeiter in der Tabelle führt.

WITH RECURSIVE employee_hierarchy AS (
  SELECT	employee_id,
    		first_name,
    		last_name,
    		reports_to,
    		'Owner' AS path
  FROM employee
  WHERE reports_to IS NULL

  UNION ALL 
  
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.reports_to,
    employee_hierarchy.path || '->' || e.last_name
  FROM employee e, employee_hierarchy
  WHERE e.reports_to = employee_hierarchy.employee_id
)
SELECT *
FROM employee_hierarchy;

Wie Sie bereits gelernt haben, beginnen Sie das Schreiben einer rekursiven CTE mit WITH RECURSIVE. Dann benennen Sie die CTE. In diesem Fall ist es employee_hierarchy.

Das Ankerelement des CTE ist die erste Anweisung SELECT. Damit wählen Sie die Wurzel der Hierarchie aus; sie ist die Grundlage, auf der die rekursive Abfrage ihre Arbeit verrichtet und alle anderen Ebenen der Hierarchie findet. Diese Anweisung wählt alle Spalten aus der Tabelle employee. Sie fügt auch die neue Spalte pathhinzu, die den Wert 'Owner' enthält. Die Klausel WHERE bedeutet, dass dies nur für die Zeilen geschieht, in denen der Wert in der Spalte reports_to NULL ist. Warum ist das so? Wenn der Wert NULL in der Spalte reports_to steht, ist der Mitarbeiter niemandem unterstellt. Das bedeutet, dass es sich um den Eigentümer des Unternehmens handelt.

Der nächste Schritt besteht darin, das Ankerelement mit dem rekursiven Element der CTE mit UNION ALL zu "verbinden". Wichtig bei der Verwendung von UNION ALL ist, dass die SELECT Anweisungen, die Sie "verbinden", dieselbe Anzahl von Spalten haben müssen. Andernfalls wird UNION ALL nicht funktionieren.

Das rekursive Mitglied ist die zweite Anweisung SELECT. Diese Anweisung wählt wiederum alle Spalten der Tabelle aus employee. Sie übernimmt auch den Wert (der 'Owner' ist) aus dem Spaltenpfad der employee_hierarchy CTE. Sie fügt '->' hinzu, gefolgt von dem Wert der Spalte last_name aus der Tabelle employee. (Der || ist ein Verkettungsoperator, der zwei oder mehr Werte zu einem Wert zusammenfasst). Dies wird ein Pfad sein, der vom Eigentümer zu jedem Mitarbeiter führt.

Die Tabelle employee und die CTE employee_hierarchy werden wie zwei beliebige andere Tabellen verbunden. Dies geschieht, indem die Spalte reports_to der Spalte employee_id entspricht. Das rekursive Element dient als Erweiterung des Ankerelements. Das bedeutet, dass es ein Ergebnis, das bereits (durch das Ankermitglied) gefunden wurde, um neue Ergebnisse erweitert. Das rekursive Mitglied führt also alles Beschriebene aus, bis es den letzten Mitarbeiter erreicht.

Schließlich wählt der einfache Aufrufteil alle Daten aus dem CTE employee_hierarchy aus. Und, voila! Das Ergebnis ist:

Wenn Sie z. B. Victor Fonseca betrachten, können Sie sehen, dass der Weg vom Eigentümer zu ihm über Sharon Simon und Patricia Rooney führt.

Lassen Sie uns rekursive Abfragen an einem anderen Beispiel üben!

Abfrage der Ordnerhierarchie

Unternehmen haben in der Regel Netzlaufwerke, auf denen die Mitarbeiter ihre gesamte Arbeit speichern. Dies führt normalerweise zu einer sehr verzweigten Baumstruktur von Ordnern. Die Daten zu den Ordnern werden in der Tabelle folder. Ihre Spalten sind:

• id - Die ID des Ordners und der Primärschlüssel (PK) der Tabelle.
• name - Der Name des Ordners.
• subfolder_of - Der Name des Ordners eine Ebene höher.

Um den Pfad für alle Ordner zu finden, benötigen Sie die folgende Abfrage:

WITH RECURSIVE folder_hierarchy AS (
  SELECT	id,
   	 	name,
    		subfolder_of,
    		CAST (name AS text) AS path
  FROM folder
  WHERE subfolder_of IS NULL
	
  UNION ALL 
	
  SELECT	folder.id,
    		folder.name,
    		folder.subfolder_of,
    		folder_hierarchy.path || '\' || folder.name
  FROM folder, folder_hierarchy
  WHERE folder.subfolder_of = folder_hierarchy.id
)
SELECT *
FROM folder_hierarchy;

Das Prinzip ist das gleiche wie im vorherigen Beispiel. Auch hier beginnen Sie mit WITH RECURSIVE und dem Namen: folder_hierarchy. Die erste Anweisung SELECT selektiert alle drei Spalten der Tabelle folder. Die vierte Spalte ist path, die Daten aus dem Spaltennamen in Form von Textwerten enthält. Die Daten werden so umgewandelt, dass sie mit dem Datentyp aus dem rekursiven Element der CTE übereinstimmen. Schließlich schränkt die WHERE-Klausel die Daten nur auf diejenigen ein, die in der Spalte subfolder_of die Werte NULL enthalten. Wo NULL steht, ist der Stammordner (d. h. derjenige, der keine Ordner darüber hat).

Die UNION ALL wird wiederum verwendet, um die Anker- und rekursiven Mitglieder des CTE zu "verbinden". Noch ein Hinweis: Die Datentypen in beiden SELECT Anweisungen müssen übereinstimmen, damit UNION ALL funktioniert. Andernfalls wird die Abfrage einen Fehler zurückgeben.

Die zweite Anweisung SELECT wählt wiederum alle Spalten aus der Tabelle folder. Die Werte der Spalten Pfad und Name werden zusammengefügt, wobei '\' die Daten trennt.

Schließlich werden alle Daten aus der CTE ausgewählt, was eine schöne Tabelle ergibt:

Jetzt hat jeder Ordner seinen Pfad und kann leicht aufgerufen werden. Sie müssen sich nicht mehr durch mehrere Ebenen von Ordnern wühlen, um das zu finden, was Sie brauchen!

Ich glaube, das sind überzeugende Beispiele für die Leistungsfähigkeit rekursiver Abfragen. Falls nicht, brauchen Sie vielleicht einen weiteren Artikel, um das wahre Potenzial rekursiver Abfragen zu erkennen. Und wenn Sie ein Oracle-Benutzer sind, erfahren Sie hier, wie Sie hierarchische Abfragen in diesem speziellen DBMS verwenden können.

Hierarchische Daten abfragen ist einfacher denn je!

Jetzt, da Sie wissen, was hierarchische Daten sind, werden Sie sie überall wiedererkennen. Ich habe Ihnen einige alltägliche Beispiele genannt, aber ich bin sicher, Sie werden noch mehr finden. Versuchen Sie, die Self-Joins, rekursiven CTEs und alles andere, was Sie hier gelernt haben, zu implementieren. Nur so können Sie Ihr Wissen auffrischen!

Wenn Ihnen praktische Übungen fehlen, finden Sie diese in unserem Rekursive Abfragen Kurs. Was können Sie in diesem Kurs lernen? Wie können Ihnen rekursive Abfragen helfen? Kein Grund zur Verwunderung; unser Chief Content Officer erklärt in diesem Artikel alles im Detail.