SQL Avanzato in MySQL: Common Table Expressions (CTE) e Window Functions

MySQL 8.0 ha introdotto il supporto per due funzionalità potentissime dello standard SQL, che erano attese da tempo: le Common Table Expressions (CTE) e le Window Functions. Queste trasformano il modo in cui si scrivono le query analitiche complesse.

Common Table Expressions (CTE)

Una CTE, introdotta dalla clausola `WITH`, è un "set di risultati temporaneo e nominato" a cui puoi fare riferimento all'interno di una `SELECT`, `INSERT`, `UPDATE` o `DELETE`. Pensa a una CTE come a una "vista" o a una "tabella temporanea" che esiste solo per la durata di una singola query.

Rendono le query complesse, che prima richiedevano subquery annidate e illeggibili, molto più strutturate e facili da capire.

Esempio: Trovare i Reparti con uno Stipendio Medio Superiore alla Media Aziendale

WITH
  media_stipendi_aziendale AS (
    SELECT AVG(stipendio) AS media_globale FROM dipendenti
  ),
  media_stipendi_reparto AS (
    SELECT reparto, AVG(stipendio) AS media_reparto FROM dipendenti GROUP BY reparto
  )
SELECT reparto, media_reparto
FROM media_stipendi_reparto, media_stipendi_aziendale
WHERE media_reparto > media_globale;

Window Functions

Le Window Functions eseguono un calcolo su un insieme di righe (una "finestra") che sono in qualche modo correlate alla riga corrente. A differenza delle funzioni di aggregazione (`GROUP BY`), non comprimono le righe in un unico risultato; ogni riga mantiene la sua identità e ottiene un valore aggiuntivo calcolato sulla sua "finestra".

Sono potentissime per calcoli come ranking, totali progressivi e medie mobili.

Esempio: Assegnare un Ranking agli Stipendi per Reparto

SELECT
  nome,
  reparto,
  stipendio,
  RANK() OVER (PARTITION BY reparto ORDER BY stipendio DESC) as ranking_in_reparto
FROM dipendenti;

Questa query restituirà una riga per ogni dipendente, con una colonna aggiuntiva che indica la sua posizione in classifica (ranking) basata sullo stipendio, ma calcolata separatamente per ogni reparto (`PARTITION BY reparto`).