Zum Inhalt springen
DuckDBDuckDBDuckDB
Deutsch

WITH-Klausel

Die WITH-Klausel erlaubt es, Common Table Expressions (CTEs) anzugeben. Reguläre (nicht-rekursive) Common Table Expressions sind im Wesentlichen Sichten, deren Gültigkeit auf eine bestimmte Abfrage beschränkt ist. CTEs können einander referenzieren und können verschachtelt werden. Rekursive CTEs können sich selbst referenzieren.

Einfache CTE-Beispiele

Ein CTE namens cte anlegen und in der Hauptabfrage verwenden:

WITH cte AS (SELECT 42 AS x)
SELECT * FROM cte;
x
42

Zwei CTEs cte1 und cte2 anlegen, wobei das zweite CTE das erste CTE referenziert:

WITH
    cte1 AS (SELECT 42 AS i),
    cte2 AS (SELECT i * 100 AS x FROM cte1)
SELECT * FROM cte2;
x
4200

Sie können Spaltennamen für CTEs angeben:

WITH cte(j) AS (SELECT 42 AS i)
FROM cte;

CTE-Materialisierung

DuckDB behandelt CTEs standardmäßig als materialisiert, das heißt, das CTE wird einmal ausgewertet und das Ergebnis in einer temporären Tabelle gespeichert. Unter bestimmten Bedingungen kann DuckDB das CTE jedoch in die Hauptabfrage inlinen, das heißt, das CTE wird nicht materialisiert und seine Definition wird an jeder Referenzstelle dupliziert. Das Inlining erfolgt anhand der folgenden Heuristiken:

  • Das CTE wird nicht mehr als einmal referenziert.
  • Das CTE enthält keine VOLATILE-Funktion.
  • Das CTE verwendet AS NOT MATERIALIZED und nicht AS MATERIALIZED.
  • Das CTE führt keine gruppierte Aggregation durch.

Die Materialisierung kann explizit aktiviert werden, indem das CTE mit AS MATERIALIZED definiert wird, und mit AS NOT MATERIALIZED deaktiviert werden. Beachten Sie, dass Inlining nicht immer möglich ist, selbst wenn die Heuristiken erfüllt sind. Enthält das CTE zum Beispiel eine Funktion read_csv, kann es nicht geinlined werden.

Nehmen Sie zum Beispiel die folgende Abfrage, die dasselbe CTE dreimal aufruft:

WITH t(x) AS (⟨complex_query⟩)
SELECT *
FROM
    t AS t1,
    t AS t2,
    t AS t3;

Inlining dupliziert die Definition von t für jede Referenz, was die folgende Abfrage ergibt:

SELECT *
FROM
    (⟨complex_query⟩) AS t1(x),
    (⟨complex_query⟩) AS t2(x),
    (⟨complex_query⟩) AS t3(x);

Ist complex_query teuer, kann die Materialisierung mit dem Schlüsselwort MATERIALIZED die Performance verbessern. In diesem Fall wird complex_query nur einmal ausgewertet.

WITH t(x) AS MATERIALIZED (⟨complex_query⟩)
SELECT *
FROM
    t AS t1,
    t AS t2,
    t AS t3;

Um die Materialisierung zu deaktivieren, verwenden Sie NOT MATERIALIZED:

WITH t(x) AS NOT MATERIALIZED (⟨complex_query⟩)
SELECT *
FROM
    t AS t1,
    t AS t2,
    t AS t3;

Im Allgemeinen wird nicht empfohlen, explizite Materialisierungshinweise zu verwenden, weil der Query-Optimizer von DuckDB anhand der Abfragestruktur und der oben genannten Heuristiken entscheiden kann, wann ein CTE materialisiert oder geinlined wird. In manchen Fällen kann es jedoch vorteilhaft sein, MATERIALIZED oder NOT MATERIALIZED zu verwenden, um das Verhalten explizit zu steuern.

Rekursive CTEs

WITH RECURSIVE erlaubt die Definition von CTEs, die sich selbst referenzieren können. Beachten Sie, dass die Abfrage so formuliert werden muss, dass die Termination sichergestellt ist, andernfalls kann sie in eine Endlosschleife geraten.

Beispiel: Fibonacci-Folge

WITH RECURSIVE kann für rekursive Berechnungen verwendet werden. So könnte WITH RECURSIVE zum Beispiel verwendet werden, um die ersten zehn Fibonacci-Zahlen zu berechnen:

WITH RECURSIVE FibonacciNumbers (
    RecursionDepth, FibonacciNumber, NextNumber
) AS (
        -- Base case
        SELECT
            0 AS RecursionDepth,
            0 AS FibonacciNumber,
            1 AS NextNumber
        UNION ALL
        -- Recursive step
        SELECT
            fib.RecursionDepth + 1 AS RecursionDepth,
            fib.NextNumber AS FibonacciNumber,
            fib.FibonacciNumber + fib.NextNumber AS NextNumber
        FROM
            FibonacciNumbers fib
        WHERE
            fib.RecursionDepth + 1 < 10
    )
SELECT
    fn.RecursionDepth AS FibonacciNumberIndex,
    fn.FibonacciNumber
FROM
    FibonacciNumbers fn;
FibonacciNumberIndex FibonacciNumber
0 0
1 1
2 1
3 2
4 3
5 5
6 8
7 13
8 21
9 34

Beispiel: Baumdurchlauf

WITH RECURSIVE kann zum Durchlaufen von Bäumen verwendet werden. Nehmen Sie zum Beispiel eine Hierarchie von Tags:

Beispielgraph Beispielgraph 
CREATE TABLE tag (id INTEGER, name VARCHAR, subclassof INTEGER);
INSERT INTO tag VALUES
    (1, 'U2',     5),
    (2, 'Blur',   5),
    (3, 'Oasis',  5),
    (4, '2Pac',   6),
    (5, 'Rock',   7),
    (6, 'Rap',    7),
    (7, 'Music',  9),
    (8, 'Movies', 9),
    (9, 'Art', NULL);

Die folgende Abfrage gibt den Pfad vom Knoten Oasis zur Wurzel des Baums (Art) zurück.

WITH RECURSIVE tag_hierarchy(id, source, path) AS (
        SELECT id, name, [name] AS path
        FROM tag
        WHERE subclassof IS NULL
    UNION ALL
        SELECT tag.id, tag.name, list_prepend(tag.name, tag_hierarchy.path)
        FROM tag, tag_hierarchy
        WHERE tag.subclassof = tag_hierarchy.id
    )
SELECT path
FROM tag_hierarchy
WHERE source = 'Oasis';
path
[Oasis, Rock, Music, Art]

Graphdurchlauf

Die Klausel WITH RECURSIVE kann verwendet werden, um Graphdurchlauf auf beliebigen Graphen auszudrücken. Hat der Graph jedoch Zyklen, muss die Abfrage Zyklenerkennung durchführen, um Endlosschleifen zu verhindern. Eine Möglichkeit dafür ist, den Pfad eines Durchlaufs in einer Liste zu speichern und vor dem Erweitern des Pfads um eine neue Kante zu prüfen, ob ihr Endpunkt bereits besucht wurde (siehe das spätere Beispiel).

Nehmen Sie den folgenden gerichteten Graphen aus dem LDBC-Graphalytics-Benchmark:

Beispielgraph Beispielgraph 
CREATE TABLE edge (node1id INTEGER, node2id INTEGER);
INSERT INTO edge VALUES
    (1, 3), (1, 5), (2, 4), (2, 5), (2, 10), (3, 1),
    (3, 5), (3, 8), (3, 10), (5, 3), (5, 4), (5, 8),
    (6, 3), (6, 4), (7, 4), (8, 1), (9, 4);

Beachten Sie, dass der Graph gerichtete Zyklen enthält, z. B. zwischen den Knoten 1, 5 und 8.

Alle Pfade von einem Knoten aufzählen

Die folgende Abfrage gibt alle Pfade zurück, die im Knoten 1 beginnen:

WITH RECURSIVE paths(startNode, endNode, path) AS (
        SELECT -- Define the path as the first edge of the traversal
            node1id AS startNode,
            node2id AS endNode,
            [node1id, node2id] AS path
        FROM edge
        WHERE startNode = 1
        UNION ALL
        SELECT -- Concatenate new edge to the path
            paths.startNode AS startNode,
            node2id AS endNode,
            array_append(path, node2id) AS path
        FROM paths
        JOIN edge ON paths.endNode = node1id
        -- Prevent adding a repeated node to the path.
        -- This ensures that no cycles occur.
        WHERE list_position(paths.path, node2id) IS NULL
    )
SELECT startNode, endNode, path
FROM paths
ORDER BY length(path), path;
startNode endNode path
1 3 [1, 3]
1 5 [1, 5]
1 5 [1, 3, 5]
1 8 [1, 3, 8]
1 10 [1, 3, 10]
1 3 [1, 5, 3]
1 4 [1, 5, 4]
1 8 [1, 5, 8]
1 4 [1, 3, 5, 4]
1 8 [1, 3, 5, 8]
1 8 [1, 5, 3, 8]
1 10 [1, 5, 3, 10]

Beachten Sie, dass das Ergebnis dieser Abfrage nicht auf kürzeste Pfade beschränkt ist, z. B. enthalten die Ergebnisse für Knoten 5 die Pfade [1, 5] und [1, 3, 5].

Ungewichtete kürzeste Pfade von einem Knoten aufzählen

In den meisten Fällen ist das Aufzählen aller Pfade nicht praktikabel oder machbar. Stattdessen sind nur die (ungewichteten) kürzesten Pfade von Interesse. Um diese zu finden, sollte die zweite Hälfte der WITH RECURSIVE-Abfrage so angepasst werden, dass ein Knoten nur aufgenommen wird, wenn er noch nicht besucht wurde. Das wird durch eine Unterabfrage umgesetzt, die prüft, ob einer der vorherigen Pfade den Knoten enthält:

WITH RECURSIVE paths(startNode, endNode, path) AS (
        SELECT -- Define the path as the first edge of the traversal
            node1id AS startNode,
            node2id AS endNode,
            [node1id, node2id] AS path
        FROM edge
        WHERE startNode = 1
        UNION ALL
        SELECT -- Concatenate new edge to the path
            paths.startNode AS startNode,
            node2id AS endNode,
            array_append(path, node2id) AS path
        FROM paths
        JOIN edge ON paths.endNode = node1id
        -- Prevent adding a node that was visited previously by any path.
        -- This ensures that (1) no cycles occur and (2) only nodes that
        -- were not visited by previous (shorter) paths are added to a path.
        WHERE NOT EXISTS (
                FROM paths previous_paths
                WHERE list_contains(previous_paths.path, node2id)
              )
    )
SELECT startNode, endNode, path
FROM paths
ORDER BY length(path), path;
startNode endNode path
1 3 [1, 3]
1 5 [1, 5]
1 8 [1, 3, 8]
1 10 [1, 3, 10]
1 4 [1, 5, 4]
1 8 [1, 5, 8]

Ungewichtete kürzeste Pfade zwischen zwei Knoten aufzählen

WITH RECURSIVE kann auch verwendet werden, um alle (ungewichteten) kürzesten Pfade zwischen zwei Knoten zu finden. Um sicherzustellen, dass die rekursive Abfrage gestoppt wird, sobald wir den Endknoten erreichen, verwenden wir eine Fensterfunktion, die prüft, ob der Endknoten unter den neu hinzugefügten Knoten ist.

Die folgende Abfrage gibt alle ungewichteten kürzesten Pfade zwischen den Knoten 1 (Startknoten) und 8 (Endknoten) zurück:

WITH RECURSIVE paths(startNode, endNode, path, endReached) AS (
   SELECT -- Define the path as the first edge of the traversal
        node1id AS startNode,
        node2id AS endNode,
        [node1id, node2id] AS path,
        (node2id = 8) AS endReached
     FROM edge
     WHERE startNode = 1
   UNION ALL
   SELECT -- Concatenate new edge to the path
        paths.startNode AS startNode,
        node2id AS endNode,
        array_append(path, node2id) AS path,
        max(CASE WHEN node2id = 8 THEN 1 ELSE 0 END)
            OVER (ROWS BETWEEN UNBOUNDED PRECEDING
                           AND UNBOUNDED FOLLOWING) AS endReached
     FROM paths
     JOIN edge ON paths.endNode = node1id
    WHERE NOT EXISTS (
            FROM paths previous_paths
            WHERE list_contains(previous_paths.path, node2id)
          )
      AND paths.endReached = 0
)
SELECT startNode, endNode, path
FROM paths
WHERE endNode = 8
ORDER BY length(path), path;
startNode endNode path
1 8 [1, 3, 8]
1 8 [1, 5, 8]

Zugriff auf die Union-Tabelle mit recurring

Im rekursiven Term eines WITH RECURSIVE-CTE bezieht sich der CTE-Name (z. B. counter) auf die Zeilen, die von der letzten Iteration erzeugt wurden. Um auf alle bisher akkumulierten Zeilen (die Union-Tabelle) zuzugreifen, verwenden Sie das Schema-Präfix recurring:

WITH RECURSIVE counter(i) AS (
    SELECT 1
        UNION ALL
    SELECT i + 1
    FROM counter
    WHERE (SELECT max(i) FROM recurring.counter) < 5
)
SELECT *
FROM counter;
i
1
2
3
4
5

Hier gibt recurring.counter Zugriff auf alle über alle vorherigen Iterationen akkumulierten Zeilen, während counter in der FROM-Klausel nur die Zeilen der jüngsten Iteration enthält. Das ist nützlich, wenn Abbruchbedingungen oder Berechnungen vom gesamten akkumulierten Ergebnis abhängen und nicht nur von der vorherigen Iteration.

Rekursive CTEs mit USING KEY

Veraltet DuckDB 1.5.0 hat die Verwendung rekursiver UNIONs für USING KEY-CTEs zugunsten rekursiver UNION ALLs veraltet.

Die rekursiven UNIONs implizieren, dass nicht alle in einer Iteration erzeugten Zeilen an die nächste weitergegeben werden, wie es bei regulären rekursiven CTEs der Fall wäre. Da das Gegenteil zutrifft, d. h. alle Zeilen von einer Iteration an die nächste weitergegeben werden, verlangen DuckDBs USING KEY-CTEs künftig stattdessen rekursive UNION ALLs.

DuckDB 1.5.0 führt außerdem eine neue Einstellung ein, um die USING KEY-Syntax zu konfigurieren.

SET deprecated_using_key_syntax = 'DEFAULT';
SET deprecated_using_key_syntax = 'UNION_AS_UNION_ALL';

Derzeit aktiviert DEFAULT beide Syntaxstile, d. h. erlaubt sowohl rekursive UNIONs als auch rekursive UNION ALLs in USING KEY-CTEs.

DuckDB 1.5.0 ist die letzte Version, die die UNION-Syntax ohne explizites Aktivieren unterstützt.

DuckDB 2.0.0 deaktiviert die UNION-Syntax standardmäßig.

DuckDB 2.1.0 entfernt das Flag deprecated_using_key_syntax und veraltet die UNION-Syntax vollständig.

USING KEY ändert das Verhalten eines regulären rekursiven CTEs.

In jeder Iteration hängt ein reguläres rekursives CTE Ergebniszeilen an die Union-Tabelle an, die letztlich das Gesamtergebnis des CTEs definiert. Im Gegensatz dazu kann ein CTE mit USING KEY Zeilen aktualisieren, die in einer früheren Iteration in die Union-Tabelle gelegt wurden: Erzeugt die aktuelle Iteration eine Zeile mit Schlüssel k, ersetzt sie eine Zeile mit demselben Schlüssel k in der Union-Tabelle (wie ein Dictionary). Existiert noch keine solche Zeile in der Union-Tabelle, wird die neue Zeile wie üblich an die Union-Tabelle angehängt.

Das erlaubt einem CTE, die Inhalte der Union-Tabelle fein zu steuern. Das Vermeiden des Nur-Anhängen-Verhaltens kann zu deutlich kleineren Union-Tabellen führen. Das hilft der Abfragelaufzeit, dem Speicherverbrauch und macht es machbar, während der laufenden Iteration auf die Union-Tabelle zuzugreifen. In einem CTE WITH RECURSIVE T(...) USING KEY ... bezeichnet die Tabelle T die von der letzten Iteration hinzugefügten Zeilen (wie bei rekursiven CTEs üblich), während die Tabelle recurring.T die bisher aufgebaute Union-Tabelle bezeichnet. Referenzen auf recurring.T erlauben die elegante und idiomatische Übersetzung eher komplexer Algorithmen in lesbaren SQL-Code.

Beispiel: USING KEY

Das ist ein rekursives CTE, bei dem USING KEY eine Schlüsselspalte (a) und eine Payload-Spalte (b) hat. Die Payload-Spalten entsprechen den zu überschreibenden Spalten. In der ersten Iteration haben wir zwei verschiedene Schlüssel, 1 und 2. Diese beiden Schlüssel erzeugen zwei neue Zeilen, (1, 3) und (2, 4). In der nächsten Iteration erzeugen wir einen neuen Schlüssel, 3, der eine neue Zeile erzeugt. Wir erzeugen auch die Zeile (2, 3), wobei 2 ein Schlüssel ist, der bereits aus der vorherigen Iteration existiert. Das überschreibt den alten Payload 4 mit dem neuen Payload 3.

WITH RECURSIVE tbl(a, b) USING KEY (a) AS (
    SELECT a, b
    FROM (VALUES (1, 3), (2, 4)) t(a, b)
        UNION ALL
    SELECT a + 1, b
    FROM tbl
    WHERE a < 3
)
SELECT *
FROM tbl;
a b
1 3
2 3
3 3

VALUES verwenden

Sie können die VALUES-Klausel für den initialen (Anker-)Teil des CTEs verwenden:

WITH RECURSIVE tbl(a, b) USING KEY (a) AS (
    VALUES (1, 3), (2, 4)
        UNION ALL
    SELECT a + 1, b
    FROM tbl
    WHERE a < 3
)
SELECT *
FROM tbl;

Beispiel: USING KEY referenziert die Union-Tabelle

Neben der Verwendung der Union-Tabelle als Dictionary können wir sie jetzt in Abfragen referenzieren. Das erlaubt es, Ergebnisse nicht nur der vorherigen Iteration, sondern auch früherer zu verwenden. Dieses neue Feature erleichtert die Umsetzung bestimmter Algorithmen.

Ein Beispiel ist der Connected-Components-Algorithmus. Für jeden Knoten bestimmt der Algorithmus den Knoten mit der niedrigsten ID, mit dem er verbunden ist. Dazu verwenden wir die Einträge in der Union-Tabelle, um die niedrigste für einen Knoten gefundene ID zu verfolgen. Enthält eine neue eingehende Zeile eine niedrigere ID, aktualisieren wir diesen Wert.

Beispielgraph Beispielgraph 
CREATE TABLE nodes (id INTEGER);
INSERT INTO nodes VALUES (1), (2), (3), (4), (5), (6), (7), (8);
CREATE TABLE edges (node1id INTEGER, node2id INTEGER);
INSERT INTO edges VALUES
    (1, 3), (2, 3), (3, 7), (7, 8), (5, 4), (6, 4);
WITH RECURSIVE connected_components(id, comp) USING KEY (id) AS (
    SELECT n.id, n.id AS comp
    FROM nodes AS n
        UNION ALL (
    SELECT DISTINCT ON (previous_iter.id) previous_iter.id, initial_iter.comp
    FROM
        recurring.connected_components AS previous_iter,
        connected_components AS initial_iter,
        edges AS e
    WHERE ((e.node1id, e.node2id) = (previous_iter.id, initial_iter.id)
       OR (e.node2id, e.node1id) = (previous_iter.id, initial_iter.id))
      AND initial_iter.comp < previous_iter.comp
    ORDER BY initial_iter.id ASC, previous_iter.comp ASC)
)
TABLE connected_components
ORDER BY id;
id comp
1 1
2 1
3 1
4 4
5 4
6 4
7 1
8 1

Einschränkungen

DuckDB unterstützt keine gegenseitig rekursiven CTEs. Siehe das zugehörige Issue und die Diskussion im DuckDB-Repository.

Syntax

Dies ist eine inoffizielle Website und nicht mit DuckDB verbunden. Offizielle Seite:duckdb.org.duckdb.ubitools.com · Übersetzt und erstellt mit Astro und daisyUI