Spaß mit SQL: Rekursive CTEs in Postgres

Common Table Expressions (CTEs) sind ein leistungsfähiges Konstrukt in SQL. Im täglichen Gespräch hören Sie möglicherweise CTEs, die als WITH -Klauseln bezeichnet werden. Sie können sich CTEs wie eine Ansicht vorstellen, die nur während der Ausführung dieser Abfrage materialisiert wird und außerhalb dieser Abfrage nicht vorhanden ist. CTEs können sehr nützliche Bausteine sein, um Ihre großen SQL-Abfragen besser lesbar zu machen. Sie können aber auch rekursiv verwendet werden, sodass Sie einige sehr komplexe Abfragen erstellen können, ohne auf eine prozedurale Sprache wie plpgsql oder plv8 zurückgreifen zu müssen.

Rekursive CTEs lassen sich aufrufen, bis eine Bedingung erfüllt ist. Lassen Sie uns direkt hineinspringen und einen rekursiven CTE erkunden – einen grundlegenden und natürlich PostgreSQL – und dann den rekursiven CTE etwas weiter zerlegen, um zu sehen, wofür wir ihn verwenden können:

WITH RECURSIVE tens (n) AS ( SELECT 10 UNION ALL SELECT n+10 FROM tens WHERE n+10<= 100 ) SELECT n FROM tens; 

Wenn das Obige ausgeführt wird, erhalten wir das folgende Ergebnis:

 n ----- 10 20 30 40 50 60 70 80 90 100 (10 rows) 

Mit dem obigen könnten wir dies auch leicht mit einer generate_series tun. Aber bleiben Sie bei uns und Sie werden die komplexeren Dinge sehen, die wir tun können, die mit generate_series nicht möglich sind. Schauen wir uns zunächst genauer an, wie es funktioniert.

Der erste Teil, den Sie bemerken werden, ist WITH RECURSIVE. Dies teilt Postgres mit, dass sich der CTE rekursiv selbst aufrufen kann. Der nächste Teil, den Sie bemerken werden, ist, dass einige Parameter berücksichtigt werden. In diesem Fall (n) kann es auch mehr als einen benötigen.

Wenn wir uns weiter in den CTE bewegen, haben wir die erste Abfrage, die ausgeführt wird, SELECT 10, die den ersten Wert generiert. Der zweite Teil ist, wo der ganze Spaß beginnt. Das UNION ALL gibt an, dass wir alle Datensätze zurückgeben, die aus der Schleife erzeugt werden. Dann ruft SELECT n+10 FROM tens WHERE n+10<= 100 den tens CTE auf, der erstellt wird, bis die Bedingung erfüllt ist.

Das sind also die Grundlagen, aber die interessante Frage ist: Wann würden Sie einen rekursiven CTE verwenden? Wenn Sie eine Baumstruktur oder eine hierarchische Struktur für Ihre Daten haben, können rekursive CTEs das Leben viel einfacher machen, als alle Ihre Daten zu laden und eine Schleife in Ihrem Code auszuführen. Für Anwendungen, die sich mit E-Commerce- und Einkaufskategorien befassen, sind rekursive CTEs eine große Hilfe.

Versuchen wir, die Vorteile rekursiver CTEs in Postgres anhand eines Beispiels etwas konkreter zu machen. Zuerst werden wir eine Tabelle mit Mitarbeitern erstellen, dann werden wir einige Beispielmitarbeiter laden. (Ja, Sie werden einige Namen aus unserem Team hier bei Citus Data auf dieser Liste sehen. Das waren die ersten Namen, die mir in den Sinn kamen.)

CREATE TABLE employees ( id serial, name varchar(255), manager_id int ); INSERT INTO employees VALUES (1, 'Umur', null); INSERT INTO employees VALUES (2, 'Craig', 1); INSERT INTO employees VALUES (3, 'Daniel', 2); INSERT INTO employees VALUES (4, 'Claire', 1); INSERT INTO employees VALUES (5, 'Lindsay', 2); INSERT INTO employees VALUES (6, 'Will', 2); INSERT INTO employees VALUES (7, 'Burak', 2); INSERT INTO employees VALUES (8, 'Eren', 2); INSERT INTO employees VALUES (9, 'Katie', 3); INSERT INTO employees VALUES (10, 'Teresa', 4); 

Jetzt werde ich eine Abfrage schreiben, die mir alle Berichte gibt, die in einer bestimmten Organisation innerhalb des Unternehmens zusammengeführt werden. In diesem Fall erhalte ich mich und alle meine Berichte zusammen mit der Manager-ID jeder Person:

WITH RECURSIVE managertree AS ( SELECT id, name, manager_id FROM employees WHERE id = 2 UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e INNER JOIN managertree mtree ON mtree.id = e.manager_id ) SELECT * FROM managertree; id | name | manager_id ----+---------+------------ 2 | Craig | 1 3 | Daniel | 2 5 | Lindsay | 2 6 | Will | 2 7 | Burak | 2 8 | Eren | 2 9 | Katie | 3 (7 rows) 

Wenn Sie das nächste Mal rekursive Berechnungen für Ihre Daten durchführen müssen, sollten Sie dies direkt in SQL tun, anstatt alle diese Daten in Ihre Anwendung zu laden. Für weitere Informationen werfen Sie einen Blick auf einige dieser hilfreichen Ressourcen:

• PostgreSQL-Dokumente zu CTEs
• Lösen des Traveling Salesman-Problems mit einem CTE
• Einen Baum mit all seinen Kindern bekommen

Als Anmerkung sind CTEs zu diesem Zeitpunkt ein Optimierungszaun in PostgreSQL, obwohl es Hoffnungen gibt, dass sich dies in Zukunft ändern wird. Allgemeine Tabellenausdrücke sind ein unglaublich nützliches Werkzeug für die Berichterstellung. Manchmal überwiegt die Lesbarkeit von CTEs die Auswirkungen auf die Leistung, aber berücksichtigen Sie die Kompromisse, wenn Sie sie verwenden

• Spaß mit SQL
• Postgres
• Tipps