Abfragen hierarchischer Daten

Unter diesem Thema wird beschrieben, wie sich hierarchische Daten speichern und abfragen lassen mit:

  • JOINs

  • Rekursiven CTEs (allgemeinen Tabellenausdrücken)

  • CONNECT BY

Siehe auch:

CONNECT BY, der rekursive CTE-Teil des WITH-Befehls, Arbeiten mit CTEs (allgemeinen Tabellenausdrücken), SQL-UDTFs (Benutzerdefinierte Tabellenfunktionen)

Unter diesem Thema:

Speichern hierarchischer Daten

Viele Datentypen lassen sich am besten als Hierarchie darstellen, z. B. als Baumstruktur.

Beispielsweise sind Mitarbeiter normalerweise in einer Hierarchie organisiert, wobei ein Unternehmensleiter an der Spitze der Hierarchie steht.

Ein weiteres Beispiel für eine Hierarchie ist eine „Teileauflösung“. Ein Auto enthält beispielsweise einen Motor; ein Motor enthält eine Kraftstoffpumpe; und eine Kraftstoffpumpe enthält einen Schlauch.

Sie können hierarchische Daten speichern in:

  • Einer Hierarchie von Tabellen.

  • Einer einzelnen Tabelle mit einer (oder mehreren) Spalten, die die Hierarchie darstellen (z. B. Angabe des direkten Vorgesetzten für einzelne Mitarbeiter).

Beide Techniken werden nachfolgend beschrieben.

Hierarchische Daten für mehrere Tabellen

Relationale Datenbanken speichern hierarchische Daten häufig mithilfe verschiedener Tabellen. Beispielsweise kann eine Tabelle „übergeordnete“ Daten und eine andere Tabelle „untergeordnete“ Daten enthalten. Wenn die gesamte Hierarchie im Voraus bekannt ist, kann für jede Ebene in der Hierarchie eine Tabelle erstellt werden.

Stellen Sie sich beispielsweise eine Personaldatenbank vor, in der Informationen über Mitarbeiter und Vorgesetzte gespeichert sind. Wenn das Unternehmen klein ist, gibt es möglicherweise nur zwei Ebenen, z. B. einen Vorgesetzten und zwei Mitarbeiter.

CREATE OR REPLACE TABLE managers  (title VARCHAR, employee_ID INTEGER);
CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
INSERT INTO managers (title, employee_ID) VALUES
    ('President', 1);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('Vice President Engineering', 10, 1),
    ('Vice President HR', 20, 1);

Hierarchische Daten in einer einzigen Tabelle

In einigen Situationen kann sich die Anzahl der Ebenen in der Hierarchie ändern.

Beispielsweise kann ein Unternehmen, das mit einer zweistufigen Hierarchie (Unternehmensleiter und andere Mitarbeiter) begonnen hat, die Anzahl der Ebenen erhöhen, wenn das Unternehmen wächst. Das Unternehmen wird möglicherweise um einen Vorstandsvorsitzenden, stellvertretende Vorstandsvorsitzenden und reguläre Mitarbeiter erweitert.

Wenn die Anzahl der Ebenen unbekannt ist, so dass keine Hierarchie mit einer bekannten Anzahl von Tabellen erstellt werden kann, können die hierarchischen Daten in manchen Fällen in einer Tabelle gespeichert werden. Beispielsweise kann eine einzelne Tabelle alle Mitarbeiter enthalten und eine Spalte umfassen, in der die Vorgesetzten-ID einzelner Mitarbeiter, die auf einen anderen Mitarbeiter in derselben Tabelle verweist, gespeichert ist. Beispiel:

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);

Das Speichern einer gesamten Hierarchie von Daten in einer Tabelle funktioniert am besten, wenn alle Ebenen der Hierarchie dieselben Daten speichern – in unserem Beispiel Mitarbeiter-ID, Titel usw. Wenn die Daten auf verschiedenen Ebenen nicht der gleichen Datensatzstruktur entsprechen, ist es möglicherweise nicht sinnvoll, alle Daten in einer Tabelle zu speichern.

Verwenden von Verknüpfungen zum Abfragen hierarchischer Daten

In einer zweistufigen Hierarchie (z. B. Vorgesetzte und Mitarbeiter) können die Daten mit einer Zweiwegeverknüpfung abgefragt werden:

SELECT 
        employees.title, 
        employees.employee_ID, 
        managers.employee_ID AS MANAGER_ID, 
        managers.title AS "MANAGER TITLE"
    FROM employees, managers
    WHERE employees.manager_ID = managers.employee_ID;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+

In einer dreistufigen Hierarchie können Sie einen Drei-Wege-Join verwenden:

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+

Dieses Konzept kann auf beliebig viele Ebenen erweitert werden – sofern Sie wissen, wie viele Ebenen benötigt werden. Wenn sich die Anzahl der Ebenen ändert, müssen sich die Abfragen ändern.

Verwenden von CONNECT BY oder rekursiven CTEs zum Abfragen hierarchischer Daten

Snowflake bietet zwei Möglichkeiten, hierarchische Daten abzufragen, bei denen die Anzahl der Ebenen nicht im Voraus bekannt ist:

  • Rekursive CTEs (allgemeine Tabellenausdrücke).

  • CONNECT BY-Klauseln.

Mit einem rekursiven CTE können Sie eine WITH-Klausel erstellen, die auf sich selbst verweisen kann. Auf diese Weise können Sie jede Ebene Ihrer Hierarchie durchlaufen und Ergebnisse akkumulieren.

Mit einer CONNECT BY-Klausel können Sie eine Art JOIN-Operation erstellen, die die Hierarchie auf einer Ebene nach der anderen verarbeitet. Dabei kann sich jede Ebene auf Daten der vorherigen Ebene beziehen.

Weitere Details dazu finden Sie unter:

Unterschiede zwischen Selbstverknüpfung, rekursivem CTE und CONNECT BY

CONNECT BY erlaubt nur Selbstverknüpfungen. Rekursive CTEs sind flexibler und ermöglichen das Verknüpfen einer Tabelle mit einer oder mehreren anderen Tabellen.

Eine CONNECT BY-Klausel bietet die meisten Möglichkeiten eines rekursiven CTE. Ein rekursiver CTE kann jedoch einige Aufgaben erledigen, zu denen CONNECT BY nicht in der Lage ist.

Wenn Sie sich beispielsweise die rekursiven CTE-Beispiele ansehen, werden Sie feststellen, dass eine der Abfragen die Ausgabe einrückt und außerdem die Ausgabe so sortiert, dass jedes „untergeordnete Element“ unter dem entsprechenden „übergeordneten Element“ angezeigt wird. Zum Sortieren wird ein Sortierschlüssel erstellt, der die Kette von IDs von oben bis zur aktuellen Ebene enthält. Im Beispiel Vorgesetzte/Mitarbeiter enthält die Kette die ID des Vorstandsvorsitzenden, gefolgt von der ID des stellvertretenden Vorstandsvorsitzenden usw. Durch diesen Sortierschlüssel werden Zeilen so gruppiert, dass sie einem seitlich liegenden Baum ähneln. Die Syntax CONNECT BY unterstützt dies nicht, da die Klausel „START WITH“ es dem Code nicht erlaubt, zusätzliche Spalten (über die in der Tabelle selbst hinaus) anzugeben, z. B. den sort_key. Vergleichen Sie die beiden folgenden Codefragmente:

SELECT indent(LEVEL) || employee_ID, manager_ID, title
  FROM employees
    -- This sub-clause specifies the record at the top of the hierarchy,
    -- but does not allow additional derived fields, such as the sort key.
    START WITH TITLE = 'President'
    CONNECT BY ...

WITH RECURSIVE current_layer
   (employee_ID, manager_ID, sort_key) AS (
     -- This allows us to add columns, such as sort_key, that are not part
     -- of the employees table.
     SELECT employee_ID, manager_ID, employee_ID AS sort_key
     ...
     )

Sie können jedoch die Funktion SYS_CONNECT_BY_PATH verwenden, um mit der CONNECT BY-Klausel einen ähnlichen Effekt zu erzielen.

Obwohl die Variante mit der CONNECT BY-Klausel eingeschränkt ist, weil die START WITH-Klausel keine Spalten zu den bereits in der Zeile enthaltenen hinzufügen kann (auch keine abgeleiteten Spalten, die auf Werten basieren, die bereits in der Zeile enthalten sind), bietet sie auch einige Vorteile:

  • Sie haben Zugriff auf alle Spalten jeder Zeile, ohne diese Spalten in einer Spaltenliste anzugeben. In einem rekursiven CTE hat die rekursive Klausel keinen Zugriff auf Spalten, die im CTE nicht explizit angegeben sind.

  • In einem rekursiven CTE müssen Sie die Spalten im CTE angeben, und die Projektionslisten der Auswahlen in der Ankerklausel und der rekursiven Klausel müssen beide mit den Spalten im CTE übereinstimmen. Wenn die Reihenfolge der Spalten in den verschiedenen Projektionsklauseln nicht übereinstimmt, können Probleme wie Endlosschleifen auftreten.

  • Die CONNECT BY-Syntax unterstützt praktische Pseudospalten wie LEVEL, CONNECT_BY_ROOT und CONNECT_BY_PATH.

Ein kleiner Unterschied zwischen CONNECT BY und einem rekursiven CTE besteht darin, dass Sie in CONNECT BY mit dem Schlüsselwort PRIOR angeben, welche Spaltenwerte aus der vorherigen Iteration übernommen werden sollen, während Sie in einem rekursiven CTE den Tabellennamen und den CTE-Namen verwenden, um anzugeben, welche Werte aus der aktuellen Iteration und welche aus der vorherigen Iteration übernommen werden. (In einem rekursiven CTE können Sie auch zwischen aktuellen und vorherigen Iterationen unterscheiden, indem Sie in der CTE-Spaltenliste andere Spaltennamen als in der Quelltabelle oder im Tabellenausdruck verwenden.)

Nicht zusammenhängende Hierarchien

Unter diesem Thema wurden Hierarchien und die Frage beschrieben, wie rekursive CTEs (allgemeine Tabellenausdrücke) und CONNECT BY-Klauseln Beziehungen zwischen übergeordneten/untergeordneten Elementen verwenden können. In allen Beispielen dieses Themas sowie in allen Beispielen in der CONNECT BY-Dokumentation und der Dokumentation für rekursive CTEs sind die Hierarchien zusammenhängend. Keines der Beispiele hat ein übergeordnetes und weiter untergeordnetes Element, ohne dass sich dazwischen ein entsprechendes untergeordnetes Element befindet.

Wenn Sie zum Beispiel eine „Teileauflösung“ für ein Auto durchführen, werden Sie nicht eine Komponente für das Auto und eine Komponente für den Reifen haben, ohne eine Komponente für das Rad zu haben, das den Reifen enthält (und das im Auto enthalten ist).

Es kann jedoch Fälle geben, in denen Daten unvollständig sind. Angenommen, in einer Mitarbeiter-/Vorgesetztenhierarchie geht der Vice President of Engineering in den Ruhestand und das Unternehmen hat noch keinen Ersatz eingestellt. Wenn der Mitarbeiterdatensatz des VPgelöscht wird, werden Mitarbeiter unterhalb des VP vom Rest der Hierarchie „abgeschnitten“, sodass die Mitarbeitertabelle keine einzelne zusammenhängende Hierarchie mehr enthält.

Wenn Sie rekursive CTEs oder CONNECT BY zum Verarbeiten von Daten verwenden, müssen Sie überlegen, ob die Daten in Ihrer Tabelle eine einzelne zusammenhängende Baumstruktur darstellen. Sie können rekursive CTEs und CONNECT BY für eine einzelne Tabelle verwenden, die mehrere Baumstrukturen enthält. Sie können jedoch immer nur eine Baumstruktur abfragen, und diese Baumstruktur muss zusammenhängend sein.