Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Query > Query WITH
PrecedenteElenci VALUESTipi di DatoSuccessivo

7.8. Query WITH

WITH fornisce un modo di scrivere sottoquery per l'uso in una query SELECT più grande. Le sottoquery possono essere pensate come la definizione di tabelle temporanee che esistono solo per questa query. Un uso di questa funzionalità è di suddividere 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_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

che mostra le vendite totali per prodotto solo nelle regioni con vendite maggiori. Questo esempio pooteva essere scritto senza la WITH, ma avremmo avuto bisogno di due livelli di sotto-SELECT annidati. È un po' più semplice seguire questa strada.

Il modificatore opzionale RECURSIVE trasforma WITH da una mera convenienza sintattica a una caratteristica che realizza cose altrimenti non possibili nello standard SQL. Usando RECURSIVE, una query WITH può riferirsi al proprio output. Un esempio molto semplice è la query per sommare 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, quindi UNION (o UNION ALL) e poi un termine ricorsivo, dove solo il termine ricorsivo può contenere un riferimento all'output proprio della query. Questo ti query viene esegito nel seguente modo:

Procedura 7.1. Valutazione di query ricorsive

  1. Valutare il termine non-ricorsivo. Per UNION (ma non per UNION ALL), scartare righe duplicate. Includere tutte le righe rimanenti nel risultato della query ricorsiva, inoltre posizionarle in una tabella di lavoro temporanea.

  2. A patto che la tabella di lavoro non sia vuota, ripetere questi passi:

    1. Valutare il termine ricorsivo, sostituendo l'attuale contenuto della tabella di lavoro per il riferimento ricorsivo a sè stessa. Per UNION (ma non per UNION ALL), scartare le righe duplicate e le righe che duplicano qualsiasi riga risultante precedente. Includere tutte le rimanenti righe nel risultato della query ricorsiva, e posizionarle in una tabella intermedia temporanea.

    2. Sostituire il contenuto della tabella di lavoro con il contenuto della tabella intermedia, quindi svuotare la tabella intermedia.

[Nota]

Nota

A rigor di termini, questo processo si chiama iterazione, non ricorsione, ma RECURSIVE è la terminologia scelta dalla commissione per lo standard SQL.

Nel'esempio sopra, la tabella di lavoro ha solo una singola riga per ogni passo, e prende i valori da 1 a 100 in passi successivi. Nel 100esimo passo, non c'è nessuno output, in conseguenza della clausola WHERE, e quindi la query termina.

Le query ricorsive sono usate tipicamente per gestire 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 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_quantity
FROM included_parts
GROUP BY sub_part

Quando si lavora con query ricorsive è importante essere sicuri che la parte ricorsiva della query eventualmente non restituirà tuple, altrimenti la query effettuerà loop indefinitamente. Qualche volta, usando UNION invece di UNION ALL puà risolvere la questione scartando righe che duplicano righe di output precedenti. Comunque, spesso un ciclo non coinvolge righe di output che sono completamente duplicate: può essere necessario controllare solo uno o pochi campi per vedere se lo stesso punto è stato raggiunto prima. Il metodo standard per gestire queste situazioni è di calcolare un array dei valori già visitati. Per esempio, considerare la seguente query che cerca una tabella graph usando 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 effettuerà un loop se le relazioni link contengono cicli. Siccome si richiede un output «profondo», cambiare semplicemente UNION ALL in UNION non eliminerebbe i loop. È necessario invece riconoscere se si è raggiunta la stessa riga di nuovo mentre si seguiva un particolare percorso di link. Si hanno due colonne path e cycle nella query possibilmente soggetta a llop:

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
        SELECT g.id, g.link, g.data, 1,
          ARRAY[g.id],
          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 cicli, il valore array è spesso utile a rappresentare il «percorso» seguito per raggiungere ogni particolare riga.

Nel caso generale in cui più di un campo necessita di essere controllato per riconoscere un ciclo, usare un array di righe. Per esempio, se si ha bisgno 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[ROW(g.f1, g.f2)],
          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;

[Suggerimento]

Suggerimento

Omettere ROW() nel caso in cui solo un campo abbia bisogno di essere controllato per riconoscere un ciclo. Questo permette di poter usare un array semplice piuttosto che un array di tipo composto, aumentando l'efficienza.

[Suggerimento]

Suggerimento

[Nota]

Nota

TODO

The recursive query evaluation algorithm produces its output in breadth-first search order. You can display the results in depth-first search order by making the outer query ORDER BY a «path» column constructed in this way.

Un trucco che può aiutare a testare le query quando non si è certi se potrebbero produrre loop è di posizionare una LIMIT nella query superiore. Per esempio, questa query produrrebbe un loop infinito senza la 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 prese dalla query superiore. Usare questo trucco in produzione non è raccomandato, dato che altri sistemi potrebbero funzionare diversamente. Inoltre, di solito non funzionerà se si vuole che la query esterna ordini i risultati della query ricorsiva o si voglia effettuare una join con un'altra tabella.

Una proprietà utile delle query WITH è che sono valutate solo una volta per esecuzione della query superiore, anche se sono riferite più di una volta dalla query superiore o da query WITH sorelle. Così, calcoli costosi che sono necessari in molteplici posti possono essere posizionati all'interno di una query WITH per evitare lavoro ridondante. Un'altra applicazione possibile è di prevenire valutazioni multiple non volute di funzioni con effetti collaterali. Comunque, l'altra faccia della medaglia è che l'ottimizzatore è meno capace di attuare restizioni dalla query superiore giù fino alla query WITH rispetto ad una ordinaria sottoquery. La query WITH sarà generalmente valutata come fissa, senza sottoespressioni di righe che la query superiore potrebbe scartare dopo (ma, come menzionato sopra, la valutazione potrebbe fermarsi precedentemente se il riferimento/i alla query richiede solo un numero limitato di righe).

Documentazione di PostgreSQL 9.0 > Il linguaggio SQL > Query > Query WITH
PrecedenteElenci VALUESTipi di DatoSuccessivo