Combinando le subquery con la clausola GROUP BY in SQL, è possibile eseguire aggregazioni e analisi dei dati complesse in modo efficiente. In questo articolo, esploreremo i fondamenti delle subquery e del GROUP BY, oltre a metodi specifici per integrarli, esempi applicativi e punti di ottimizzazione delle prestazioni. Questo contenuto sarà utile per chi desidera migliorare le proprie competenze in SQL.
Fondamenti delle subquery
Una subquery è una query SQL inclusa all’interno di un’altra query SQL. Questo permette di costruire query complesse in modo incrementale, mantenendo temporaneamente i risultati per utilizzarli successivamente. Le subquery si utilizzano come segue:
Sintassi delle subquery
La sintassi di base di una subquery è la seguente:
SELECT colonna FROM tabella WHERE colonna = (SELECT colonna FROM tabella WHERE condizione);Tipi di subquery
Le subquery si dividono principalmente in tre tipi: subquery scalari, subquery di riga e subquery di tabella.
Subquery scalari
Una subquery che restituisce un singolo valore. Esempio:
SELECT name FROM employees WHERE id = (SELECT manager_id FROM departments WHERE name = 'Sales');Subquery di riga
Una subquery che restituisce una riga di dati. Esempio:
SELECT * FROM employees WHERE (department_id, salary) = (SELECT department_id, MAX(salary) FROM employees GROUP BY department_id);Subquery di tabella
Una subquery che restituisce più righe e colonne. Esempio:
SELECT name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');Specificare i seguenti elementi.
Fondamenti del GROUP BY
La clausola GROUP BY viene utilizzata in SQL per raggruppare i dati e aggregare ogni gruppo. Questo consente di aggregare e analizzare dati con attributi comuni.
Sintassi del GROUP BY
La sintassi di base della clausola GROUP BY è la seguente:
SELECT colonna, funzione_aggregata(colonna) FROM tabella GROUP BY colonna;Funzioni di aggregazione più comuni
Le funzioni di aggregazione più comunemente utilizzate con la clausola GROUP BY includono:
COUNT
Conta il numero di record. Esempio:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;SUM
Calcola la somma di una colonna. Esempio:
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;AVG
Calcola la media di una colonna. Esempio:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;MAX
Ottiene il valore massimo di una colonna. Esempio:
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;MIN
Ottiene il valore minimo di una colonna. Esempio:
SELECT department_id, MIN(salary) FROM employees GROUP BY department_id;Utilizzo della clausola HAVING
La clausola HAVING viene utilizzata per specificare condizioni dopo che i dati sono stati raggruppati con GROUP BY. Mentre la clausola WHERE specifica condizioni per le righe, HAVING le specifica per i gruppi. Esempio:
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING AVG(salary) > 50000;Esempi di integrazione di subquery e GROUP BY
Combinando le subquery con GROUP BY, è possibile eseguire aggregazioni e filtraggi complessi. Di seguito sono riportati alcuni esempi specifici.
Aggregazione di dati filtrati con subquery e GROUP BY
Nell’esempio seguente, si estraggono dati da una tabella di dipendenti che soddisfano determinate condizioni utilizzando una subquery, quindi si aggregano i risultati con GROUP BY.
SELECT department_id, AVG(salary) AS avg_salary
FROM (SELECT * FROM employees WHERE hire_date >= '2020-01-01') AS recent_hires
GROUP BY department_id;In questa query, la subquery estrae i dipendenti assunti dopo il 1 gennaio 2020, quindi si calcola lo stipendio medio per ogni dipartimento.
Utilizzo del risultato di un’aggregazione come subquery
Successivamente, vediamo un esempio in cui si utilizza il risultato di una aggregazione con GROUP BY come subquery in una query esterna.
SELECT department_id, avg_salary
FROM (SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id) AS department_avg
WHERE avg_salary > 60000;In questa query, si calcola prima lo stipendio medio per ogni dipartimento, e poi si filtrano i risultati per mostrare solo i dipartimenti con uno stipendio medio superiore a 60.000.
Aggregazione nidificata utilizzando subquery
Un esempio più complesso consiste nell’utilizzo di subquery nidificate per eseguire aggregazioni più dettagliate.
SELECT department_id, MAX(avg_salary)
FROM (SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id) AS department_avg
GROUP BY department_id;In questa query, si calcola prima lo stipendio medio per ogni dipartimento, e poi si estrae il dipartimento con lo stipendio medio massimo utilizzando una subquery nidificata.
Esempi applicativi delle aggregazioni con subquery
L’uso delle subquery consente di eseguire aggregazioni e analisi avanzate. Di seguito sono riportati alcuni esempi applicativi.
Creazione di un ranking con subquery
Nell’esempio seguente, si crea un ranking degli stipendi all’interno di ciascun dipartimento sulla base degli stipendi dei dipendenti.
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;Questa query ordina gli stipendi dei dipendenti in ordine decrescente all’interno di ciascun dipartimento e calcola il ranking degli stipendi utilizzando la funzione RANK().
Calcolo delle percentuali utilizzando i risultati aggregati
Nell’esempio seguente, si calcola la percentuale di dipendenti in ogni dipartimento rispetto al numero totale di dipendenti.
SELECT department_id,
COUNT(*) AS dept_employee_count,
(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees)) AS employee_percentage
FROM employees
GROUP BY department_id;Questa query utilizza una subquery per ottenere il numero totale di dipendenti e calcola la percentuale di dipendenti per ciascun dipartimento.
Query che combinano più aggregazioni
Nell’esempio seguente, si ottengono la media, il massimo e il minimo degli stipendi per ciascun dipartimento in una sola volta.
SELECT department_id,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;Questa query utilizza la clausola GROUP BY insieme a più funzioni di aggregazione per ottenere statistiche dettagliate sugli stipendi per ciascun dipartimento.
Aggregazioni condizionate
Un esempio di aggregazione condizionata è l’aggregazione di dati che soddisfano solo determinate condizioni.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2022-01-01'
GROUP BY department_id;Questa query calcola la media degli stipendi per ciascun dipartimento prendendo in considerazione solo i dipendenti assunti dal 1 gennaio 2022.
Punti di ottimizzazione delle prestazioni
Per utilizzare efficacemente le subquery e la clausola GROUP BY, è importante ottimizzare le prestazioni. Seguendo i punti riportati di seguito, è possibile migliorare la velocità di esecuzione delle query.
Utilizzo degli indici
Gli indici sono strutture di dati create su specifiche colonne di un database per migliorare la velocità di ricerca e aggregazione. È consigliabile creare indici sulle colonne utilizzate frequentemente in subquery o clausole GROUP BY.
CREATE INDEX idx_employees_hire_date ON employees(hire_date);
CREATE INDEX idx_employees_department_id ON employees(department_id);Verifica del piano EXPLAIN
EXPLAIN viene utilizzato per controllare il piano di esecuzione di una query SQL. Ciò consente di capire come verrà eseguita la query e di identificare eventuali colli di bottiglia.
EXPLAIN SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;Salvataggio del risultato di una subquery in una tabella temporanea
Salvare il risultato di una subquery in una tabella temporanea può evitare la riesecuzione della query e migliorare le prestazioni complessive.
CREATE TEMPORARY TABLE temp_recent_hires AS
SELECT * FROM employees WHERE hire_date >= '2020-01-01';
SELECT department_id, AVG(salary) FROM temp_recent_hires GROUP BY department_id;Utilizzo di tipi di dati appropriati
La scelta del tipo di dati influisce notevolmente sulle prestazioni delle query. Utilizzare tipi di dati appropriati può ridurre il consumo di memoria e migliorare la velocità di esecuzione delle query.
Evitare subquery ridondanti
Se una query contiene più subquery che restituiscono lo stesso risultato, è possibile ottimizzare la query unificandole, migliorando così l’efficienza della query.
SELECT department_id, AVG(salary)
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department_id;Errori comuni e come affrontarli
Quando si utilizzano subquery e la clausola GROUP BY, si possono verificare alcuni errori comuni. Ecco una guida su come affrontarli.
Errore: La subquery restituisce più righe
Questo errore si verifica quando una subquery restituisce più righe dove è previsto un singolo valore. Per risolvere, è possibile aggiungere LIMIT 1 alla subquery o utilizzare una funzione di aggregazione appropriata.
-- Esempio di subquery che restituisce più righe
SELECT name
FROM employees
WHERE id = (SELECT id FROM employees WHERE department_id = 1);
-- Soluzione: Utilizzo di LIMIT 1
SELECT name
FROM employees
WHERE id = (SELECT id FROM employees WHERE department_id = 1 LIMIT 1);Errore: Colonna non inclusa nel GROUP BY nella clausola SELECT
Se si include una colonna nella clausola SELECT che non è inclusa nella clausola GROUP BY, si verificherà un errore. Per risolvere, includere tutte le colonne nella clausola GROUP BY.
-- Esempio che genera un errore
SELECT department_id, name, AVG(salary)
FROM employees
GROUP BY department_id;
-- Soluzione: Aggiungere name al GROUP BY
SELECT department_id, name, AVG(salary)
FROM employees
GROUP BY department_id, name;Errore: Prestazioni scarse della subquery
Se una subquery è lenta, potrebbe essere necessario aggiungere indici o ottimizzare la query. Utilizzare una tabella temporanea per dividere la query può essere efficace.
-- Esempio di scarse prestazioni della subquery
SELECT department_id, (SELECT AVG(salary) FROM employees WHERE department_id = d.id)
FROM departments d;
-- Soluzione: Aggiungere un indice
CREATE INDEX idx_employees_department_id ON employees(department_id);
-- Oppure utilizzare una tabella temporanea
CREATE TEMPORARY TABLE temp_avg_salaries AS
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
SELECT d.id, t.avg_salary
FROM departments d
JOIN temp_avg_salaries t ON d.id = t.department_id;Errore: Memoria insufficiente
Query di grandi dimensioni con subquery o aggregazioni possono consumare molta memoria, portando a errori di memoria insufficiente. Per risolvere, dividere la query in più esecuzioni o regolare le impostazioni del database.
-- Esempio di esecuzione divisa di una query
CREATE TEMPORARY TABLE temp_large_query AS
SELECT * FROM large_table WHERE condition;
SELECT * FROM temp_large_query WHERE another_condition;Conclusione
Combinando efficacemente subquery e la clausola GROUP BY, è possibile migliorare notevolmente la potenza e la flessibilità delle query SQL. Partendo dall’uso di base, passando per esempi applicativi e punti di ottimizzazione delle prestazioni, è possibile eseguire aggregazioni e analisi complesse in modo efficiente. Comprendendo e affrontando i comuni errori, si possono creare query SQL più robuste e performanti. Utilizza queste tecniche per potenziare la tua gestione dei database.