7.8. WITH Queries (Espressioni di tabelle comuni)

7.8.1. SELECT in WITH

Il valore di base di SELECT in WITH è di scomporre 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 delle vendite per prodotto solo nelle regioni di vendita più importanti. La clausola WITH definisce due dichiarazioni ausiliarie chiamate regional_sales e top_regions, dove l’output di regional_sales è usato in top_regions e l’output di top_regions è usato nella query SELECT primaria. Questo esempio avrebbe potuto essere scritto senza WITH, ma avremmo avuto bisogno di due livelli di sub-SELECT annidati. È un po’ più facile da seguire in questo modo.

Il modificatore opzionale RECURSIVE cambia WITH da una mera convenienza sintattica in una caratteristica che realizza cose altrimenti non possibili in SQL standard. Usando RECURSIVE, una query WITH può fare riferimento al suo stesso 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 WITH ricorsiva è sempre un termine non ricorsivo, poi UNION (o UNION ALL), poi un termine ricorsivo, dove solo il termine ricorsivo può contenere un riferimento al proprio output della query. Una tale query viene eseguita come segue:

Nell’esempio precedente, la tabella di lavoro ha solo una singola riga in ogni passo, e assume i valori da 1 a 100 nei passi successivi. Nel 100° passo, non c’è alcun output a causa della clausola WHERE, e quindi la query termina.

Le query ricorsive sono tipicamente usate per trattare dati gerarchici o strutturati ad albero. Un esempio utile è questa query per trovare tutte le sottoparti dirette e indirette di un prodotto, data solo una tabella che mostra le 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 restituisca nessuna tupla, altrimenti la query andrà in loop all’infinito. A volte, usando UNION invece di UNION ALL si può ottenere questo risultato scartando le righe che duplicano le precedenti righe in uscita. 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 gestire tali situazioni è quello di calcolare un array dei valori già visitati. Per esempio, si consideri la seguente query che cerca un grafico di tabella usando un campo di collegamento:

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 andrà in loop se le relazioni di collegamento contengono cicli. Poiché abbiamo bisogno di un output di “profondità”, cambiare semplicemente UNION ALL in UNION non eliminerebbe il looping. Invece abbiamo bisogno di riconoscere se abbiamo raggiunto di nuovo la stessa riga seguendo un particolare percorso di link. 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 di per sé in quanto rappresenta il “percorso” fatto per raggiungere una particolare riga.

Nel caso generale in cui più di un campo deve essere controllato per riconoscere un ciclo, usate un array di righe. Per 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 siete sicuri che possano andare in loop è di mettere un LIMIT nella query padre. Per esempio, questa query andrebbe in loop 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 tante righe di una query WITH quante sono effettivamente recuperate dalla query padre. L’uso di questo trucco in produzione non è raccomandato, perché altri sistemi potrebbero funzionare diversamente. Inoltre, di solito non funziona se si fa in modo che la query esterna ordini i risultati della query ricorsiva o li unisca a qualche altra tabella, perché in questi casi la query esterna di solito cerca di recuperare comunque tutto l’output della query WITH.

Un’utile proprietà delle query WITH è che vengono valutate solo una volta per ogni esecuzione della query padre, anche se sono riferite più di una volta dalla query padre o da query WITH sorelle. Così, calcoli costosi che sono necessari in più posti possono essere collocati all’interno di una query WITH per evitare lavoro ridondante. 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 è meno in grado di spingere le restrizioni dalla query padre verso il basso in una query WITH rispetto ad una sub-query ordinaria. La query WITH sarà generalmente valutata come scritta, senza soppressione delle righe che la query padre potrebbe scartare in seguito. (Ma, come detto sopra, la valutazione potrebbe fermarsi presto se i riferimenti alla query richiedono solo un numero limitato di righe.)

Gli esempi precedenti mostrano solo l’uso di WITH con SELECT, ma può essere collegato nello stesso modo a INSERT, UPDATE o DELETE. In ogni caso fornisce effettivamente delle tabelle temporanee a cui si può fare riferimento nel comando principale.