7.8. Zapytania WITH (typowe wyrażenia tabelaryczne)

7.8.1. SELECT w WITH

Podstawową wartością SELECT w WITH jest rozbijanie skomplikowanych zapytań na prostsze części. Przykładem może być zapytanie:

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óre wyświetla sumy sprzedaży per-produkt tylko w najlepszych regionach sprzedaży. Klauzula WITH definiuje dwa pomocnicze wyrażenia o nazwach regional_sales i top_regions, gdzie dane wyjściowe z regional_sales są używane w top_regions, a dane wyjściowe z top_regions są używane w głównym zapytaniu SELECT. Ten przykład mógłby być napisany bez WITH, ale potrzebowalibyśmy dwóch poziomów zagnieżdżonych sub-SELECTs. W ten sposób jest to nieco łatwiejsze do naśladowania.

Opcjonalny modyfikator RECURSIVE zmienia WITH ze zwykłego udogodnienia składniowego w cechę, która pozwala osiągnąć rzeczy, które nie są możliwe w standardowym SQL. Używając RECURSIVE, zapytanie WITH może odnosić się do swoich własnych danych wyjściowych. Bardzo prostym przykładem jest zapytanie o sumę liczb całkowitych 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 postać zapytania rekurencyjnego WITH to zawsze wyrażenie nierekurencyjne, następnie UNION (lub UNION ALL), następnie wyrażenie rekurencyjne, gdzie tylko wyrażenie rekurencyjne 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 danych wyjściowych z powodu klauzuli WHERE, a więc zapytanie kończy się.

Kwerendy rekursywne są zwykle używane do radzenia sobie z hierarchicznymi lub drzewiastymi danymi. Użytecznym przykładem jest zapytanie mające na celu znalezienie wszystkich bezpośrednich i pośrednich podczęści produktu, biorąc pod uwagę tylko tabelę, która pokazuje bezpośrednie inkluzje:

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

Przy pracy z zapytaniami rekurencyjnymi ważne jest, aby upewnić się, że część rekurencyjna zapytania ostatecznie nie zwróci żadnych tupli, w przeciwnym razie zapytanie będzie się zapętlać w nieskończoność. Czasami użycie UNION zamiast UNION ALL może to osiągnąć poprzez odrzucenie wierszy, które powielają poprzednie wiersze wyjściowe. Jednakże, 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 zobaczyć, czy ten sam punkt został osiągnięty wcześniej. Standardową metodą radzenia sobie z takimi sytuacjami jest obliczenie tablicy już odwiedzonych wartości. Na przykład, rozważmy następujące zapytanie, które przeszukuje graf tabelaryczny używając 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 zapętla się, jeśli relacje link zawierają cykle. Ponieważ wymagamy „głębokiego” wyjścia, zwykła zmiana UNION ALL na UNION nie wyeliminuje zapętlenia. Zamiast tego musimy rozpoznać, czy dotarliśmy ponownie do tego samego wiersza podczas podążania określoną ścieżką powiązań. Dodajemy dwie kolumny ścieżka i cykl do podatnego na zapętlenia zapytania:

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 przydatna sama w sobie jako reprezentacja „ścieżki” podjętej w celu dotarcia do konkretnego wiersza.

W ogólnym przypadku, gdy więcej niż jedno pole musi być sprawdzone, aby rozpoznać cykl, użyj tablicy wierszy. Na przykład, gdybyśmy potrzebowali 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 jesteś pewien czy mogą się zapętlić jest umieszczenie LIMITU w zapytaniu nadrzędnym. Na przykład, to zapytanie zapętliłoby się na zawsze bez LIMITU:

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

To działa, ponieważ PostgreSQL ocenia tylko tyle wierszy zapytania WITH, ile jest rzeczywiście pobieranych przez zapytanie nadrzędne. Używanie tej sztuczki na produkcji nie jest zalecane, ponieważ inne systemy mogą działać inaczej. Ponadto, zazwyczaj nie zadziała, jeśli zewnętrzne zapytanie posortuje wyniki zapytania rekurencyjnego lub dołączy je do innej tabeli, ponieważ w takich przypadkach zewnętrzne zapytanie i tak będzie próbowało pobrać wszystkie dane wyjściowe zapytania WITH.

Przydatną właściwością zapytań WITH jest to, że są one analizowane tylko raz podczas wykonywania zapytania nadrzędnego, nawet jeśli odwołujemy się do nich więcej niż raz przez zapytanie nadrzędne lub rodzeństwo zapytań WITH. W ten sposób kosztowne obliczenia, które są potrzebne w wielu miejscach, mogą być umieszczone w zapytaniu WITH, aby uniknąć nadmiarowej pracy. Innym możliwym zastosowaniem jest zapobieganie niepożądanej wielokrotnej ewaluacji funkcji z efektami ubocznymi. Jednakże, drugą stroną tego medalu jest to, że optymalizator jest mniej zdolny do przenoszenia ograniczeń z zapytania nadrzędnego w dół do zapytania WITH niż do zwykłego podzapytania. Zapytanie WITH będzie generalnie oceniane tak jak zostało 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śniej, jeśli odwołanie(a) do zapytania wymagają tylko ograniczonej liczby wierszy.)

Przykłady powyżej pokazują tylko WITH używane z SELECT, ale może być dołączone w ten sam sposób do INSERT, UPDATE lub DELETE. W każdym przypadku efektywnie dostarcza tymczasową tabelę(y), do której można się odwołać w głównym poleceniu.