- Kategorien:
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:
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> ]
...
...
Spaltenliste
Dies folgt im Allgemeinen den Regeln für die Projektionsklausel einer SELECT-Anweisung.
Ebenenausdruck
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.Datenquelle
Die Datenquelle ist normalerweise eine Tabelle, kann jedoch auch eine andere tabellenähnliche Datenquelle sein, z. B. eine Ansicht, UDTF usw.
Prädikat
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).
SpalteN_Bezeichner
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>
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.
Standardmäßig gibt es eine Obergrenze für die Anzahl der Iterationen, um eine Endlosschleife von Abfragen zu verhindern.
Einige Abweichungen innerhalb der Projektionsklausel sind gültig. Obwohl in der Syntax
Ebenenausdruck
nach derSpaltenliste
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.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 KlauselCONNECT 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 KlauselCONNECT BY
. Mit dem OperatorCONNECT_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.Obwohl die
CONNECT BY
-Klausel theoretisch so oft wie nötig iterieren kann, um die Daten zu verarbeiten, begrenzt Snowflake die Anzahl der Iterationen derzeit auf 100, um zu verhindern, dass die Abfrage unbegrenzt ausgeführt wird, wenn sie nicht ordnungsgemäß erstellt wurde.
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 | +----------------+----------+--------------+---------------------+----------------------------+