Zábava s SQL: rekurzivní CTE v Postgres

běžné tabulkové výrazy (CTE) jsou výkonným konstruktem v SQL. V každodenní konverzaci můžete slyšet CTE označované jako WITH klauzule. Můžete si CTEs jako podobný názor, který se zhmotnil jen chvíli, že dotaz je spuštěn a neexistuje mimo dotaz. CTE mohou být velmi užitečné stavební kameny pro umožnění větší SQL dotazy, které mají být čitelnější. Ale mohou být také použity rekurzivně, což vám umožní vytvořit některé velmi složité dotazy, aniž byste museli spadnout do procedurálního jazyka, jako je plpgsql nebo plv8.

rekurzivní CTE se umožňují volat, dokud není splněna určitá podmínka. Pojďme skočit přímo v a prozkoumat rekurzivní CTE—základní jeden, a PostgreSQL samozřejmě—a pak pojďme pitvat rekurzivní CTE trochu dál, aby viděli, co můžeme použít pro:

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

Když výše uvedené je běh dostaneme následující výsledek:

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

s výše uvedeným bychom to také mohli snadno udělat s generate_series. Ale držte se nás a uvidíte složitější věci, které můžeme udělat, které nejsou možné s generate_series. Nejprve se podívejme blíže na to, jak to funguje.

první část, kterou si všimnete, je WITH RECURSIVE. To říká Postgres CTE může rekurzivně volat sám. Další část, kterou si všimnete, je, že do ní vyžaduje některé parametry. V tomto případě (n) může také trvat více než jeden, pokud potřebujete.

pohybující se dále do CTE, máme první dotaz, který je spuštěn, SELECT 10, který generuje první hodnotu. Druhá část je místem, kde začíná veškerá zábava. UNION ALL určuje, že budeme vracet všechny záznamy, které jsou vyrobeny ze smyčky. Pak SELECT n+10 FROM tens WHERE n+10<= 100 bude nadále volat tens CTE, který je vytvořen, dokud není splněna podmínka.

takže to jsou základy, ale zajímavá otázka zní: kdy byste použili rekurzivní CTE? Když máte strom nebo hierarchickou strukturu vašich dat, rekurzivní CTE mohou život mnohem jednodušší než načtení všech dat a spuštění smyčky v kódu. Pro aplikace, které se zabývají kategoriemi elektronického obchodu a nakupování, jsou rekurzivní CTE velkou pomocí.

pokusme se o to, aby výhody rekurzivních CTE v Postgres byly trochu konkrétnější, s příkladem. Nejprve vytvoříme tabulku zaměstnanců, pak načteme několik příkladů zaměstnanců. (Ano, uvidíte některá jména z našeho týmu zde na Citus Data na tomto seznamu. Byla to křestní jména, která mě napadla.)

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); 

teď budu psát dotaz, který mi dává všechny zprávy, které se valí do určitého org v rámci společnosti. V tomto případě jdu si pro sebe a všechny mé zprávy, spolu s každou osobou je manažer id:

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) 

Příště budete muset udělat nějaké rekurzivní výpočet přes vaše data, zvažte to dělá přímo v SQL jako protiklad k načtení všech dat do vaší aplikace. Pro další čtení zvažte pohled na některé z těchto užitečných zdrojů:

• PostgreSQL docs na CTEs
• Řešit problém obchodního cestujícího se CTE
• strom s všechny jsou to děti,

Jako vědomí CTEs v této době jsou optimalizace plot v PostgreSQL, i když existuje naděje, že změna v budoucnu. Běžné výrazy tabulky jsou neuvěřitelně užitečným nástrojem pro podávání zpráv. V době, čitelnost CTEs převáží vliv na výkon, ale zvážit kompromisy při jejich použití

• zábava s sql
• Postgres
• tipy