Aggregati per gruppo senza GROUP BY
Usare una sottoquery correlata per calcolare il massimo di un gruppo accanto alle righe di dettaglio
Aggregati per gruppo senza GROUP BY è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.
Il problema del dettaglio più aggregato
Una richiesta tipica nei colloqui è: «Mostri ogni riga insieme a un aggregato del relativo gruppo.» Per esempio, elenchi ogni dipendente con lo stipendio massimo del suo reparto sulla stessa riga.
Un semplice GROUP BY accorpa le righe, quindi non può conservare il dettaglio dei singoli dipendenti. Servono insieme le righe di dettaglio e un numero a livello di gruppo.
Una sottoquery correlata risolve il problema con eleganza: calcola l'aggregato del gruppo per ogni riga di dettaglio senza accorpare nulla.
Perché un GROUP BY semplice non funziona qui
Se scrive SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id, ottiene una riga per reparto e perde i nomi individuali.
Aggiungere name a SELECT senza aggiungerlo a GROUP BY genera il classico errore «la colonna deve comparire in GROUP BY».
L'intervistatore verifica se comprende che GROUP BY riduce la cardinalità. Per conservare le righe di dettaglio, deve calcolare l'aggregato in un altro modo.
La sottoquery correlata in aiuto
Inserisca l'aggregato del gruppo nella lista SELECT come sottoquery correlata. Ogni riga del dipendente attiva un MAX interno limitato al reparto di quel dipendente.
La correlazione e2.dept_id = e1.dept_id collega l'aggregato al gruppo corretto, mentre la query esterna continua a restituire una riga per dipendente.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_max_salary
FROM employees e1;Confrontare ogni riga con il proprio gruppo
Una volta inserito l'aggregato del gruppo nella query, può confrontarlo con ogni riga. Una domanda frequente è: «Trovi i dipendenti che guadagnano più della media del loro reparto.»
Qui l'AVG correlato si trova in WHERE, quindi ogni dipendente viene confrontato con la media del proprio reparto.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Calcolare la differenza rispetto al gruppo
Può anche mostrare quanto ogni riga si discosta dall'aggregato del proprio gruppo. Sottraendo la media correlata si ottiene uno scarto per riga.
Noti che la stessa sottoquery correlata può essere riutilizzata in più espressioni SELECT; il motore la valuta per ogni riga ogni volta che compare.
SELECT e1.name,
e1.salary,
e1.salary - (SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS gap_from_dept_avg
FROM employees e1;Trovare il dipendente più pagato per gruppo
Per restituire solo la persona con lo stipendio più alto in ogni reparto, confronti ogni stipendio con il MAX correlato e mantenga le corrispondenze.
Questo schema restituisce anche i pari merito: se due dipendenti condividono lo stipendio massimo del reparto, compaiono entrambi. La gestione dei pari merito è spesso la domanda successiva dell'intervistatore.
SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);L'alternativa delle funzioni finestra
SQL moderno offre uno strumento più pulito: le funzioni finestra. MAX(salary) OVER (PARTITION BY dept_id) calcola l'aggregato del gruppo senza accorpare le righe e senza una nuova scansione correlata.
Gli intervistatori apprezzano quando sa proporre entrambe le soluzioni e spiegare che la versione con funzione finestra di solito offre prestazioni migliori perché scansiona la tabella una sola volta.
SELECT name,
dept_id,
salary,
MAX(salary) OVER (PARTITION BY dept_id) AS dept_max_salary
FROM employees;Confronto tra sottoquery correlate e funzioni finestra
Entrambi gli approcci restituiscono la stessa struttura dei risultati, ma differiscono:
- Sottoquery correlata: portabile, funziona anche con motori molto datati, ma viene rivalutata per ogni riga.
- Funzione finestra: un'unica scansione, molto più veloce su tabelle grandi, ma richiede il supporto alle funzioni finestra in SQL.
Dica quale sceglierebbe e perché. Per un'esecuzione una tantum su una tabella piccola vanno bene entrambe; per l'analisi su larga scala, preferisca la funzione finestra.
Esempio svolto: ordini sopra la media del cliente
Applichi lo schema agli ordini. Mostri gli ordini il cui importo supera la media personale degli ordini del cliente che li ha effettuati.
L'AVG correlato è limitato da o2.customer_id = o1.customer_id, fornendo a ogni ordine il valore di riferimento personale del cliente.
SELECT o1.order_id, o1.customer_id, o1.amount
FROM orders o1
WHERE o1.amount > (
SELECT AVG(o2.amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);Attenzione ai casi NULL e ai gruppi vuoti
Se un gruppo contiene una sola riga, la sua media coincide con quella riga, quindi salary > avg è falso e la riga viene esclusa. Citi spontaneamente questo caso limite.
Inoltre, gli stipendi NULL vengono ignorati da AVG e MAX, come previsto dalla semantica degli aggregati SQL. Se ogni valore di un gruppo è NULL, l'aggregato è NULL e i confronti diventano UNKNOWN, escludendo la riga. Anticipare questi casi distingue una risposta approfondita.
Calcolare la posizione all'interno di un gruppo
Può esprimere la posizione di una riga all'interno del proprio gruppo con un COUNT correlato. Per trovare la posizione dello stipendio di ogni dipendente nel proprio reparto, conti quanti colleghi guadagnano di più.
La posizione 1 indica lo stipendio più alto. Aggiungere 1 trasforma il conteggio dei dipendenti con uno stipendio maggiore in una posizione numerata a partire da 1, mentre la correlazione mantiene il confronto limitato al reparto.
SELECT e1.name,
e1.dept_id,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.salary > e1.salary) AS salary_rank_in_dept
FROM employees e1;Verifica rapida
Scelga il motivo per cui una sottoquery correlata è migliore di un semplice GROUP BY per questa attività.
Riepilogo: aggregati per gruppo senza GROUP BY
Punti chiave:
- Una sottoquery correlata inserisce un aggregato a livello di gruppo su ogni riga di dettaglio senza accorpare le righe.
- La usi in SELECT per mostrare l'aggregato oppure in WHERE per confrontare ogni riga con il proprio gruppo.
- Lo schema
= MAX(...)restituisce tutte le righe al primo posto a pari merito. - Una funzione finestra con
PARTITION BYfa lo stesso in un'unica scansione e in genere si adatta meglio all'aumentare dei dati.
Proponga entrambe le soluzioni e motivi la scelta durante il colloquio.
Domande Frequenti
La lezione «Aggregati per gruppo senza GROUP BY» è gratuita?
Sì — il testo completo di «Aggregati per gruppo senza GROUP BY» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.
Cosa imparerò in «Aggregati per gruppo senza GROUP BY»?
Usare una sottoquery correlata per calcolare il massimo di un gruppo accanto alle righe di dettaglio Eserciti Coding Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare Coding Interview Prep?
Non è richiesta alcuna esperienza precedente. Coding Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.
Quanto tempo richiede la lezione «Aggregati per gruppo senza GROUP BY»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione Coding Interview Prep?
Sì. Ogni lezione Coding Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Anatomia di una sottoquery correlata
- Aggregati per gruppo senza GROUP BY
- EXISTS e NOT EXISTS correlati
- Riscrivere le sottoquery correlate come join