PostgreSQL: Dokumentacja: 13: 7.8. Z zapytaniami (wspólne wyrażenia tabeli)
7.8.1. Wybierz z
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 WITH
ze 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:
rekurencyjna ocena zapytań
-
Oceń nie-rekurencyjny termin. Dla
UNION
(ale nieUNION ALL
) Odrzuć zduplikowane wiersze. Uwzględnij wszystkie pozostałe wiersze w wyniku rekurencyjnego zapytania, a także umieść je w tymczasowej tabeli roboczej. -
tak długo, jak stół roboczy nie jest pusty, powtórz te kroki:
-
Oceń rekurencyjny termin, zastępując bieżącą zawartość tabeli roboczej rekurencyjnym samo-odniesieniem. W przypadku
UNION
(ale nieUNION ALL
) Odrzuć zduplikowane wiersze i wiersze, które powielają poprzedni wiersz wyniku. Uwzględnij wszystkie pozostałe wiersze w wyniku zapytania rekurencyjnego, a także umieść je w tymczasowej tabeli pośredniej. -
Zastąp zawartość tabeli roboczej zawartością tabeli pośredniej, a następnie opróżnij tabelę pośrednią.
-
Uwaga
ściśle mówiąc, ten proces nie jest iteracją, ale rekurencją, ale RECURSIVE
jest terminologią wybraną przez Komitet Standardów SQL.
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;
Tip
Pomiń składnię ROW()
we wspólnym przypadku, w którym tylko jedno pole musi być sprawdzone, aby rozpoznać cykl. Pozwala to na użycie prostej tablicy zamiast tablicy typu kompozytowego, zyskując wydajność.
Wskazówka
rekurencyjny algorytm oceny zapytań generuje wyniki w kolejności przeszukiwania. Wyniki można wyświetlić w kolejności wyszukiwania wgłębnego, tworząc w ten sposób kolumnę “path” dla zapytania zewnętrznego ORDER BY
.
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.