Abfragen hierarchischer Daten¶

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.

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
    ORDER BY employees.title;
+----------------------------+-------------+------------+---------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE |
|----------------------------+-------------+------------+---------------|
| Vice President Engineering |          10 |          1 | President     |
| Vice President HR          |          20 |          1 | President     |
+----------------------------+-------------+------------+---------------+

Copy

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          |
+----------------------------+-------------+------------+----------------------------+

Copy

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:

• WITH und Arbeiten mit CTEs (allgemeinen Tabellenausdrücken):

• CONNECT BY.

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 einer seitlich liegenden Baumstruktur ähneln. Die CONNECT BY-Syntax unterstützt dies nicht, da die START WITH-Klausel 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 Codeausschnitte:

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 Strukturbäume enthält. Sie können jedoch immer nur einen Strukturbaum abfragen, und dieser Strukturbaum muss zusammenhängend sein.