PostgreSQL: 13: 7.8. S dotazy (Common Table Expressions)

základní hodnotou SELECT v WITH je rozdělení komplikovaných dotazů na jednodušší části. Příkladem je:

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;

který zobrazuje součty prodejů za produkt pouze v nejlepších prodejních regionech. WITH klauzule definuje dvě pomocné prohlášení jménem regional_sales a top_regions, kde výstup regional_sales se používá v top_regions a výstupu top_regions se používá v primární SELECT dotaz. Tento příklad by mohl být napsán bez WITH, ale potřebovali bychom dvě úrovně vnořených sub – SELECT s. Je to trochu jednodušší sledovat tímto způsobem.

volitelný modifikátor RECURSIVE změní WITH z pouhého syntaktického pohodlí na funkci, která dosahuje věcí, které nejsou jinak možné ve standardním SQL. Pomocí RECURSIVE může dotaz WITH odkazovat na vlastní výstup. Velmi jednoduchým příkladem je tento dotaz pro součet celých čísel od 1 do 100:

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

obecné podobě rekurzivní WITH dotaz je vždy non-rekurzivní termín, pak UNION (nebo UNION ALL), pak rekurzivní horizontu, kde jen rekurzivní výraz může obsahovat odkaz na dotaz je vlastní výstup. Takový dotaz se provádí následovně:

ve výše uvedeném příkladu má pracovní tabulka v každém kroku pouze jeden řádek a v postupných krocích přebírá hodnoty od 1 do 100. Ve 100. kroku neexistuje žádný výstup kvůli klauzuli WHERE, a tak dotaz končí.

rekurzivní dotazy se obvykle používají k řešení hierarchických nebo stromově strukturovaných dat. Užitečným příkladem je tento dotaz najít všechny přímé a nepřímé dílčích částí produktu, vzhledem k tomu, pouze tabulku, která ukazuje okamžitou inkluze:

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

Při práci s rekurzivní dotazy, je důležité, aby být jisti, že rekurzivní část dotazu se nakonec vrátí žádná n-tice, nebo jiný dotaz, bude smyčka na dobu neurčitou. Někdy může použití UNION namísto UNION ALL dosáhnout tím, že vyřadí řádky, které duplikují předchozí výstupní řádky. Nicméně, často cyklus nezahrnuje výstup řádky, které jsou zcela duplicitní: může být nutné zkontrolovat jeden nebo několik polí, jestli stejném místě byla předtím. Standardní metodou pro řešení takových situací je výpočet pole již navštívených hodnot. Zvažte například následující dotaz, který prohledá tabulku graph pomocí link pole:

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;

Tento dotaz bude informovat, pokud se link vztahy obsahují cykly. Protože požadujeme výstup “hloubka”, jen změna UNION ALL na UNION by nevylučovala opakování. Místo toho musíme rozpoznat, zda jsme znovu dosáhli stejného řádku při sledování konkrétní cesty odkazů. Přidáme dva sloupce path a cycle loop-náchylné dotaz:

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;

Kromě prevence cykly, pole, hodnota je často užitečné v jeho vlastní pravý jako zastupující “cestu” k dosažení nějaké konkrétní řádek.

v obecném případě, kdy je třeba zkontrolovat více než jedno pole, abyste rozpoznali cyklus, použijte pole řádků. Například, pokud jsme potřebovali porovnat pole f1 a 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;

užitečným trikem pro testování dotazů, pokud si nejste jisti, zda mohou smyčky, je umístit LIMIT do nadřazeného dotazu. Například, tento dotaz by smyčka navždy, bez LIMIT:

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

To funguje, protože PostgreSQL je provádění vyhodnocuje pouze tolik řádků, WITH dotaz, jak jsou skutečně přitažené za vlasy, mateřské dotaz. Použití tohoto triku ve výrobě se nedoporučuje, protože jiné systémy mohou fungovat jinak. Také to obvykle nebude fungovat, pokud provedete vnější dotaz třídit výsledky rekurzivního dotazu nebo je připojit k jiné tabulce, protože v takových případech se vnější dotaz obvykle pokusí načíst veškerý výstup dotazu WITH.

užitečných vlastností WITH dotazy je, že jsou obvykle hodnoceny pouze jednou na exekuce mateřské dotaz, i když jsou uvedené více než jednou mateřskou dotaz nebo sourozenec WITH dotazy. Drahé výpočty, které jsou potřebné na více místech, lze tedy umístit do dotazu WITH, aby se zabránilo redundantní práci. Další možnou aplikací je zabránit nežádoucímu vícenásobnému hodnocení funkcí s vedlejšími účinky. Nicméně, na druhé straně této mince je, že optimizer nebude schopen tlačit omezení z nadřazeného dotazu do množit odkazy WITH dotaz, protože to může mít vliv na všechny použití WITH výstup dotazu, když by to mělo mít vliv pouze jeden. Dotaz s více odkazy WITH bude vyhodnocen jako napsaný, bez potlačení řádků, které by Nadřazený dotaz mohl později zahodit. (Jak je však uvedeno výše, hodnocení se může zastavit dříve, Pokud odkaz(y) Na dotaz vyžaduje pouze omezený počet řádků.

pokud je však dotaz WITH rekurzivní a bez vedlejších účinků (to znamená, že je SELECT neobsahující žádné Volatilní funkce), lze jej složit do nadřazeného dotazu, což umožňuje společnou optimalizaci dvou úrovní dotazu. Ve výchozím nastavení se to stane, pokud Nadřazený dotaz odkazuje na dotaz WITH pouze jednou, ale ne pokud odkazuje na dotaz WITH více než jednou. Můžete přepsat toto rozhodnutí zadáním MATERIALIZED vynutit samostatný výpočet WITH dotaz, nebo zadáním NOT MATERIALIZED vynutit být sloučeny do mateřské dotaz. Druhá volba riskuje duplicitní výpočet dotazu WITH, ale stále může poskytnout čisté úspory, pokud každé použití dotazu WITH potřebuje pouze malou část úplného výstupu dotazu WITH.

jednoduchý příklad těchto pravidel je

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

WITH dotaz bude složen, produkující stejný plán spuštění jako

SELECT * FROM big_table WHERE key = 123;

zejména, pokud je index na key, to bude pravděpodobně být použit k načtení pouze řádky s key = 123. Na druhou stranu, v

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

WITH dotaz bude zhmotnil, produkující dočasnou kopii big_table, který je pak spojil s sám — bez nároku na jakýkoli index. Tento dotaz bude proveden mnohem efektivněji, pokud je zapsán jako

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;

, takže omezení nadřazeného dotazu lze použít přímo na skenování big_table.

příkladem, kde NOT MATERIALIZED může být nežádoucí, je

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;

zde materializace dotazu WITH zajišťuje, že very_expensive_function je vyhodnoceno pouze jednou na řádek tabulky, nikoli dvakrát.

výše uvedené příklady ukazují pouze použití WITH s SELECT, ale lze jej připojit stejným způsobem k INSERT, UPDATE nebo DELETE. V každém případě účinně poskytuje dočasné tabulky,na které lze odkazovat v hlavním příkazu.