7.8. WITH-Abfragen (Gemeinsame Tabellenausdrücke)

7.8.1. SELECT in WITH

Der grundlegende Wert 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;

, das die Gesamtumsätze pro Produkt nur in den wichtigsten Verkaufsregionen anzeigt. Die WITH-Klausel definiert zwei Hilfsanweisungen namens regional_sales und top_regions, wobei die Ausgabe von regional_sales in top_regions und die Ausgabe von top_regions in der primären SELECT-Abfrage verwendet wird. Dieses Beispiel hätte auch ohne WITH geschrieben werden können, aber dann hätten wir zwei Ebenen von verschachtelten Sub-SELECTs benötigt.

Der optionale Modifikator RECURSIVE verwandelt WITH von einer bloßen syntaktischen Bequemlichkeit in eine Funktion, die Dinge ermöglicht, die in Standard-SQL 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 sie nimmt in aufeinanderfolgenden Schritten die Werte von 1 bis 100 an. Im 100. Schritt gibt es aufgrund der WHERE-Klausel keine Ausgabe und die Abfrage bricht ab.

Rekursive Abfragen werden typischerweise 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 gegeben ist, die unmittelbare Einschlüsse zeigt:

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

Beim Arbeiten mit rekursiven Abfragen ist es wichtig, sicherzustellen, dass der rekursive Teil der Abfrage schließlich keine Tupel zurückgibt, da die Abfrage sonst eine Endlosschleife bildet. Manchmal kann dies durch die Verwendung von UNION anstelle von UNION ALL erreicht werden, indem Zeilen verworfen werden, die vorherige Ausgabezeilen duplizieren. Oft beinhaltet ein Zyklus jedoch keine vollständig doppelten Ausgabezeilen: Es kann notwendig sein, nur ein oder einige wenige Felder zu überprüfen, um festzustellen, ob derselbe Punkt schon einmal erreicht wurde. Die Standardmethode für solche Situationen besteht darin, ein Array der bereits besuchten Werte zu berechnen. Betrachten Sie zum Beispiel die folgende Abfrage, die ein Tabellendiagramm anhand eines Verknüpfungsfeldes 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 führt zu einer Schleife, wenn die Verknüpfungsbeziehungen Zyklen enthalten. Da wir eine „tiefe“ Ausgabe benötigen, reicht es nicht aus, UNION ALL in UNION zu ändern, um die Schleifen zu vermeiden. Stattdessen müssen wir erkennen, ob wir dieselbe Zeile erneut erreicht haben, während wir einem bestimmten Pfad von Verknüpfungen folgten. Wir fügen der schleifenanfälligen Abfrage zwei Spalten Pfad und Zyklus 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 von der Verhinderung von Zyklen ist der Array-Wert oft schon für sich genommen nützlich, da er den „Pfad“ darstellt, der genommen wurde, um eine bestimmte Zeile zu erreichen.

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 vergleichen müssen:

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, bei denen Sie nicht sicher sind, ob sie eine Schleife bilden könnten, besteht darin, ein LIMIT in die übergeordnete Abfrage einzufügen. Zum Beispiel würde diese Abfrage ohne LIMIT eine Endlosschleife bilden:

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. Es wird nicht empfohlen, diesen Trick in der Produktion zu verwenden, da andere Systeme möglicherweise anders arbeiten. Außerdem funktioniert er 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 versuchen wird, die gesamte Ausgabe der WITH-Abfrage zu holen.

Eine nützliche Eigenschaft von WITH-Abfragen ist, dass sie nur einmal pro Ausführung der übergeordneten Abfrage ausgewertet werden, auch wenn sie mehr als einmal von der übergeordneten Abfrage oder von WITH-Geschwisterabfragen angesprochen 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 Nebeneffekten zu verhindern. Die Kehrseite der Medaille ist jedoch, dass der Optimierer weniger in der Lage ist, Einschränkungen aus der übergeordneten Abfrage in eine WITH-Abfrage zu übertragen als eine gewöhnliche Unterabfrage. Die WITH-Abfrage wird in der Regel so ausgewertet, wie sie geschrieben wurde, ohne Unterdrückung von Zeilen, die die übergeordnete Abfrage danach möglicherweise verwirft. (Aber, wie oben erwähnt, kann die Auswertung früh beendet werden, wenn der/die Verweis(e) auf die Abfrage nur eine begrenzte Anzahl von Zeilen erfordert/erfordern.)

Die obigen Beispiele zeigen nur die Verwendung von WITH mit SELECT, aber es kann auf die gleiche Weise mit INSERT, UPDATE oder DELETE verbunden werden. In jedem Fall stellt es temporäre Tabellen bereit, auf die im Hauptbefehl Bezug genommen werden kann.