PostgreSQL: Dokumentacja: 13: 7.8. Z zapytaniami (wspólne wyrażenia tabeli)

podstawową wartością SELECT w WITH jest podział skomplikowanych zapytań na prostsze części. Przykładem jest:

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;

który wyświetla sumy sprzedaży poszczególnych produktów tylko w najlepszych regionach sprzedaży. Klauzula WITH definiuje dwie pomocnicze instrukcje nazwane regional_sales i top_regions, gdzie wyjście regional_sales jest używane w top_regions, A wyjście top_regions jest używane w podstawowym zapytaniu SELECT. Ten przykład mógłby zostać napisany bez WITH, ale potrzebowalibyśmy dwóch poziomów zagnieżdżonego pod-SELECT s. jest to nieco łatwiejsze do naśladowania w ten sposób.

opcjonalny modyfikator RECURSIVE zmienia WITHze zwykłej wygody składniowej w funkcję, która realizuje rzeczy niemożliwe w standardowym SQL. Używając RECURSIVE, zapytanie WITH może odnosić się do własnego wyjścia. Bardzo prostym przykładem jest to zapytanie sumujące liczby całkowite 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;

ogólna forma zapytania rekurencyjnego WITH jest zawsze terminem niekurencyjnym, następnie UNION (lub UNION ALL), następnie terminem rekurencyjnym, gdzie tylko termin rekurencyjny może zawierać odniesienie do własnego wyniku zapytania. Takie zapytanie jest wykonywane w następujący sposób:

w powyższym przykładzie tabela robocza ma tylko jeden wiersz w każdym kroku i przyjmuje wartości od 1 do 100 w kolejnych krokach. W setnym kroku nie ma wyjścia z powodu klauzuli WHERE, więc zapytanie kończy się.

zapytania rekurencyjne są zwykle używane do obsługi danych hierarchicznych lub o strukturze drzewa. Użytecznym przykładem jest to zapytanie, aby znaleźć wszystkie bezpośrednie i pośrednie części produktu, biorąc pod uwagę tylko tabelę, która pokazuje bezpośrednie wtrącenia:

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

podczas pracy z zapytaniami rekurencyjnymi ważne jest, aby upewnić się, że rekurencyjna część zapytania ostatecznie nie zwróci żadnych krotek, w przeciwnym razie zapytanie będzie zapętlane w nieskończoność. Czasami użycie UNION zamiast UNION ALL może to osiągnąć, odrzucając wiersze, które powielają poprzednie wiersze wyjściowe. Jednak często cykl nie obejmuje wierszy wyjściowych, które są całkowicie zduplikowane: może być konieczne sprawdzenie tylko jednego lub kilku pól, aby sprawdzić, czy ten sam punkt został osiągnięty wcześniej. Standardową metodą obsługi takich sytuacji jest obliczenie tablicy już odwiedzonych wartości. Na przykład rozważ następujące zapytanie, które przeszukuje tabelę graph za pomocą pola link :

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;

to zapytanie zostanie zapętlone, jeśli relacje link zawierają cykle. Ponieważ wymagamy wyjścia “głębokości”, po prostu zmiana UNION ALL na UNION nie wyeliminuje pętli. Zamiast tego musimy rozpoznać, czy ponownie dotarliśmy do tego samego wiersza, podążając określoną ścieżką linków. Do zapytania w pętli dodajemy dwie kolumny path i cycle :

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;

oprócz zapobiegania cyklom, wartość tablicy jest często użyteczna sama w sobie jako reprezentująca “ścieżkę” podjętą do osiągnięcia określonego wiersza.

w ogólnym przypadku, w którym należy sprawdzić więcej niż jedno pole, aby rozpoznać cykl, należy użyć tablicy wierszy. Na przykład, jeśli musimy porównać pola f1 i 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;

pomocną sztuczką do testowania zapytań, gdy nie masz pewności, czy mogą one zapętlić, jest umieszczenie LIMIT w zapytaniu nadrzędnym. Na przykład, to zapytanie zapętliłoby się na zawsze bez LIMIT:

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

działa to, ponieważ implementacja PostgreSQL ocenia tylko tyle wierszy zapytania WITH, ile jest pobieranych przez zapytanie nadrzędne. Stosowanie tej sztuczki w produkcji nie jest zalecane, ponieważ inne systemy mogą działać inaczej. Ponadto, zazwyczaj nie zadziała, jeśli sprawisz, że zewnętrzne zapytanie posortuje wyniki rekurencyjnego zapytania lub połączy je z inną tabelą, ponieważ w takich przypadkach zewnętrzne zapytanie i tak spróbuje pobrać wszystkie wyniki zapytania WITH.

przydatną właściwością zapytań WITH jest to, że są one zwykle obliczane tylko raz na wykonanie zapytania nadrzędnego, nawet jeśli są one odwoływane więcej niż raz przez zapytanie nadrzędne lub zapytania rodzeństwa WITH. Tak więc kosztowne obliczenia, które są potrzebne w wielu miejscach, mogą być umieszczone w zapytaniu WITH, aby uniknąć zbędnej pracy. Innym możliwym zastosowaniem jest zapobieganie niepożądanym wielokrotnym ocenom funkcji ze skutkami ubocznymi. Jednak drugą stroną tej monety jest to, że optymalizator nie jest w stanie wypchnąć ograniczeń z zapytania nadrzędnego do zapytania WITH, ponieważ może to mieć wpływ na wszystkie zastosowania zapytania WITH, gdy powinno dotyczyć tylko jednego. Zapytanie wielokrotne WITH zostanie ocenione jako napisane, bez tłumienia wierszy, które zapytanie nadrzędne może później odrzucić. (Ale, jak wspomniano powyżej, ewaluacja może zatrzymać się wcześnie, jeśli odniesienia do zapytania wymagają tylko ograniczonej liczby wierszy.)

jednak, jeśli zapytanie WITH nie jest rekurencyjne i nie zawiera efektów ubocznych (to znaczy jest SELECT nie zawiera zmiennych funkcji), można je złożyć do zapytania nadrzędnego, umożliwiając wspólną optymalizację dwóch poziomów zapytania. Domyślnie dzieje się tak, jeśli zapytanie nadrzędne odwołuje się do zapytania WITH tylko raz, ale nie, jeśli odwołuje się do zapytania WITH więcej niż raz. Można nadpisać tę decyzję, określając MATERIALIZED, aby wymusić oddzielne obliczenie zapytania WITH, lub określając NOT MATERIALIZED, aby wymusić scalenie go z zapytaniem nadrzędnym. Ten drugi wybór grozi duplikacją obliczeń zapytania WITH, ale nadal może dać oszczędności netto, jeśli każde użycie zapytania WITH wymaga tylko niewielkiej części pełnego wyniku zapytania WITH.

prosty przykład tych reguł to

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

to WITH zapytanie zostanie złożone, tworząc ten sam plan wykonania co

SELECT * FROM big_table WHERE key = 123;

w szczególności, jeśli istnieje indeks na key, prawdopodobnie zostanie użyty do pobrania tylko wierszy o key = 123. Z drugiej strony, w

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

zapytanie WITH zostanie zmaterializowane, tworząc tymczasową kopię big_table, która jest następnie połączona ze sobą — bez korzyści z jakiegokolwiek indeksu. To zapytanie zostanie wykonane znacznie wydajniej, jeśli zostanie zapisane 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 aby ograniczenia zapytania nadrzędnego mogły być zastosowane bezpośrednio do skanowania big_table.

przykładem, w którym NOT MATERIALIZED może być niepożądany, jest

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;

tutaj materializacja zapytania WITH zapewnia, że very_expensive_function jest obliczana tylko raz na wiersz tabeli, a nie dwa razy.

powyższe przykłady pokazują tylko, że WITH jest używany z SELECT, ale może być dołączony w ten sam sposób do INSERT, UPDATE lub DELETE. W każdym przypadku skutecznie dostarcza tymczasowe tabele, do których można się odwołać w Komendzie Głównej.