PostgreSQL: Dokumentation: 13: 7.8. MIT Abfragen (allgemeine Tabellenausdrücke)

Der Grundwert von SELECT in WITH besteht darin, komplizierte Abfragen in einfachere Teile zu zerlegen. Ein Beispiel ist:

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_salesFROM ordersWHERE region IN (SELECT region FROM top_regions)GROUP BY region, product;

hier werden die Gesamtumsätze pro Produkt nur in den obersten Verkaufsregionen angezeigt. Die Klausel WITH definiert zwei Hilfsanweisungen mit den Namen regional_sales und top_regions, wobei die Ausgabe von regional_sales in top_regions und die Ausgabe von top_regions in der primären Abfrage SELECT verwendet wird. Dieses Beispiel hätte ohne WITH geschrieben werden können, aber wir hätten zwei Ebenen verschachtelter Sub-SELECT s benötigt.

Der optionale Modifikator RECURSIVE ändert WITH von einer reinen syntaktischen Bequemlichkeit in eine Funktion, die Dinge erreicht, die in Standard-SQL sonst nicht möglich sind. Mit RECURSIVE kann eine WITH -Abfrage auf ihre eigene Ausgabe verweisen. Ein sehr einfaches Beispiel ist diese Abfrage, um die ganzen Zahlen von 1 bis 100 zu summieren:

WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100)SELECT sum(n) FROM t;

Die allgemeine Form einer rekursiven WITH -Abfrage ist immer ein nicht-rekursiver Term, dann UNION (oder UNION ALL), dann ein rekursiver Term, wobei nur der rekursive Term einen Verweis auf die eigene Ausgabe der Abfrage enthalten kann. Eine solche Abfrage wird wie folgt ausgeführt:

Im obigen Beispiel hat die Arbeitstabelle in jedem Schritt nur eine einzige Zeile und nimmt in aufeinanderfolgenden Schritten die Werte von 1 bis 100 an. Im 100. Schritt wird aufgrund der WHERE -Klausel keine Ausgabe ausgegeben, sodass die Abfrage beendet wird.

Rekursive Abfragen werden normalerweise verwendet, um mit hierarchischen oder baumstrukturierten Daten umzugehen. Ein nützliches Beispiel ist diese Abfrage, um alle direkten und indirekten Unterteile eines Produkts zu finden, wobei nur eine Tabelle angegeben wird, die unmittelbare Einschlüsse anzeigt:

WITH RECURSIVE included_parts(sub_part, part, quantity) AS ( SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part, p.quantity FROM included_parts pr, parts p WHERE p.part = pr.sub_part)SELECT sub_part, SUM(quantity) as total_quantityFROM included_partsGROUP BY sub_part

Wenn Sie mit rekursiven Abfragen arbeiten, müssen Sie sicherstellen, dass der rekursive Teil der Abfrage letztendlich keine Tupel zurückgibt, da die Abfrage sonst unbegrenzt wiederholt wird. Manchmal kann die Verwendung von UNION anstelle von UNION ALL dies erreichen, indem Zeilen verworfen werden, die vorherige Ausgabezeilen duplizieren. Häufig handelt es sich bei einem Zyklus jedoch nicht um vollständig doppelte Ausgabezeilen: Es kann erforderlich sein, nur ein oder einige Felder zu überprüfen, um festzustellen, ob derselbe Punkt zuvor erreicht wurde. Die Standardmethode zur Behandlung solcher Situationen besteht darin, ein Array der bereits besuchten Werte zu berechnen. Betrachten Sie beispielsweise die folgende Abfrage, die eine Tabelle graph mit einem link -Feld durchsucht:

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link)SELECT * FROM search_graph;

Diese Abfrage wird wiederholt, wenn die link -Beziehungen Zyklen enthalten. Da wir eine “Tiefenausgabe” benötigen, würde das Ändern von UNION ALL in UNION die Schleife nicht beseitigen. Stattdessen müssen wir erkennen, ob wir dieselbe Zeile erneut erreicht haben, während wir einem bestimmten Linkpfad folgen. Wir fügen der schleifenanfälligen Abfrage zwei Spalten path und cycle hinzu:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Abgesehen davon, dass Zyklen verhindert werden, ist der Array-Wert häufig für sich genommen nützlich, um den “Pfad” darzustellen, der zum Erreichen einer bestimmten Zeile verwendet wird.

Im allgemeinen Fall, in dem mehr als ein Feld überprüft werden muss, um einen Zyklus zu erkennen, verwenden Sie ein Array von Zeilen. Zum Beispiel, wenn wir die Felder f1 und f2:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1, ARRAY, false FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || ROW(g.f1, g.f2), ROW(g.f1, g.f2) = ANY(path) FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle)SELECT * FROM search_graph;

Ein hilfreicher Trick zum Testen von Abfragen, wenn Sie nicht sicher sind, ob sie eine Schleife bilden könnten, besteht darin, eine LIMIT in die übergeordnete Abfrage einzufügen. Zum Beispiel würde diese Abfrage für immer ohne die Schleife LIMIT:

WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t)SELECT n FROM t LIMIT 100;

Dies funktioniert, weil die PostgreSQL-Implementierung nur so viele Zeilen einer WITH -Abfrage auswertet, wie tatsächlich von der übergeordneten Abfrage abgerufen werden. Die Verwendung dieses Tricks in der Produktion wird nicht empfohlen, da andere Systeme möglicherweise anders funktionieren. Außerdem funktioniert es normalerweise nicht, wenn Sie die äußere Abfrage dazu bringen, die Ergebnisse der rekursiven Abfrage zu sortieren oder sie mit einer anderen Tabelle zu verknüpfen, da in solchen Fällen die äußere Abfrage normalerweise versucht, die gesamte Ausgabe der WITH -Abfrage abzurufen.

Eine nützliche Eigenschaft von WITH -Abfragen besteht darin, dass sie normalerweise nur einmal pro Ausführung der übergeordneten Abfrage ausgewertet werden, auch wenn sie von der übergeordneten Abfrage oder den WITH -Geschwisterabfragen mehr als einmal referenziert werden. So können teure Berechnungen, die an mehreren Stellen benötigt werden, innerhalb einer WITH -Abfrage platziert werden, um redundante Arbeit zu vermeiden. Eine weitere mögliche Anwendung besteht darin, unerwünschte Mehrfachauswertungen von Funktionen mit Nebenwirkungen zu verhindern. Die andere Seite dieser Medaille ist jedoch, dass der Optimierer nicht in der Lage ist, Einschränkungen von der übergeordneten Abfrage in eine mehrfach referenzierte WITH -Abfrage zu verschieben, da dies alle Verwendungen der Ausgabe der WITH -Abfrage betreffen kann, wenn sie nur eine betreffen sollte. Die mehrfach referenzierte WITH -Abfrage wird wie geschrieben ausgewertet, ohne dass Zeilen unterdrückt werden, die die übergeordnete Abfrage anschließend möglicherweise verwirft. (Wie oben erwähnt, kann die Auswertung jedoch vorzeitig beendet werden, wenn die Verweise auf die Abfrage nur eine begrenzte Anzahl von Zeilen erfordern.)

Wenn eine WITH -Abfrage jedoch nicht rekursiv und nebenwirkungsfrei ist (dh eine SELECT enthält keine flüchtigen Funktionen), kann sie in die übergeordnete Abfrage gefaltet werden, was eine gemeinsame Optimierung der beiden Abfrageebenen ermöglicht. Standardmäßig geschieht dies, wenn die übergeordnete Abfrage nur einmal auf die Abfrage WITH verweist, nicht jedoch, wenn sie mehr als einmal auf die Abfrage WITH verweist. Sie können diese Entscheidung überschreiben, indem Sie MATERIALIZED angeben, um eine separate Berechnung der WITH -Abfrage zu erzwingen, oder indem Sie NOT MATERIALIZED angeben, um die Zusammenführung mit der übergeordneten Abfrage zu erzwingen. Die letztere Option riskiert eine doppelte Berechnung der WITH -Abfrage, kann jedoch dennoch zu Nettoeinsparungen führen, wenn jede Verwendung der WITH -Abfrage nur einen kleinen Teil der vollständigen Ausgabe der WITH -Abfrage benötigt.

Ein einfaches Beispiel für diese Regeln ist

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w WHERE key = 123;

Diese WITH Abfrage wird gefaltet und erzeugt den gleichen Ausführungsplan wie

SELECT * FROM big_table WHERE key = 123;

Insbesondere wenn es einen Index für keygibt, wird er wahrscheinlich verwendet, um nur die Zeilen mit key = 123 abzurufen. Andererseits wird in

WITH w AS ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

die WITH —Abfrage materialisiert, wodurch eine temporäre Kopie von big_table erzeugt wird, die dann mit sich selbst verbunden wird – ohne den Vorteil eines Indexes. Diese Abfrage wird viel effizienter ausgeführt, wenn sie als

WITH w AS NOT MATERIALIZED ( SELECT * FROM big_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.refWHERE w2.key = 123;

geschrieben wird, sodass die Einschränkungen der übergeordneten Abfrage direkt auf Scans von big_table angewendet werden können.

Ein Beispiel, in dem NOT MATERIALIZED unerwünscht sein könnte, ist

WITH w AS ( SELECT key, very_expensive_function(val) as f FROM some_table)SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

Hier stellt die Materialisierung der WITH -Abfrage sicher, dass very_expensive_function nur einmal pro Tabellenzeile und nicht zweimal ausgewertet wird.

Die obigen Beispiele zeigen nur, dass WITH mit SELECT verwendet wird, aber es kann auf die gleiche Weise an INSERT, UPDATE oder DELETE angehängt werden. In jedem Fall werden temporäre Tabellen bereitgestellt, auf die im Hauptbefehl verwiesen werden kann.