7.8. WITH Queries (Common Table Expressions)

7.8.1. SELECT în WITH

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ă totalurile vânzărilor per produs numai în regiunile de vânzări de top. Clauza WITH definește două instrucțiuni auxiliare numite regional_sales și top_regions, în care rezultatul din regional_sales este utilizat în top_regions, iar rezultatul din top_regions este utilizat în interogarea principală SELECT. Acest exemplu ar fi putut fi scris fără WITH, dar am fi avut nevoie de două niveluri de subSELECT-uri imbricate. Este un pic mai ușor de urmărit în acest fel.

Modificatorul opțional RECURSIVE transformă 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 de însumare a numerelor î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 WITH recursive este întotdeauna un termen non-recursiv, apoi UNION (sau UNION ALL), apoi un termen recursiv, în care numai termenul recursiv poate conține o referință la propriul rezultat al interogării. O astfel de interogare se execută după cum urmează:

În exemplul de mai sus, tabelul de lucru are doar un singur rând în fiecare pas, și preia valorile de la 1 la 100 în pași succesivi. În al 100-lea pas, nu există niciun rezultat din cauza clauzei WHERE, astfel că interogarea se termină.

Interogările recursive sunt utilizate de obicei pentru a trata date ierarhice sau structurate în arbore. Un exemplu util este această interogare pentru a găsi toate subpărțile directe și indirecte ale unui produs, având în vedere doar un tabel care arată incluziunile 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 se lucrează cu interogări recursive, este important să se asigure că partea recursivă a interogării nu va returna în cele din urmă niciun tuplu, altfel interogarea se va derula în buclă la nesfârșit. Uneori, utilizarea UNION în loc de UNION ALL poate realiza acest lucru, eliminând rândurile care dublează rândurile de ieșire anterioare. Cu toate acestea, de multe ori, un ciclu nu implică rânduri de ieșire care sunt complet duplicate: poate fi necesar să se verifice doar unul sau câteva câmpuri pentru a vedea dacă același punct a mai fost atins anterior. Metoda standard de tratare a unor astfel de situații este de a calcula o matrice a valorilor deja vizitate. De exemplu, luați în considerare următoarea interogare care caută în graficul unui tabel folosind un câmp de legătură:

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 va face o buclă în cazul în care relațiile de legătură conțin cicluri. Deoarece avem nevoie de o ieșire de „profunzime”, simpla schimbare a UNION ALL în 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 un anumit traseu 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, deoarece reprezintă „calea” parcursă pentru a ajunge la un anumit rând.

În cazul general în care mai mult de un câmp trebuie verificat pentru a recunoaște un ciclu, utilizați o matrice de rânduri. De exemplu, dacă am avea nevoie 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 că acestea ar putea face buclă este de a plasa un LIMIT în interogarea părinte. De exemplu, această interogare ar intra în buclă la nesfârșit 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 atâtea rânduri ale unei interogări WITH câte sunt preluate efectiv 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 ca interogarea exterioară să sorteze rezultatele interogării recursive sau să le unească cu un alt tabel, deoarece în astfel de cazuri interogarea exterioară va încerca, de obicei, să recupereze oricum toate rezultatele interogării WITH.

O proprietate utilă a interogărilor WITH este că acestea sunt evaluate o singură dată la fiecare execuție a interogării părinte, chiar dacă sunt menționate de mai multe ori de interogarea părinte sau de interogările WITH fraterne. Astfel, calculele costisitoare care sunt necesare în mai multe locuri pot fi plasate în cadrul unei interogări WITH pentru a evita munca redundantă. O altă aplicație posibilă este prevenirea evaluărilor multiple nedorite ale funcțiilor cu efecte secundare. Cu toate acestea, reversul medaliei este că optimizatorul este mai puțin capabil să împingă restricțiile din interogarea mamă în jos într-o interogare WITH decât într-o subinterogare obișnuită. În general, interogarea WITH va fi evaluată așa cum este scrisă, fără suprimarea rândurilor pe care interogarea mamă le-ar putea elimina ulterior. (Dar, după cum s-a menționat mai sus, evaluarea s-ar putea opri mai devreme dacă referința (referințele) la interogare solicită doar un număr limitat de rânduri.)

Exemplele de mai sus arată că WITH este utilizat doar cu SELECT, dar poate fi atașat în același mod la INSERT, UPDATE sau DELETE. În fiecare caz, el furnizează efectiv tabel(e) temporar(e) la care se poate face referire în comanda principală.

.