SQL WITH

SQL > SQL Avanzato > WITH

A volte ti trovi di fronte a una domanda complessa che non può essere facilmente risolta con una singola istruzione SQL.

Potresti cercare di ottenere la risposta in un'unica operazione utilizzando diverse viste inline o diverse sottoquery, ma questo renderebbe probabilmente il tuo SQL difficile da comprendere, e alcune varianti di SQL, come Hive SQL, non consentono più di una sottoquery.

Un secondo approccio è suddividere la complessità in diverse fasi diverse. Un modo per farlo è creare diverse tabelle, ognuna delle quali conserva i risultati di ciascuna fase. Questa è spesso una buona strategia, poiché semplifica il debug e rende più agevole seguire come il codice viene eseguito.

Tuttavia, l'effetto collaterale indesiderato è che devi ricordarti di eliminare queste tabelle dopo aver completato la tua analisi; in caso contrario, queste tabelle rimangono nel database, creando così problemi di gestione del database.

L'alternativa alla creazione di più tabelle è utilizzare la clausola WITH in SQL.

Sintassi

La sintassi di base per la clausola WITH è la seguente:

WITH <nome_query_1> AS (
SELECT Dichiarazione 1
)
Dichiarazione SELECT Principale

L'uso della clausola WITH è molto simile alla creazione di tabelle. Quando si crea una tabella, le si assegna un nome. Allo stesso modo, quando si utilizza la clausola WITH, si assegna anche un nome, e questo nome funge essenzialmente da nome della tabella nella dichiarazione SQL principale.

Poiché la clausola WITH non crea una tabella o una vista, l'oggetto associato alla clausola WITH scompare dopo l'esecuzione della dichiarazione SQL principale, e non c'è nulla da pulire.

È anche possibile avere più clausole WITH. La sintassi è la seguente:

WITH <nome_query_1> AS (
SELECT Dichiarazione 1
), <nome_query_2> AS (
SELECT Dichiarazione 2
),
..
<nome_query_n> AS (
SELECT Dichiarazione N
)
Dichiarazione SELECT Principale

Per avere più clausole WITH, non è necessario specificare WITH più volte. Dopo che la prima clausola WITH è completata, aggiungi una virgola e quindi puoi specificare la clausola successiva iniziando con <nome_query> seguito da COME. Non c'è una virgola tra l'ultima clausola WITH e la dichiarazione SQL principale.

Esempi

Utilizziamo la seguente tabella nei nostri esempi.

Tabella Store_Sales

Store_Name Sales

Los Angeles 15000

San Diego 2500

New York 3000

Boston 7000

Esempio 1: Utilizzo di With in una dichiarazione SELECT

Diciamo che vogliamo elencare tutti i negozi che hanno vendite superiori alla media. Per fare ciò, possiamo utilizzare la seguente dichiarazione WITH:

WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

Risultato:

Store_Name	Sales
Los Angeles	15000
Boston	7000

La dichiarazione WITH calcola l'importo medio delle vendite (che è di $6.875), quindi la query SQL principale restituisce semplicemente tutte le righe in cui il valore della colonna Vendite è maggiore di questa cifra media.

Un equivalente SQL utilizzando una vista inline sarebbe:

SELECT a1.* FROM Store_Sales a1,
(SELECT AVG(Sales) AVG_SALES FROM Store_Sales) t1
WHERE a1.Sales > t1.AVG_SALES;

Analogamente, questo può essere ottenuto tramite la seguente SQL utilizzando una sottoquery:

SELECT a1.* FROM Store_Sales a1
WHERE a1.Sales >
(SELECT AVG(Sales) AVG_SALES FROM Store_Sales);

Si noti che la versione con la sottoquery non funziona in Hive SQL a causa del modo in cui Hive SQL supporta le sottoquery.

Esempio 2: Utilizzare WITH in una dichiarazione CREATE TABLE

Possiamo anche utilizzare WITH insieme a una dichiarazione CREATE TABLE. Diciamo che vogliamo creare una tabella utilizzando la clausola WITH nell'esempio precedente, scriveremmo:

CREATE TABLE Above_Average_Sales
AS
WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

La parte WITH della query viene dopo CREATE TABLE AS. Non mettere la clausola WITH all'inizio. La query sottostante restituirà un errore:

WITH t1 AS (
SELECT AVG(Sales) AVG_SALES FROM Store_Sales
)
CREATE TABLE Above_Average_Sales
AS
SELECT a1.* FROM Store_Sales a1, t1
WHERE a1.Sales > t1.AVG_SALES;

SQL EXISTS >>

Questa pagina è stata aggiornata l'ultima volta il 15/10/2023

	SQL WITH
Tutorial de SQL Comandi SQL Modifica delle Tabelle SQL Avanzato Sintassi SQL