5 esempi pratici di CTE SQL

Le espressioni comuni di tabella (CTE) sono state introdotte in SQL per migliorare la leggibilità e la struttura delle query SQL, in particolare quelle che richiedono più passaggi per ottenere il risultato necessario. In questo articolo, esamineremo diversi esempi per mostrare come le CTE di SQL possono aiutarvi con calcoli complessi e strutture di dati gerarchiche.

Espressioni di tabella comuni in SQL

Le espressioni di tabella comuni (CTE), chiamate anche clausole WITH, consentono di creare sottoquery denominate che vengono poi richiamate nella query principale. Le CTE sono state introdotte in SQL per migliorare la leggibilità e la struttura di un'istruzione SQL.

La sintassi di base delle CTE è la seguente:

WITH subquery_name AS
(SELECT … subquery ...)
SELECT … main query ...

Si inizia con la parola chiave WITH seguita dal nome che si assegna alla CTE (subquery). Quindi si inserisce la parola chiave AS e si include la sottoquery tra parentesi. Dopo aver definito la CTE, si passa alla query principale, dove si può fare riferimento alla CTE con il suo nome.

Se non si conoscono le CTE, si consiglia di consultare questo articolo che spiega in modo più dettagliato come funzionano le CTE.

È possibile avere più CTE in una query, fare riferimento a una CTE all'interno di un'altra (cioè le CTE annidate) o persino fare riferimento a una CTE all'interno di se stessa (CTE ricorsive). Questo ci offre un gran numero di strumenti e opportunità.

Esempi di CTE SQL

Per mostrare come le CTE possono aiutarvi in vari compiti analitici, vi illustrerò cinque esempi pratici.

Inizieremo con la tabella orderscon alcune informazioni di base come la data dell'ordine, l'ID del cliente, il nome del negozio, l'ID del dipendente che ha registrato l'ordine e l'importo totale dell'ordine.

Ora scriviamo un paio di query SQL! Potete anche esercitarvi con le CTE SQL in questo corso interattivo Query ricorsive che tratta tutti i tipi di CTE.

Esempio 1

Nel nostro primo esempio, vogliamo confrontare l'importo totale di ogni ordine con l'importo medio degli ordini del negozio corrispondente.

Possiamo iniziare calcolando l'importo medio degli ordini per ogni negozio con una CTE e aggiungendo questa colonna all'output della query principale:

WITH avg_per_store AS
  (SELECT store, AVG(amount) AS average_order
   FROM orders
   GROUP BY store)
SELECT o.id, o.store, o.amount, avg.average_order AS avg_for_store
FROM orders o
JOIN avg_per_store avg
ON o.store = avg.store;

Come si vede, la nostra query inizia con una CTE chiamata avg_per_store. Utilizzando questa CTE, creiamo una tabella che elenca tutti i negozi e l'importo medio degli ordini per negozio. Quindi, nella query principale, selezioniamo per visualizzare l'ID dell'ordine, il nome del negozio, l'importo dell'ordine dalla tabella originale e l'importo medio dell'ordine per il negozio. orders e l'importo medio dell'ordine per ogni negozio (avg_for_store) dalla CTE definita in precedenza.

Ecco l'output:

Con questa tabella, possiamo vedere come ogni ordine si confronta con l'importo medio dell'ordine nel negozio corrispondente.

Passiamo ora a un esempio più complesso.

Esempio 2

In questo caso, confronteremo diversi negozi. In particolare, vogliamo vedere come l'importo medio degli ordini di ciascun negozio si confronta con il minimo e il massimo dell'importo medio degli ordini di tutti i negozi.

Come nel primo esempio, inizieremo calcolando l'importo medio degli ordini per ogni negozio con una CTE. Poi, definiremo altre due CTE:

• Per calcolare il minimo dell'importo medio dell'ordine tra tutti i negozi.
• Per calcolare il massimo dell'importo medio degli ordini tra tutti i negozi.

Si noti che queste due CTE utilizzeranno il risultato della prima CTE.

Infine, nella query principale, uniremo tutte e tre le CTE per ottenere le informazioni necessarie:

WITH avg_per_store AS (
    SELECT store, AVG(amount) AS average_order
    FROM orders
    GROUP BY store),
    min_order_store AS (
    SELECT MIN (average_order) AS min_avg_order_store
    FROM avg_per_store),
    max_order_store AS (
    SELECT MAX (average_order) AS max_avg_order_store
    FROM avg_per_store)
SELECT avg.store, avg.average_order, min.min_avg_order_store,
max.max_avg_order_store
FROM avg_per_store avg
CROSS JOIN min_order_store min
CROSS JOIN max_order_store max;

Come si vede, anche con più CTE annidate, la query SQL rimane pulita e facile da seguire. Se si usassero le subquery, sarebbe necessario annidare una subquery all'interno delle altre due e ripeterla più volte all'interno della stessa query. Qui, con le CTE, definiamo semplicemente tutte e tre le CTE all'inizio e le richiamiamo quando necessario.

Ecco l'output di questa query:

Si può facilmente vedere come ogni negozio si confronta con gli altri in termini di importo medio degli ordini. Naturalmente, quando si hanno solo tre negozi, si può fare un confronto senza aggiungere le colonne min_avg_order_store e max_avg_order_store. Tuttavia, quando è necessario analizzare le prestazioni di molti negozi in base a diverse metriche, questo approccio potrebbe essere molto utile.

Leggete questa guida per imparare le migliori pratiche di SQL CTE.

Esempio 3

Nel prossimo esempio, continueremo a confrontare le prestazioni dei nostri negozi, ma con alcune metriche diverse. Supponiamo che la nostra azienda consideri piccoli gli ordini inferiori a 200 dollari e grandi gli ordini uguali o superiori a 200 dollari. Ora, vogliamo calcolare quanti ordini grandi e quanti ordini piccoli ha avuto ogni negozio.

Per svolgere questo compito utilizzando le clausole di WITH, abbiamo bisogno di due comuni espressioni di tabella:

• Per ottenere il numero di ordini grandi per ogni negozio.
• Per ottenere il numero di ordini piccoli per ogni negozio.

Alcuni negozi potrebbero non avere né ordini grandi né ordini piccoli, il che porterebbe ai valori NULL. Dobbiamo assicurarci di non perdere nessun negozio durante le JOIN. Per questo motivo, preferisco avere un'altra CTE che produce semplicemente un elenco di tutti i negozi. Poi, nella query principale, uniremo questa CTE con le due CTE contenenti le metriche sugli ordini grandi e piccoli:

WITH stores AS
   (SELECT store
    FROM orders
    GROUP BY store),
  big AS
  (SELECT store, COUNT(*) AS big_orders
   FROM orders
   WHERE amount >= 200.00
   GROUP BY store),
  small AS
  (SELECT store, COUNT(*) AS small_orders
   FROM orders
   WHERE amount < 200.00
   GROUP BY store)
SELECT s.store, b.big_orders, sm.small_orders
FROM stores s
FULL JOIN big b
ON s.store = b.store
FULL JOIN small sm
ON s.store = sm.store;

Quindi, in questa query

• Definire la CTE stores per ottenere l'elenco completo dei negozi.
• Definire la CTE big per calcolare, per ogni negozio, il numero di ordini con importo totale pari o superiore a 200 dollari.
• Definire la CTE small per calcolare, per ogni negozio, il numero di ordini inferiori a 200 dollari.
• Unire le tre CTE.

Ecco l'output:

Possiamo ora notare che il negozio West ha un'ottima performance: tutti i suoi ordini sono superiori a 200 dollari. Anche il negozio Center si comporta bene, con due ordini superiori a 200 dollari e uno inferiore a 200 dollari. Solo la metà degli ordini del negozio East è grande, con due ordini superiori a 200 dollari e due ordini inferiori a 200 dollari.

Esempio 4

Per i prossimi due esempi, utilizzeremo la tabella sottostante con alcune informazioni di base sui dipendenti della nostra azienda. In particolare, abbiamo l'ID del dipendente, il nome, il cognome, l'ID del suo superiore, il reparto e l'importo dell'ultimo bonus.

Ora calcoliamo il bonus medio per reparto, quindi contiamo quanti dipendenti hanno avuto bonus superiori alla media del rispettivo reparto e quanti inferiori.

Le espressioni di tabella comuni possono essere molto utili per calcoli così complessi. In questa query SQL avremo tre CTE:

• Calcolare l'importo medio dei bonus per ogni reparto.
• Calcolare, per reparto, il numero di dipendenti i cui bonus erano superiori alla media del rispettivo reparto.
• Calcolare, per reparto, il numero di dipendenti i cui bonus sono stati inferiori alla media del rispettivo reparto.

Nella query principale, uniremo tutte e tre le CTE.

WITH avg_bonus_department AS
    (SELECT department, AVG(bonus) AS average_bonus
    FROM employees
    GROUP BY department),
    above_average AS
    (SELECT e.department, count(*) AS employees_above_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus > average_bonus
     GROUP BY e.department),
     below_average AS
     (SELECT e.department, count(*) AS employees_below_average
     FROM employees e
     JOIN avg_bonus_department avg
     ON e.department = avg.department
     WHERE bonus < average_bonus
     GROUP BY e.department)
SELECT avg.department, avg.average_bonus, aa.employees_above_average, ba.employees_below_average
FROM avg_bonus_department avg
LEFT JOIN above_average aa
ON avg.department = aa.department
LEFT JOIN below_average ba
ON avg.department = ba.department;

Ecco il risultato della query:

Poiché c'è solo una persona in Finanza, il bonus medio del reparto è esattamente uguale al bonus di questa persona. Di conseguenza, nel dipartimento Finance non c'è nessuno che abbia un bonus superiore o inferiore alla media (che si riflette come valore NULL nel risultato). Lo stesso vale per l'amministratore delegato.

Per quanto riguarda il reparto Vendite, possiamo vedere che il bonus medio è stato di 828,57 dollari e che solo due persone su sette hanno avuto bonus superiori alla media del reparto.

Lasciamo a voi la possibilità di interpretare allo stesso modo i risultati dei reparti Marketing e Operazioni e passiamo a un esempio ancora più complesso con una query ricorsiva.

Esempio 5

Lecomuni espressioni di tabella possono fare riferimento a se stesse, il che le rende uno strumento perfetto per analizzare le strutture gerarchiche. Vediamo un esempio.

Utilizzando le informazioni della tabella employees e della tabella orders possiamo disegnare la seguente struttura organizzativa della nostra azienda. Il personale del negozio è considerato parte del team delle vendite. Inoltre, nella orders tabella, possiamo vedere quali dipendenti hanno ordini in quali negozi, quindi possiamo ricavare il negozio a cui appartiene ogni addetto alle vendite.

Ora, supponiamo di dover scoprire il livello di ciascun dipendente nella struttura organizzativa (ad esempio, il livello 1 è quello dell'amministratore delegato, il livello 2 è quello dei suoi riporti diretti, ecc.) Possiamo aggiungere una colonna che lo mostri con una query ricorsiva:

WITH RECURSIVE levels AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    1 AS level
  FROM employees
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employees.id,
    employees.first_name,
    employees.last_name,
    employees.superior_id,
    levels.level + 1
  FROM employees, levels
  WHERE employees.superior_id = levels.id
)

SELECT *
FROM levels;

Come si vede, la CTE levels in questa query fa riferimento a se stessa. Inizia selezionando il record corrispondente al grande capo, quello che non ha un superiore (cioè superior_id IS NULL). Assegniamo 1 al livello di questa persona, poi usiamo UNION ALL per aggiungere altri record, aggiungendone uno per ogni livello di gestione nella struttura organizzativa.

Ecco l'output:

L'argomento delle query ricorsive è piuttosto impegnativo, quindi non entrerò nei dettagli in questa sede. Ma vi invito a consultare questo articolo che spiega le CTE ricorsive con esempi, soprattutto se lavorate con dati gerarchici.

E consultate questo articolo per altri esempi di CTE SQL.

Esercitiamoci con le CTE SQL!

Spero che questi esempi vi abbiano mostrato quanto possano essere utili le CTE per diversi compiti analitici. Esse contribuiscono a migliorare la leggibilità e la struttura delle query SQL, aiutano a eseguire calcoli complessi e annidati e sono utili per elaborare in modo efficiente i dati gerarchici. Per saperne di più su quando utilizzare le CTE, consultate questo articolo.

Se volete imparare a conoscere le espressioni di tabella comuni, vi consiglio di iniziare con questo corso unico Query ricorsive corso. Include 114 esercizi interattivi che coprono tutti i tipi di CTE, compresi i CTE semplici, i CTE annidati e i CTE ricorsivi. Alla fine del corso, saprete come gestire le query SQL con le CTE, come e quando annidare le CTE e come utilizzare le CTE ricorsive per spostarsi nei modelli di dati gerarchici.

Se desiderate acquisire la padronanza di altri strumenti avanzati per l'analisi dei dati con SQL, dovreste prendere in considerazione la possibilità di partecipare al SQL avanzato ! Il corso va oltre le CTE e copre anche le funzioni finestra e le estensioni GROUP BY in SQL. E LearnSQL.it offre molti modi diversi per esercitarsi online su questi concetti avanzati di SQL.

Grazie per aver letto e buon apprendimento!