PostgreSQL: Documentazione: 13: 7.8. CON query (espressioni di tabella comuni)

Il valore di base di SELECT in WITH è quello di suddividere le query complicate in parti più semplici. Un esempio è:

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;

che visualizza i totali di vendita per prodotto solo nelle regioni di vendita principali. La clausola WITH definisce due istruzioni ausiliarie denominate regional_sales e top_regions, in cui l’output di regional_sales viene utilizzato in top_regions e l’output di top_regions viene utilizzato nella query primaria SELECT. Questo esempio avrebbe potuto essere scritto senza WITH, ma avremmo avuto bisogno di due livelli di sub-SELECT annidati. È un po ‘ più facile seguire questo modo.

Il modificatore opzionale RECURSIVE cambia WITH da una semplice comodità sintattica a una funzionalità che realizza cose non altrimenti possibili in SQL standard. Utilizzando RECURSIVE, una query WITH può fare riferimento al proprio output. Un esempio molto semplice è questa query per sommare gli interi da 1 a 100:

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

La forma generale di una query ricorsiva WITH è sempre un termine non ricorsivo, quindi UNION (o UNION ALL), quindi un termine ricorsivo, in cui solo il termine ricorsivo può contenere un riferimento all’output della query. Tale query viene eseguita come segue:

Nell’esempio precedente, il tavolo di lavoro ha una sola riga in ogni passaggio e assume i valori da 1 a 100 in passaggi successivi. Nel passaggio 100, non c’è output a causa della clausola WHERE, quindi la query termina.

Le query ricorsive vengono in genere utilizzate per gestire dati gerarchici o strutturati ad albero. Un esempio utile è questa query per trovare tutte le sotto-parti dirette e indirette di un prodotto, data solo una tabella che mostra inclusioni immediate:

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

Quando si lavora con query ricorsive è importante essere sicuri che la parte ricorsiva della query alla fine non restituirà tuple, altrimenti la query si interromperà indefinitamente. A volte, utilizzando UNION invece di UNION ALL può ottenere ciò scartando le righe che duplicano le righe di output precedenti. Tuttavia, spesso un ciclo non comporta righe di output completamente duplicate: potrebbe essere necessario controllare solo uno o pochi campi per vedere se lo stesso punto è stato raggiunto prima. Il metodo standard per la gestione di tali situazioni consiste nel calcolare una matrice di valori già visitati. Ad esempio, si consideri la seguente query che cerca una tabella graph utilizzando un campo 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;

Questa query verrà eseguita in loop se le relazioni link contengono cicli. Poiché richiediamo un output “depth”, cambiare UNION ALL in UNION non eliminerebbe il loop. Invece dobbiamo riconoscere se abbiamo raggiunto di nuovo la stessa riga mentre seguiamo un particolare percorso di collegamenti. Aggiungiamo due colonne path e cycle alla query soggetta a loop:

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;

Oltre a prevenire i cicli, il valore dell’array è spesso utile a sé stante come rappresentante del “percorso” intrapreso per raggiungere una particolare riga.

Nel caso generale in cui è necessario controllare più di un campo per riconoscere un ciclo, utilizzare un array di righe. Ad esempio, se avessimo bisogno di confrontare i campi f1 e 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 trucco utile per testare le query quando non si è certi che possano eseguire un ciclo è quello di inserire un LIMIT nella query padre. Ad esempio, questa query si interromperebbe per sempre senza il LIMIT:

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

Questo funziona perché l’implementazione di PostgreSQL valuta solo quante righe di una query WITH vengono effettivamente recuperate dalla query padre. L’utilizzo di questo trucco in produzione non è raccomandato, perché altri sistemi potrebbero funzionare in modo diverso. Inoltre, di solito non funzionerà se si effettua la query esterna per ordinare i risultati della query ricorsiva o unirli a qualche altra tabella, perché in questi casi la query esterna di solito tenta di recuperare comunque tutti gli output della query WITH.

Una proprietà utile delle query WITH è che normalmente vengono valutate solo una volta per esecuzione della query padre, anche se vengono indirizzate più di una volta dalla query padre o dalle query di pari livello WITH. Pertanto, i calcoli costosi necessari in più punti possono essere collocati all’interno di una query WITH per evitare lavori ridondanti. Un’altra possibile applicazione è quella di prevenire valutazioni multiple indesiderate di funzioni con effetti collaterali. Tuttavia, l’altro lato di questa medaglia è che l’ottimizzatore non è in grado di spingere le restrizioni dalla query genitore verso il basso in una query WITH con riferimento multiplo, poiché ciò potrebbe influire su tutti gli usi dell’output della query WITH quando dovrebbe influire solo su uno. La query WITH con riferimento multiplo verrà valutata come scritta, senza la soppressione delle righe che la query padre potrebbe scartare in seguito. (Ma, come accennato in precedenza, la valutazione potrebbe interrompersi presto se i riferimenti alla query richiedono solo un numero limitato di righe.)

Tuttavia, se una query WITH non è ricorsiva e priva di effetti collaterali (ovvero, è una SELECT che non contiene funzioni volatili), può essere piegata nella query padre, consentendo l’ottimizzazione congiunta dei due livelli di query. Per impostazione predefinita, ciò accade se la query padre fa riferimento alla query WITH solo una volta, ma non se fa riferimento alla query WITH più di una volta. È possibile ignorare tale decisione specificando MATERIALIZED per forzare il calcolo separato della query WITH o specificando NOT MATERIALIZED per costringerla a essere unita nella query padre. Quest’ultima scelta rischia di duplicare il calcolo della query WITH, ma può comunque fornire un risparmio netto se ogni utilizzo della query WITH richiede solo una piccola parte dell’output completo della query WITH.

Un semplice esempio di queste regole è

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

Questa query WITH verrà piegata, producendo lo stesso piano di esecuzione di

SELECT * FROM big_table WHERE key = 123;

In particolare, se c’è un indice su key, probabilmente verrà utilizzato per recuperare solo le righe con key = 123. D’altra parte, in

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

la query WITH verrà materializzata, producendo una copia temporanea di big_table che viene quindi unita a se stessa — senza il beneficio di alcun indice. Questa query verrà eseguita in modo molto più efficiente se scritta come

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;

in modo che le restrizioni della query padre possano essere applicate direttamente alle scansioni di big_table.

Un esempio in cui NOT MATERIALIZED potrebbe essere indesiderabile è

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;

Qui, la materializzazione della query WITH garantisce che very_expensive_function venga valutata solo una volta per riga della tabella, non due volte.

Gli esempi sopra mostrano solo WITH utilizzato con SELECT, ma può essere collegato allo stesso modo a INSERT, UPDATEo DELETE. In ogni caso fornisce effettivamente tabelle temporanee a cui si può fare riferimento nel comando principale.