WITH¶

Allgemeine Nutzung¶

• Eine WITH-Klausel kann sich rekursiv auf sich selbst und auf andere CTEs beziehen, die zuvor in derselben Klausel aufgeführt sind. Beispielsweise kann cte_name2 auf cte_name1 und auf sich selbst verweisen, während cte_name1 auf sich selbst verweisen kann, aber nicht auf cte_name2.

• Sie können rekursive und nicht rekursive (iterative und nicht iterative) CTE-Klauseln in der WITH-Klausel mischen. Die CTE-Klauseln sollten so angeordnet werden, dass, wenn ein CTE auf einen anderen CTE verweisen muss, der zu referenzierende CTE zu einem früheren Zeitpunkt in der Anweisung definiert wurde (z. B. der zweite CTE kann auf den ersten CTE verweisen, aber nicht umgekehrt).

  Die CTEs müssen nicht in der Reihenfolge danach aufgelistet werden, ob sie rekursiv sind oder nicht. Ein nicht rekursiver CTE kann beispielsweise unmittelbar nach dem Schlüsselwort RECURSIVE aufgeführt werden, und ein rekursiver CTE kann nach dem nicht rekursiven CTE stehen.

  In einem rekursiven CTE können entweder die Ankerklausel oder die rekursive Klausel (oder beide) auf andere CTEs verweisen.

• Bei rekursiven CTEs ist cte_column_list erforderlich.

• Bei nicht rekursiven CTEs ist cte_column_list optional.

• Stellen Sie sicher, dass Sie in einem rekursiven CTE UNION ALL und nicht UNION verwenden.

• Das Schlüsselwort RECURSIVE ist optional.

  • CTEs können rekursiv sein, unabhängig davon, ob das Schlüsselwort RECURSIVE angegeben wurde oder nicht.

  • Sie können das Wort RECURSIVE auch dann verwenden, wenn keine CTEs rekursiv sind.

  • Wenn RECURSIVE verwendet wird, darf es nur einmal verwendet werden, auch wenn mehr als ein CTE rekursiv ist.

  Obwohl SQL-Anweisungen mit und ohne Schlüsselwort RECURSIVE ordnungsgemäß funktionieren, erleichtert die sorgfältige Verwendung des Schlüsselworts das Verständnis und die Wartung des Codes. Snowflake empfiehlt die Verwendung des Schlüsselworts RECURSIVE, wenn einer oder mehrere CTEs rekursiv sind. Außerdem empfiehlt Snowflake nachdrücklich, das Schlüsselwort wegzulassen, wenn keiner der CTEs rekursiv ist.

WITH cte AS (
  SELECT ..., 1 as level ...

  UNION ALL

  SELECT ..., cte.level + 1 as level
   FROM cte ...
   WHERE ... level < 10
) ...

Einschränkungen¶

• Die Snowflake-Implementierung rekursiver CTEs unterstützt die folgenden Schlüsselwörter nicht, auch wenn diese von einigen anderen Systemen unterstützt werden:

  • SEARCH DEPTH FIRST BY ...

  • CYCLE ... SET ...

Ankerklausel¶

Die Ankerklausel in einem rekursiven CTE ist eine SELECT-Anweisung.

Die Ankerklausel wird bei der Ausführung der Anweisung, in die sie eingebettet ist, einmal ausgeführt. Sie wird vor der rekursiven Klausel ausgeführt und generiert die erste Reihe von Zeilen aus dem rekursiven CTE. Diese Zeilen sind nicht nur in der Ausgabe der Abfrage enthalten, sondern werden auch von der rekursiven Klausel referenziert.

Die Ankerklausel kann ein beliebiges SQL-Konstrukt enthalten, das in einer SELECT-Klausel zulässig ist. Die Ankerklausel kann jedoch nicht auf cte_name1 verweisen. Nur die rekursive Klausel kann auf cte_name1 verweisen.

Obwohl die Ankerklausel normalerweise aus derselben Tabelle wie die rekursive Klausel ausgewählt wird, ist dies nicht erforderlich. Die Ankerklausel kann aus jeder tabellenähnlichen Datenquelle auswählen, einschließlich einer anderen Tabelle, einer Ansicht, einem UDTF oder einem Konstantenwert.

Die Ankerklausel wählt eine einzelne „Ebene“ der Hierarchie aus, typischerweise die oberste Ebene oder die höchste Ebene, die von Interesse ist. Wenn die Abfrage beispielsweise die „Stückliste“ eines Autos anzeigen soll, gibt die Ankerklausel die Komponente der höchsten Ebene zurück, nämlich das Auto selbst.

Die Ausgabe der Ankerklausel stellt eine Ebene der Hierarchie dar, und diese Ebene wird als Inhalt der „Ansicht“ gespeichert, auf die in der ersten Iteration der rekursiven Klausel zugegriffen wird.

Rekursive Klausel¶

Die rekursive Klausel ist eine SELECT-Anweisung. Diese SELECT-Anweisung ist auf Projektionen, Filter und Verknüpfungen (Joins) beschränkt (innere Verknüpfungen und äußere Verknüpfungen, bei denen sich die rekursive Referenz auf der konservierten Seite der äußeren Verknüpfung befindet). Die rekursive Klausel darf nicht enthalten:

• Aggregat- oder Fensterfunktionen,

• GROUP BY, ORDER BY, LIMIT oder DISTINCT

Die rekursive Klausel kann auf cte_name1 verweisen (und tut es normalerweise), als ob der CTE eine Tabelle oder Ansicht wäre.

Die rekursive Klausel enthält normalerweise ein JOIN, das die in der Ankerklausel verwendete Tabelle mit dem CTE verknüpft. Die JOIN-Klausel kann jedoch mehr als eine Tabelle oder tabellenähnliche Datenquelle (Ansicht usw.) verknüpfen.

Die erste Iteration der rekursiven Klausel beginnt mit den Daten aus der Ankerklausel. Diese Daten werden dann mit den anderen Tabellen in der FROM-Klausel der rekursiven Klausel verknüpft.

Jede nachfolgende Iteration beginnt mit den Daten der vorherigen Iteration.

Sie können sich die CTE-Klausel oder „Ansicht“ so vorstellen, dass sie den Inhalt der vorherigen Iteration enthält, damit dieser Inhalt für Verknüpfungen verfügbar ist. Beachten Sie, dass bei einer Iteration der CTE nur den Inhalt der vorherigen Iteration enthält, und nicht die Ergebnisse, die im Zuge aller vorherigen Iterationen akkumuliert wurden. Die akkumulierten Ergebnisse (einschließlich der Ankerklausel) werden an einem separaten Ort gespeichert.

Spaltenlisten in einem rekursiven CTE¶

Es gibt drei Spaltenlisten in einem rekursiven CTE:

• cte_column_list

• anchor_column_list (in der Ankerklausel)

• recursive_column_list (in der rekursiven Klausel)

Ein rekursiver CTE kann andere Spaltenlisten enthalten (z. B. in einer Unterabfrage), aber diese drei Spaltenlisten müssen vorhanden sein.

Diese drei Spaltenlisten müssen alle miteinander korrespondieren.

Im Pseudocode sieht das ungefähr so aus:

WITH RECURSIVE cte_name (X, Y) AS
(
  SELECT related_to_X, related_to_Y FROM table1
  UNION ALL
  SELECT also_related_to_X, also_related_to_Y
    FROM table1 JOIN cte_name ON <join_condition>
)
SELECT ... FROM ...

Die Spalten X und related_to_X müssen übereinstimmen. Die Ankerklausel generiert den anfänglichen „Inhalt“ der „Ansicht“, die den CTE darstellt. Daher muss jede Spalte der Ankerklausel (z. B. Spalte related_to_x) eine Ausgabe generieren, die in die entsprechende Spalte des CTE gehört (z. B. Spalte X).

Die Spalten also_related_to_X und X müssen übereinstimmen; bei jeder Iteration der rekursiven Klausel wird die Ausgabe dieser Klausel zum neuen Inhalt des CTE/der Ansicht für die nächste Iteration.

Außerdem müssen die Spalten related_to_X und also_related_to_X übereinstimmen, da sie sich jeweils auf einer Seite des Operators UNION ALL befinden, und die Spalten auf jeder Seite eines UNION ALL-Operators müssen übereinstimmen.

Nicht rekursive Beispiele¶

In diesem Abschnitt stellen wir Beispielabfragen und Beispielausgaben vor. Um die Beispiele kurz zu halten, werden im Code die Anweisungen zum Erstellen und Laden der Tabellen weggelassen.

In diesem ersten Beispiel wird eine einfache WITH-Klausel verwendet, um eine Teilmenge von Daten zu extrahieren, in diesem Fall Musikalben, die 1976 veröffentlicht wurden. Bei dieser kleinen Datenbank stellen die Alben „Amigos“ und „Look Into The Future“ (beide aus dem Jahr 1976) die Ausgabe der Abfrage dar:

with
  albums_1976 as (select * from music_albums where album_year = 1976)
select album_name from albums_1976 order by album_name;
+----------------------+
| ALBUM_NAME           |
|----------------------|
| Amigos               |
| Look Into The Future |
+----------------------+

Copy

In diesem nächsten Beispiel wird eine WITH-Klausel mit einer früheren WITH-Klausel verwendet. Der CTE mit dem Namen journey_album_info_1976 verwendet den CTE mit dem Namen album_info_1976. Die Ausgabe ist das Album „Look Into The Future“ mit dem Namen der Band:

with
   album_info_1976 as (select m.album_ID, m.album_name, b.band_name
      from music_albums as m inner join music_bands as b
      where m.band_id = b.band_id and album_year = 1976),
   Journey_album_info_1976 as (select *
      from album_info_1976 
      where band_name = 'Journey')
select album_name, band_name 
   from Journey_album_info_1976;
+----------------------+-----------+
| ALBUM_NAME           | BAND_NAME |
|----------------------+-----------|
| Look Into The Future | Journey   |
+----------------------+-----------+

Copy

In diesem Beispiel werden Musiker aufgelistet, die auf Santana-Alben und Journey-Alben gespielt haben. In diesem Beispiel wird die WITH-Klausel nicht verwendet. Bei dieser Abfrage (und den nächsten Abfragen, die alle gleichwertige Methoden zum Ausführen derselben Abfrage darstellen) besteht die Ausgabe aus den IDs und Namen von Musikern, die beide auf Santana- und Journey-Alben gespielt haben.

select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Santana'
intersect
select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Wie Sie sehen, enthält die vorherige Abfrage doppelten Code. In den nächsten Beispielen zeigen wir Ihnen, wie Sie diese Abfrage mithilfe einer oder mehrerer expliziter Ansichten vereinfachen und anschließend mithilfe von CTEs optimieren können.

Diese Abfrage zeigt, wie Sie Ansichten verwenden können, um die Duplizierung und Komplexität im vorherigen Beispiel zu reduzieren (wie im vorherigen Beispiel wird keine WITH-Klausel verwendet):

create or replace view view_musicians_in_bands AS
  select distinct musicians.musician_id, musician_name, band_name
    from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
    where musicians.musician_ID = musicians_and_albums.musician_ID
      and musicians_and_albums.album_ID = music_albums.album_ID
      and music_albums.band_ID = music_bands.band_ID;

Copy

Mit dieser Ansicht können Sie die ursprüngliche Abfrage wie folgt neu schreiben:

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

In diesem Beispiel wird eine WITH-Klausel verwendet, die das Gleiche erledigt wie die vorhergehende Abfrage:

with
  musicians_in_bands as (
     select distinct musicians.musician_id, musician_name, band_name
      from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
      where musicians.musician_ID = musicians_and_albums.musician_ID
        and musicians_and_albums.album_ID = music_albums.album_ID
        and music_albums.band_ID = music_bands.band_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Mit diesen Anweisungen lassen sich genauere Ansichten erstellen (in diesem Beispiel wird keine WITH-Klausel verwendet):

Listen Sie die Alben einer bestimmten Band auf:

create or replace view view_album_IDs_by_bands AS
 select album_ID, music_bands.band_id, band_name
  from music_albums inner join music_bands
  where music_albums.band_id = music_bands.band_ID;

Copy

Listen Sie die Musiker auf, die auf Alben gespielt haben:

create or replace view view_musicians_in_bands AS
 select distinct musicians.musician_id, musician_name, band_name
  from musicians inner join musicians_and_albums inner join view_album_IDs_by_bands
  where musicians.musician_ID = musicians_and_albums.musician_ID
    and musicians_and_albums.album_ID = view_album_IDS_by_bands.album_ID;

Copy

Verwenden Sie nun die Ansichten, um Musiker abzufragen, die sowohl auf Santana- als auch auf Journey-Alben gespielt haben:

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Mit diesen Anweisungen werden genauere implizite Ansichten erstellt (in diesem Beispiel wird eine WITH-Klausel verwendet):

with
  album_IDs_by_bands as (select album_ID, music_bands.band_id, band_name
                          from music_albums inner join music_bands
                          where music_albums.band_id = music_bands.band_ID),
  musicians_in_bands as (select distinct musicians.musician_id, musician_name, band_name
                          from musicians inner join musicians_and_albums inner join album_IDs_by_bands
                          where musicians.musician_ID = musicians_and_albums.musician_ID
                            and musicians_and_albums.album_ID = album_IDS_by_bands.album_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Rekursive Beispiele¶

Dies ist ein grundlegendes Beispiel für die Verwendung eines rekursiven CTE zum Generieren einer Fibonacci-Reihe:

WITH RECURSIVE current_f (current_val, previous_val) AS
    (
    SELECT 0, 1
    UNION ALL 
    SELECT current_val + previous_val, current_val FROM current_f
      WHERE current_val + previous_val < 100
    )
  SELECT current_val FROM current_f ORDER BY current_val;
+-------------+
| CURRENT_VAL |
|-------------|
|           0 |
|           1 |
|           1 |
|           2 |
|           3 |
|           5 |
|           8 |
|          13 |
|          21 |
|          34 |
|          55 |
|          89 |
+-------------+

Copy

Dieses Beispiel ist eine Abfrage mit einem rekursiven CTE, das eine „Teileexplosion“ für ein Auto zeigt:

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    component_ID INTEGER,
    quantity 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
    ;

Copy

WITH RECURSIVE current_layer (indent, layer_ID, parent_component_ID, component_id, description, sort_key) AS (
  SELECT 
      '...', 
      1, 
      parent_component_ID, 
      component_id, 
      description, 
      '0001'
    FROM components WHERE component_id = 1
  UNION ALL
  SELECT indent || '...',
      layer_ID + 1,
      components.parent_component_ID,
      components.component_id, 
      components.description,
      sort_key || SUBSTRING('000' || components.component_ID, -4)
    FROM current_layer JOIN components 
      ON (components.parent_component_id = current_layer.component_id)
  )
SELECT
  -- The indentation gives us a sort of "side-ways tree" view, with
  -- sub-components indented under their respective components.
  indent || description AS description, 
  component_id,
  parent_component_ID
  -- The layer_ID and sort_key are useful for debugging, but not
  -- needed in the report.
--  , layer_ID, sort_key
  FROM current_layer
  ORDER BY sort_key;
+-------------------------+--------------+---------------------+
| DESCRIPTION             | COMPONENT_ID | PARENT_COMPONENT_ID |
|-------------------------+--------------+---------------------|
| ...car                  |            1 |                   0 |
| ......wheel             |           11 |                   1 |
| .........tire           |          111 |                  11 |
| .........#112 bolt      |          112 |                  11 |
| .........brake          |          113 |                  11 |
| ............brake pad   |         1131 |                 113 |
| ......engine            |           12 |                   1 |
| .........#112 bolt      |          112 |                  12 |
| .........piston         |          121 |                  12 |
| .........cylinder block |          122 |                  12 |
+-------------------------+--------------+---------------------+

Copy

Weitere Beispiele finden Sie unter Arbeiten mit CTEs (allgemeinen Tabellenausdrücken).