Clausola WITH

Modalità Focus

Clausola WITH - Amazon Redshift

Sintassi Parametri Note per l'utilizzo Ricorsivo CTEs Esempi Esempio: CTE ricorsiva

Una clausola WITH è una clausola facoltativa che precede l'elenco SELECT in una query. La clausola WITH definisce uno o più common_table_expression. Ogni espressione comune di tabella (CTE) definisce una tabella temporanea, che è simile a una definizione di vista. È possibile fare riferimento a queste tabelle temporanee nella clausola FROM. Vengono utilizzati solo durante l'esecuzione della query a cui appartengono. Ogni CTE nella clausola WITH specifica un nome di tabella, un elenco facoltativo di nomi di colonna e un'espressione di query che restituisce una tabella (un'istruzione SELECT). Quando si fa riferimento al nome della tabella temporanea nella clausola FROM della stessa espressione di query che la definisce, la CTE è ricorsiva.

Le sottoquery della clausola WITH sono un modo efficace per definire le tabelle che possono essere utilizzate durante l'esecuzione di una singola query. In ogni caso, è possibile ottenere gli stessi risultati utilizzando le sottoquery nel corpo principale dell'istruzione SELECT, ma le sottoquery della clausola WITH potrebbero essere più semplici da scrivere e leggere. Ove possibile, le sottoquery della clausola WITH che sono referenziate più volte sono ottimizzate come sottoespressioni comuni; vale a dire, potrebbe essere possibile valutare una sottoquery WITH una volta e riutilizzarne i risultati. Tieni presente che le sottoespressioni comuni non sono limitate a quelle definite nella clausola WITH.

Sintassi


[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]

dove common_table_expression può essere non ricorsivo o ricorsivo. Di seguito è riportata la forma non ricorsiva:


CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

Di seguito è riportata la forma ricorsiva di common_table_expression:


CTE_table_name (column_name [, ...] ) AS ( recursive_query )

Parametri

RECURSIVE

Parola chiave che identifichi la query come CTE ricorsiva. Questa parola chiave è obbligatoria se common_table_expression definito nella clausola WITH è ricorsivo. È possibile specificare la parola chiave RECURSIVE una sola volta, immediatamente dopo la parola chiave WITH, anche quando la clausola WITH contiene più ricorsive. CTEs In generale, una CTE ricorsiva è una sottoquery UNION ALL con due parti.

common_table_expression

Definisce una tabella temporanea a cui è possibile fare riferimento nella Clausola FROM e viene utilizzato solo durante l'esecuzione della query a cui appartiene.

CTE_table_name

Nome univoco per una tabella temporanea che definisce i risultati di una sottoquery della clausola WITH. Non puoi utilizzare nomi duplicati in una singola clausola WITH. A ogni sottoquery deve essere assegnato un nome di tabella a cui è possibile fare riferimento nella Clausola FROM.

column_name

Un elenco facoltativo di nomi di colonna di output per la sottoquery della clausola WITH, separati da virgole. Il numero dei nomi di colonna specificati deve essere uguale o inferiore al numero delle colonne definite dalla sottoquery. Per una CTE non ricorsiva, column_name è facoltativo. Per una CTE ricorsiva, column_name è obbligatorio.

query

Qualsiasi query SELECT supportata da Amazon Redshift. Per informazioni, consultare SELECT.

recursive_query

Una query UNION ALL costituita da due sottoquery SELECT:

La prima sottoquery SELECT non ha un riferimento ricorsivo allo stesso CTE_table_name. Restituisce un set di risultati che è il seed iniziale della ricorsione. Questa parte è chiamata membro iniziale o membro del seed.
La seconda sottoquery SELECT fa riferimento allo stesso CTE_table_name nella sua clausola FROM. Questo è chiamato membro ricorsivo. La recursive_query contiene una condizione WHERE per terminare la ecursive_query.

Note per l'utilizzo

Puoi utilizzare una clausola WITH nelle seguenti istruzioni SQL:

SELECT
SELECT INTO
CREATE TABLE AS
CREATE VIEW
DECLARE
EXPLAIN
INSERT INTO...SELECT
PREPARE
UPDATE (all'interno di una sottoquery di una clausola WHERE; non è possibile definire una CTE ricorsiva nella sottoquery. La CTE ricorsiva deve precedere la clausola UPDATE.)
DELETE

Se la clausola FROM di una query che contiene una clausola WITH non fa riferimento a nessuna delle tabelle definite dalla clausola WITH, la clausola WITH viene ignorata e la query viene eseguita normalmente.

A una tabella definita da una sottoquery della clausola WITH è possibile fare riferimento solo nell'ambito della query SELECT avviata dalla clausola WITH. Ad esempio, puoi fare riferimento a una tabella di questo tipo nella clausola FROM di una sottoquery nell'elenco SELECT, nella clausola WHERE o nella clausola HAVING. Non puoi utilizzare una clausola WITH in una sottoquery e fare riferimento alla tabella nella clausola FROM della query principale o un'altra sottoquery. Questo modello di query genera un messaggio di errore nel formato relation table_name doesn't exist per la tabella della clausola WITH.

Non puoi specificare un'altra clausola WITH all'interno di una sottoquery della clausola WITH.

Non puoi creare riferimenti alle tabelle definite dalle sottoquery della clausola WITH. Ad esempio, la seguente query restituisce un errore a causa del riferimento in avanti alla tabella W2 nella definizione della tabella W1:


with w1 as (select * from w2), w2 as (select * from w1)
select * from sales;
ERROR:  relation "w2" does not exist

Una sottoquery della clausola WITH non può consistere in un'istruzione SELECT INTO; tuttavia, è possibile utilizzare una clausola WITH in un'istruzione SELECT INTO.

Espressioni di tabella comuni ricorsive

Una espressione di tabella comune (CTE) ricorsiva è una CTE che fa riferimento a sé stessa. Una CTE ricorsiva è utile per eseguire query su dati gerarchici, ad esempio organigrammi che mostrano relazioni di reporting tra dipendenti e responsabili. Per informazioni, consultare Esempio: CTE ricorsiva.

Un altro uso comune è una distinta base multilivello, quando un prodotto è costituito da molti componenti e ogni componente è costituito a sua volta da altri componenti o sottoinsiemi.

Assicurarsi di limitare la profondità di ricorsione includendo una clausola WHERE nella seconda sottoquery SELECT della query ricorsiva. Per un esempio, consultare Esempio: CTE ricorsiva. In caso contrario, può verificarsi un errore simile a quello riportato di seguito.

Recursive CTE out of working buffers.
Exceeded recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.

Nota

max_recursion_rows è un parametro che imposta il numero massimo di righe che un CTE ricorsivo può restituire per evitare cicli di ricorsione infiniti. Si consiglia di non modificarlo con un valore superiore a quello predefinito. In questo modo si evita che infiniti problemi di ricorsione nelle query occupino uno spazio eccessivo nel cluster.

È possibile specificare un ordinamento e limitare il risultato della CTE ricorsiva. È possibile includere opzioni di raggruppamento e distinte sul risultato finale della CTE ricorsiva.

Non è possibile specificare un'altra clausola WITH RECURSIVE all'interno di una sottoquery. Il membro recursive_query non può includere una clausola order by o limit.

Esempi

L'esempio seguente mostra il caso più semplice possibile di una query che contiene una clausola WITH. La query WITH denominata VENUECOPY seleziona tutte le righe dalla tabella VENUE. La query principale a sua volta seleziona tutte le righe da VENUECOPY. La tabella VENUECOPY esiste solo per la durata di questa query.


with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;


 venueid |         venuename          |    venuecity    | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park                | Bridgeview      | IL         |          0
2 | Columbus Crew Stadium      | Columbus        | OH         |          0
3 | RFK Stadium                | Washington      | DC         |          0
4 | CommunityAmerica Ballpark  | Kansas City     | KS         |          0
5 | Gillette Stadium           | Foxborough      | MA         |      68756
6 | New York Giants Stadium    | East Rutherford | NJ         |      80242
7 | BMO Field                  | Toronto         | ON         |          0
8 | The Home Depot Center      | Carson          | CA         |          0
9 | Dick's Sporting Goods Park | Commerce City   | CO         |          0
v     10 | Pizza Hut Park             | Frisco          | TX         |          0
(10 rows)

L'esempio seguente mostra una clausola WITH che produce due tabelle, denominate VENUE_SALES e TOP_VENUES. La seconda tabella della query WITH seleziona dalla prima. A sua volta, la clausola WHERE del blocco di query principale contiene una sottoquery che vincola la tabella TOP_VENUES.


with venue_sales as
(select venuename, venuecity, sum(pricepaid) as venuename_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
group by venuename, venuecity),

top_venues as
(select venuename
from venue_sales
where venuename_sales > 800000)

select venuename, venuecity, venuestate,
sum(qtysold) as venue_qty,
sum(pricepaid) as venue_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
and venuename in(select venuename from top_venues)
group by venuename, venuecity, venuestate
order by venuename;


        venuename       |   venuecity   | venuestate | venue_qty | venue_sales
------------------------+---------------+------------+-----------+-------------
August Wilson Theatre   | New York City | NY         |      3187 |  1032156.00
Biltmore Theatre        | New York City | NY         |      2629 |   828981.00
Charles Playhouse       | Boston        | MA         |      2502 |   857031.00
Ethel Barrymore Theatre | New York City | NY         |      2828 |   891172.00
Eugene O'Neill Theatre  | New York City | NY         |      2488 |   828950.00
Greek Theatre           | Los Angeles   | CA         |      2445 |   838918.00
Helen Hayes Theatre     | New York City | NY         |      2948 |   978765.00
Hilton Theatre          | New York City | NY         |      2999 |   885686.00
Imperial Theatre        | New York City | NY         |      2702 |   877993.00
Lunt-Fontanne Theatre   | New York City | NY         |      3326 |  1115182.00
Majestic Theatre        | New York City | NY         |      2549 |   894275.00
Nederlander Theatre     | New York City | NY         |      2934 |   936312.00
Pasadena Playhouse      | Pasadena      | CA         |      2739 |   820435.00
Winter Garden Theatre   | New York City | NY         |      2838 |   939257.00
(14 rows)

I seguenti due esempi illustrano le regole per l'ambito dei riferimenti di tabella basati sulle sottoquery della clausola WITH. La prima query viene eseguita, ma la seconda non riesce con un errore previsto. La prima query contiene la sottoquery clausola WITH all'interno dell'elenco SELECT della query principale. Alla tabella definita dalla clausola WITH (HOLIDAYS) si fa riferimento nella clausola FROM della sottoquery nell'elenco SELECT:


select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join date on sales.dateid=date.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

caldate   | daysales | dec25sales
-----------+----------+------------
2008-12-25 | 70402.00 |   70402.00
2008-12-31 | 12678.00 |   70402.00
(2 rows)

La seconda query non riesce perché tenta di fare riferimento alla tabella HOLIDAYS nella query principale e nella sottoquery elenco SELECT. I riferimenti della query principale sono fuori ambito.


select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join holidays on sales.dateid=holidays.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

ERROR:  relation "holidays" does not exist

Esempio: CTE ricorsiva

Di seguito è riportato un esempio di CTE ricorsiva che restituisce i dipendenti che riportano direttamente o indirettamente a John. La query ricorsiva contiene una clausola WHERE per limitare la profondità di ricorsione a meno di 4 livelli.


--create and populate the sample table
  create table employee (
  id int,
  name varchar (20),
  manager_id int
  );
  
  insert into employee(id, name, manager_id)  values
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
  
--run the recursive query
  with recursive john_org(id, name, manager_id, level) as
( select id, name, manager_id, 1 as level
  from employee
  where name = 'John'
  union all
  select e.id, e.name, e.manager_id, level + 1 as next_level
  from employee e, john_org j
  where e.manager_id = j.id and level < 4
  )
 select distinct id, name, manager_id from john_org order by manager_id;

Di seguito è riportato il risultato della query.


    id        name      manager_id
  ------+-----------+--------------
   101    John           100
   102    Jorge          101
   103    Kwaku          101
   110    Liu            101
   201    Sofía          102
   106    Mateo          102
   110    Nikki          103
   104    Paulo          103
   105    Richard        103
   120    Saanvi         104
   200    Shirley        104
   205    Zhang          104