Kategorien:: Abfragesyntax

CONNECT BY¶

Verknüpft eine Tabelle mit sich selbst, um hierarchische Daten in der Tabelle zu verarbeiten. Die CONNECT BY-Unterklausel der FROM-Klausel iteriert, um die Daten zu verarbeiten.

Beispielsweise können Sie eine Abfrage erstellen, die eine „Teileauflösung“ anzeigt, um eine Komponente und die Unterkomponenten dieser Komponente rekursiv aufzulisten.

Die Snowflake-Syntax für CONNECT BY ist größtenteils mit der Oracle-Syntax kompatibel.

Siehe auch:: WITH

Syntax¶

Die allgemeine Form einer Anweisung mit CONNECT BY ähnelt der folgenden (einige Abweichungen in der Reihenfolge sind zulässig, werden jedoch nicht dargestellt):

SELECT <column_list> [ , <level_expression> ]
  FROM <data_source>
    START WITH <predicate>
    CONNECT BY [ PRIOR ] <col1_identifier> = [ PRIOR ] <col2_identifier>
           [ , [ PRIOR ] <col3_identifier> = [ PRIOR ] <col4_identifier> ]
           ...
  ...

column_list

Dies folgt im Allgemeinen den Regeln für die Projektionsklausel einer SELECT-Anweisung.

level_expression

CONNECT BY-Abfragen erlauben einige Pseudospalten. Eine dieser Pseudospalten ist LEVEL, die die aktuelle Hierarchieebene angibt (wobei Ebene 1 die oberste Hierarchieebene darstellt). Die Projektionsklausel der Abfrage kann LEVEL als Spalte verwenden.

data_source

Die Datenquelle ist normalerweise eine Tabelle, kann jedoch auch eine andere tabellenähnliche Datenquelle sein, z. B. eine Ansicht, UDTF usw.

predicate

Das Prädikat ist ein Ausdruck, der die erste „Ebene“ der Hierarchie auswählt (z. B. den Leiter des Unternehmens oder die Komponente auf oberster Ebene in einer Teileauflösung). Das Prädikat sollte einer WHERE-Klausel ähneln, jedoch ohne das Schlüsselwort WHERE.

Beispiele für Prädikate finden Sie im Abschnitt Beispiele (unter diesem Thema).

colN_identifier

Die CONNECT BY-Klausel sollte einen oder mehrere Ausdrücke enthalten, die denen in Verknüpfungen ähneln. Insbesondere sollte sich eine Spalte in der „aktuellen“ Ebene der Tabelle auf eine Spalte in der „vorherigen“ (höheren) Ebene der Tabelle beziehen.

In einer Vorgesetzten-/Mitarbeiterhierarchie könnte die Klausel beispielsweise folgendermaßen aussehen:

... CONNECT BY manager_ID = PRIOR employee_ID ...

Das Schlüsselwort PRIOR gibt an, dass der Wert von der vorherigen (höheren/übergeordneten) Ebene übernommen werden soll.

In diesem Beispiel sollte die ID des aktuellen Mitarbeiters mit der ID der vorherigen Ebene übereinstimmen.

Die CONNECT BY-Klausel kann mehr als einen solchen Ausdruck enthalten, zum Beispiel:

... CONNECT BY y = PRIOR x AND b = PRIOR a ...

Jeder Ausdruck, der dem folgenden ähnlich ist, sollte genau ein Vorkommen des Schlüsselworts PRIOR haben:

CONNECT BY <col_1_identifier> = <col_2_identifier>
Das Schlüsselwort PRIOR kann auf der linken Seite und auf der rechten Seite des =-Zeichens stehen. Beispiel:
CONNECT BY <col_1_identifier> = PRIOR <col_2_identifier>
oder
CONNECT BY PRIOR <col_1_identifier> = <col_2_identifier>

Nutzungshinweise¶

Eine CONNECT BY-Klausel verknüpft eine Tabelle immer mit sich selbst und nicht mit einer anderen Tabelle.
Einige Abweichungen innerhalb der Projektionsklausel sind gültig. Obwohl in der Syntax level_expression nach column_list angezeigt wird, können die Ebenenausdrücke in beliebiger Reihenfolge vorkommen.
Das Schlüsselwort PRIOR darf in jeder CONNECT BY-Klausel genau einmal vorkommen. PRIOR kann sowohl auf der linken als auch auf der rechten Seite des Ausdrucks auftreten, jedoch nicht auf beiden.
Eine Abfrage mit CONNECT BY kann auch einen oder beide der folgenden Elemente enthalten:
- Filter in einer WHERE-Klausel
- JOINs (entweder in einer FROM-Klausel oder in einer WHERE-Klausel)
Die Reihenfolge der Auswertung ist wie folgt:
1. JOINs (unabhängig davon, ob in der WHERE-Klausel oder der FROM-Klausel angegeben)
2. CONNECT BY
3. Filter (andere als JOIN-Filter)
So werden beispielsweise Filter in einer WHERE-Klausel nach der CONNECT BY-Klausel verarbeitet.
Die Snowflake-Implementierung von CONNECT BY ist größtenteils mit der Oracle-Implementierung kompatibel. Snowflake unterstützt allerdings Folgendes nicht:
- NOCYCLE
- CONNECT_BY_ISCYCLE
- CONNECT_BY_ISLEAF

Snowflake unterstützt die Funktion SYS_CONNECT_BY_PATH in Verbindung mit der Klausel CONNECT BY. SYS_CONNECT_BY_PATH gibt eine Zeichenfolge zurück, die den Pfad vom Stamm zum aktuellen Element enthält. Ein Beispiel befindet sich im Abschnitt Beispiele unten.

Snowflake unterstützt den Operator CONNECT_BY_ROOT in Verbindung mit der Klausel CONNECT BY. Mit dem Operator CONNECT_BY_ROOT kann die aktuelle Ebene Informationen aus der Stammebene der Hierarchie nutzen, auch wenn die Stammebene nicht die unmittelbare übergeordnete Ebene der aktuellen Ebene ist. Ein Beispiel befindet sich im Abschnitt Beispiele unten.
Die CONNECT BY-Klausel kann so viele Iterationen durchlaufen, wie zur Verarbeitung der Daten erforderlich sind. Die fehlerhafte Konstruktion einer Abfrage kann zu einer Endlosschleife führen. In diesen Fällen wird die Abfrage so lange ausgeführt, bis eine der folgenden Situationen eintritt: die Abfrage war erfolgreich, die Abfrage weist eine Zeitüberschreitung auf (z. B. wird die durch den Parameter STATEMENT_TIMEOUT_IN_SECONDS angegebene Anzahl von Sekunden überschritten), oder der Benutzer führt einen Abbruch der Abfrage aus.

Weitere Informationen zum Entstehen einer Endlosschleife und zu Richtlinien, mit deren Hilfe dieses Problem verhindert wird, finden Sie unter Problembehandlung bei rekursiven CTEs.

Beispiele¶

In diesem Beispiel wird mit CONNECT BY die Verwaltungshierarchie in einer Tabelle mit Mitarbeiterdaten angezeigt. Die Tabelle und Daten sind nachstehend aufgeführt:

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);

Die Abfrage und Ausgabe werden unten gezeigt:

SELECT employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|-------------+------------+----------------------------|
|           1 |       NULL | President                  |
|          10 |          1 | Vice President Engineering |
|          20 |          1 | Vice President HR          |
|         100 |         10 | Programmer                 |
|         101 |         10 | QA Engineer                |
|         200 |         20 | Health Insurance Analyst   |
+-------------+------------+----------------------------+

In diesem Beispiel wird mit der Funktion SYS_CONNECT_BY_PATH die Hierarchie vom Unternehmensleiter bis zum aktuellen Mitarbeiter angezeigt:

SELECT SYS_CONNECT_BY_PATH(title, ' -> '), employee_ID, manager_ID, title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+----------------------------------------------------------------+-------------+------------+----------------------------+
| SYS_CONNECT_BY_PATH(TITLE, ' -> ')                             | EMPLOYEE_ID | MANAGER_ID | TITLE                      |
|----------------------------------------------------------------+-------------+------------+----------------------------|
|  -> President                                                  |           1 |       NULL | President                  |
|  -> President -> Vice President Engineering                    |          10 |          1 | Vice President Engineering |
|  -> President -> Vice President HR                             |          20 |          1 | Vice President HR          |
|  -> President -> Vice President Engineering -> Programmer      |         100 |         10 | Programmer                 |
|  -> President -> Vice President Engineering -> QA Engineer     |         101 |         10 | QA Engineer                |
|  -> President -> Vice President HR -> Health Insurance Analyst |         200 |         20 | Health Insurance Analyst   |
+----------------------------------------------------------------+-------------+------------+----------------------------+

In diesem Beispiel wird das Schlüsselwort CONNECT_BY_ROOT verwendet, um in jeder Ausgabezeile Informationen von der Spitze der Hierarchie anzuzeigen:

SELECT 
employee_ID, manager_ID, title,
CONNECT_BY_ROOT title AS root_title
  FROM employees
    START WITH title = 'President'
    CONNECT BY
      manager_ID = PRIOR employee_id
  ORDER BY employee_ID;
+-------------+------------+----------------------------+------------+
| EMPLOYEE_ID | MANAGER_ID | TITLE                      | ROOT_TITLE |
|-------------+------------+----------------------------+------------|
|           1 |       NULL | President                  | President  |
|          10 |          1 | Vice President Engineering | President  |
|          20 |          1 | Vice President HR          | President  |
|         100 |         10 | Programmer                 | President  |
|         101 |         10 | QA Engineer                | President  |
|         200 |         20 | Health Insurance Analyst   | President  |
+-------------+------------+----------------------------+------------+

In diesem Beispiel wird mit CONNECT BY eine „Teileauflösung“ dargestellt:

Hier sind die Daten:

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    quantity INTEGER,
    component_ID INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Hier sind die Abfrage und Ausgabe:

SELECT
  description,
  quantity,
  component_id, 
  parent_component_ID,
  SYS_CONNECT_BY_PATH(component_ID, ' -> ') AS path
  FROM components
    START WITH component_ID = 1
    CONNECT BY 
      parent_component_ID = PRIOR component_ID
  ORDER BY path
  ;
+----------------+----------+--------------+---------------------+----------------------------+
| DESCRIPTION    | QUANTITY | COMPONENT_ID | PARENT_COMPONENT_ID | PATH                       |
|----------------+----------+--------------+---------------------+----------------------------|
| car            |        1 |            1 |                   0 |  -> 1                      |
| wheel          |        4 |           11 |                   1 |  -> 1 -> 11                |
| tire           |        1 |          111 |                  11 |  -> 1 -> 11 -> 111         |
| #112 bolt      |        5 |          112 |                  11 |  -> 1 -> 11 -> 112         |
| brake          |        1 |          113 |                  11 |  -> 1 -> 11 -> 113         |
| brake pad      |        1 |         1131 |                 113 |  -> 1 -> 11 -> 113 -> 1131 |
| engine         |        1 |           12 |                   1 |  -> 1 -> 12                |
| #112 bolt      |       16 |          112 |                  12 |  -> 1 -> 12 -> 112         |
| piston         |        4 |          121 |                  12 |  -> 1 -> 12 -> 121         |
| cylinder block |        1 |          122 |                  12 |  -> 1 -> 12 -> 122         |
+----------------+----------+--------------+---------------------+----------------------------+