PostgreSQL: Documentație: 13: 7.8. Cu interogări (expresii comune de tabel)

valoarea de bază a SELECT în WITH este de a descompune interogările complicate în părți mai simple. Un exemplu este:

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;

care afișează vânzările pe produs totalizează numai în regiunile de vânzări de top. Clauza WITH definește două instrucțiuni auxiliare numite regional_sales și top_regions, unde ieșirea regional_sales este utilizată în top_regions și ieșirea top_regions este utilizată în interogarea primară SELECT. Acest exemplu ar fi putut fi scris fără WITH, dar am fi avut nevoie de două niveluri de sub-SELECTimbricate.

modificatorul opțional RECURSIVE modifică WITH dintr-o simplă comoditate sintactică într-o caracteristică care realizează lucruri care nu sunt altfel posibile în SQL standard. Folosind RECURSIVE, o interogare WITH se poate referi la propria ieșire. Un exemplu foarte simplu este această interogare pentru a însuma numerele întregi de la 1 la 100:

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

forma generală a unei interogări recursive WITH este întotdeauna un termen nerecursiv, apoi UNION (sau UNION ALL), apoi un termen recursiv, unde numai termenul recursiv poate conține o referință la propria ieșire a interogării. O astfel de interogare este executată după cum urmează:

în exemplul de mai sus, masa de lucru are doar un singur rând în fiecare pas și preia valorile de la 1 la 100 în pași succesivi. În pasul 100, nu există ieșire din cauza clauzei WHERE și astfel interogarea se termină.

interogările Recursive sunt de obicei utilizate pentru a trata date ierarhice sau structurate în arbore. Un exemplu util este această interogare pentru a găsi toate sub-părțile directe și indirecte ale unui produs, având în vedere doar un tabel care prezintă incluziuni imediate:

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

când lucrați cu interogări recursive, este important să vă asigurați că partea recursivă a interogării nu va returna în cele din urmă tupluri, altfel interogarea se va bucla la nesfârșit. Uneori, folosind UNION în loc de UNION ALL se poate realiza acest lucru prin aruncarea înapoi în mare rânduri care duplicat rândurile de ieșire anterioare. Cu toate acestea, adesea un ciclu nu implică rânduri de ieșire care sunt complet duplicate: poate fi necesar să verificați doar unul sau câteva câmpuri pentru a vedea dacă același punct a fost atins înainte. Metoda standard pentru gestionarea unor astfel de situații este de a calcula o serie de valori deja vizitate. De exemplu, luați în considerare următoarea interogare care caută un tabel graph folosind un câmp 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;

această interogare se va bucla dacă relațiile link conțin cicluri. Pentru că avem nevoie de o ieșire” adâncime”, doar schimbarea UNION ALL la UNION nu ar elimina bucla. În schimb, trebuie să recunoaștem dacă am ajuns din nou la același rând în timp ce urmăm o anumită cale de legături. Adăugăm două coloane path și cycle la interogarea predispusă la buclă:

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;

în afară de prevenirea ciclurilor, valoarea matricei este adesea utilă în sine ca reprezentând “calea” luată pentru a ajunge la un anumit rând.

în cazul general în care trebuie verificate mai multe câmpuri pentru a recunoaște un ciclu, utilizați o serie de rânduri. De exemplu, dacă trebuie să comparăm câmpurile 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;

un truc util pentru testarea interogărilor atunci când nu sunteți sigur dacă s-ar putea bucla este de a plasa un LIMIT în interogarea părinte. De exemplu, această interogare ar bucla pentru totdeauna fără LIMIT:

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

acest lucru funcționează deoarece implementarea PostgreSQL evaluează doar câte rânduri dintr-o interogare WITH sunt preluate de fapt de interogarea părinte. Utilizarea acestui truc în producție nu este recomandată, deoarece alte sisteme ar putea funcționa diferit. De asemenea, de obicei, nu va funcționa dacă faceți interogarea exterioară să sorteze rezultatele interogării recursive sau să le alăturați la un alt tabel, deoarece în astfel de cazuri interogarea exterioară va încerca, de obicei, să aducă toate rezultatele interogării WITH oricum.

o proprietate utilă a interogărilor WITH este că acestea sunt evaluate în mod normal o singură dată pe executarea interogării părinte, chiar dacă sunt menționate de mai multe ori de interogările părinte sau de interogările frate WITH. Astfel, calculele costisitoare care sunt necesare în mai multe locuri pot fi plasate într-o interogare WITH pentru a evita munca redundantă. O altă aplicație posibilă este de a preveni evaluările multiple nedorite ale funcțiilor cu efecte secundare. Cu toate acestea, cealaltă față a acestei monede este că Optimizatorul nu este capabil să împingă restricțiile de la interogarea părinte în jos într-o interogare cu referință multiplă WITH, deoarece aceasta ar putea afecta toate utilizările ieșirii interogării WITH atunci când ar trebui să afecteze doar una. Interogarea cu referință multiplă WITH va fi evaluată ca scrisă, fără suprimarea rândurilor pe care interogarea părinte le-ar putea renunța ulterior. (Dar, așa cum am menționat mai sus, evaluarea s-ar putea opri mai devreme dacă referința (referințele) la interogare necesită doar un număr limitat de rânduri.)

cu toate acestea, dacă o interogare WITH este nerecursivă și fără efecte secundare (adică este o SELECT care nu conține funcții volatile), atunci poate fi pliată în interogarea părinte, permițând optimizarea comună a celor două niveluri de interogare. În mod implicit, acest lucru se întâmplă dacă interogarea părinte face referire la interogarea WITH o singură dată, dar nu și dacă face referire la interogarea WITH de mai multe ori. Puteți suprascrie acea decizie specificând MATERIALIZED pentru a forța calculul separat al interogării WITH sau specificând NOT MATERIALIZED pentru a forța îmbinarea acesteia în interogarea părinte. Această din urmă alegere riscă calculul duplicat al interogării WITH, dar poate oferi totuși o economie netă dacă fiecare utilizare a interogării WITH are nevoie doar de o mică parte din ieșirea completă a interogării WITH.

un exemplu simplu al acestor reguli este

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

această interogare WITH va fi pliată, producând același plan de execuție ca

SELECT * FROM big_table WHERE key = 123;

în special, dacă există un index pe key, acesta va fi probabil folosit pentru a prelua doar rândurile cu key = 123. Pe de altă parte, în

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

interogarea WITH va fi materializată, producând o copie temporară a big_table care este apoi unită cu ea însăși — fără a beneficia de niciun index. Această interogare va fi executată mult mai eficient dacă este scrisă ca

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;

, astfel încât restricțiile interogării părinte să poată fi aplicate direct scanărilor big_table.

un exemplu în care NOT MATERIALIZED ar putea fi nedorit este

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;

aici, materializarea interogării WITH asigură că very_expensive_function este evaluată o singură dată pe rând de tabel, nu de două ori.

exemplele de mai sus arată doar WITH utilizat cu SELECT, dar poate fi atașat în același mod la INSERT, UPDATE sau DELETE. În fiecare caz, acesta oferă în mod eficient tabel temporar(E) care pot fi menționate în comanda principală.