7.8. Dotazy WITH (společné tabulkové výrazy)

7.8.1. SELECT ve WITH

Základním významem SELECT ve WITH je rozložit složité dotazy na jednodušší části. Příkladem může být:

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ý zobrazí součty prodejů za jednotlivé produkty pouze v nejvyšších prodejních oblastech. Klauzule WITH definuje dva pomocné příkazy s názvy regional_sales a top_regions, přičemž výstup regional_sales se použije v top_regions a výstup top_regions se použije v primárním dotazu SELECT. Tento příklad by bylo možné napsat bez příkazu WITH, ale potřebovali bychom dvě úrovně vnořených dílčích příkazů SELECT. Takto je to o něco přehlednější.

Povinný modifikátor RECURSIVE mění WITH z pouhé syntaktické vymoženosti na vlastnost, která umožňuje dosáhnout věcí, které jinak ve standardním SQL nejsou možné. Pomocí modifikátoru RECURSIVE může dotaz WITH odkazovat na svůj vlastní výstup. Velmi jednoduchým příkladem je tento dotaz na 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ý tvar rekurzivního dotazu WITH je vždy nerekurzivní výraz, pak UNION (nebo UNION ALL), pak rekurzivní výraz, přičemž pouze rekurzivní výraz může obsahovat odkaz na vlastní výstup dotazu. Takový dotaz se provede takto:

V uvedeném příkladu má pracovní tabulka v každém kroku jen jeden řádek a v postupných krocích nabývá hodnot od 1 do 100.

Poznámka: Tento postup je iterací, nikoli rekurzí. Ve 100. kroku není žádný výstup kvůli klauzuli WHERE, a tak se dotaz ukončí.

Rekurzivní dotazy se obvykle používají pro práci s hierarchickými nebo stromově strukturovanými daty. Užitečným příkladem je tento dotaz na vyhledání všech přímých a nepřímých podčástí součinu, přičemž je dána pouze tabulka, která zobrazuje bezprostřední 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ími dotazy je důležité mít jistotu, že rekurzivní část dotazu nakonec nevrátí žádné tuply, jinak se dotaz zacyklí do nekonečna. Někdy toho lze dosáhnout použitím funkce UNION místo UNION ALL tím, že se vyřadí řádky, které duplikují předchozí výstupní řádky. Často však cyklus nezahrnuje výstupní řádky, které jsou zcela duplicitní: může být nutné zkontrolovat pouze jedno nebo několik polí, aby se zjistilo, zda již bylo dosaženo stejného bodu. Standardní metodou pro řešení takových situací je výpočet pole již navštívených hodnot. Uvažujme například následující dotaz, který prohledává graf tabulky pomocí pole odkazů:

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 se zacyklí, pokud vztahy odkazů obsahují cykly. Protože požadujeme výstup „do hloubky“, pouhá změna UNION ALL na UNION by zacyklení neodstranila. Místo toho potřebujeme rozpoznat, zda jsme se při sledování určité cesty vazeb dostali znovu na stejný řádek. Do dotazu náchylného k cyklům přidáme dva sloupce cesta a cyklus:

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ě zabránění cyklům je hodnota pole často užitečná sama o sobě, protože reprezentuje „cestu“, kterou jsme urazili, abychom dosáhli nějakého konkrétního řádku.

V obecném případě, kdy je třeba zkontrolovat více než jedno pole, abychom rozpoznali cyklus, použijte pole řádků. Pokud bychom například 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ů, když si nejste jisti, zda se mohou zacyklit, je umístit do nadřazeného dotazu LIMIT. Například tento dotaz by se bez LIMITu zacyklil navždy:

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

Toto funguje, protože implementace PostgreSQL vyhodnocuje pouze tolik řádků dotazu WITH, kolik je skutečně načteno nadřazeným dotazem. Použití tohoto triku v produkčním prostředí se nedoporučuje, protože jiné systémy mohou fungovat jinak. Obvykle také nebude fungovat, pokud přimějete vnější dotaz třídit výsledky rekurzivního dotazu nebo je připojit k nějaké jiné tabulce, protože v takových případech se vnější dotaz obvykle stejně pokusí načíst všechny výstupy dotazu WITH.

Užitečnou vlastností dotazů WITH je, že se vyhodnocují pouze jednou za provedení nadřazeného dotazu, i když se na ně nadřazený dotaz nebo sourozenecké dotazy WITH odkazují více než jednou. Nákladné výpočty, které jsou potřeba na více místech, tak mohou být umístěny do dotazu WITH, aby se zabránilo nadbytečné práci. Dalším možným použitím je zabránit nežádoucímu vícenásobnému vyhodnocování funkcí s vedlejšími efekty. Druhou stranou této mince však je, že optimalizátor je méně schopný posunout omezení z nadřazeného dotazu dolů do dotazu WITH než do běžného poddotazu. Dotaz WITH bude zpravidla vyhodnocen tak, jak je napsán, bez potlačení řádků, které by nadřazený dotaz mohl následně vyřadit. (Ale jak bylo uvedeno výše, vyhodnocování se může zastavit dříve, pokud odkaz(y) na dotaz vyžadují pouze omezený počet řádků.)

Výše uvedené příklady ukazují použití WITH pouze s dotazem SELECT, ale stejným způsobem jej lze připojit i k dotazům INSERT, UPDATE nebo DELETE. V každém případě efektivně poskytuje dočasnou(é) tabulku(y), na kterou(é) lze odkazovat v hlavním příkazu.