Righe Top-N per gruppo con ROW_NUMBER

Una delle domande più comuni nei colloqui SQL sembra semplice: "Restituisca i 3 dipendenti con lo stipendio più alto in ogni reparto." I candidati che ricorrono subito a LIMIT sbagliano, perché LIMIT limita l'intero set di risultati, non ogni gruppo.

L'intervistatore verifica se conosce le funzioni finestra. La risposta canonica è: numerare le righe all'interno di ogni gruppo, quindi mantenere le righe il cui numero è ≤ N. Questa lezione costruisce questo schema passo dopo passo.

Supponga di scrivere la query seguente. Restituisce solo 3 righe in totale nell'intera tabella, non 3 per reparto.

LIMIT (oppure TOP o FETCH FIRST) opera sul set di risultati finale. In SQL standard non esiste un LIMIT per gruppo. Quando in un colloquio propone LIMIT 3 per un problema relativo ai gruppi, dimostra di non aver ancora assimilato il concetto di partizionamento.

ROW_NUMBER() è una funzione finestra che assegna a ogni riga un intero univoco e senza interruzioni in base a un ordinamento. Utilizzata da sola, numera l'intero risultato.

L'elemento decisivo è PARTITION BY: riavvia la numerazione da 1 per ogni gruppo. Combini PARTITION BY department con ORDER BY salary DESC e ogni reparto riceve la propria sequenza 1, 2, 3, ... in base allo stipendio.

Dopo aver eseguito la query precedente, ogni riga contiene un valore rn. All'interno di ogni reparto, lo stipendio più alto riceve rn = 1, quello successivo 2 e così via. Quando inizia un nuovo reparto, la numerazione riparte da 1.

• Vendite: Ana (1), Bo (2), Cal (3), Dee (4)
• Ingegneria: Eve (1), Fin (2), Gus (3)

Ora "top 3 per reparto" significa semplicemente "mantenere le righe in cui rn <= 3".

Il passo successivo più naturale è WHERE rn <= 3, ma non funziona. Le funzioni finestra vengono calcolate dopo la clausola WHERE nell'ordine logico di esecuzione, quindi l'alias rn non esiste ancora quando viene eseguito WHERE.

Gli intervistatori amano questa insidia. La soluzione consiste nel calcolare la funzione finestra in una sottoquery o CTE, quindi filtrare il risultato di quella query interna in una query esterna.

Racchiuda la numerazione in una CTE denominata ranked, quindi selezioni i dati da essa applicando il filtro nel WHERE della query esterna. È la risposta che gli intervistatori vogliono vedere ed è anche molto leggibile.

Memorizzi questo schema: partizionare per gruppo, ordinare per metrica, filtrare rn ≤ N nella query esterna. Si generalizza a top-1, top-5 o a qualsiasi N modificando un solo numero.

Se il dialetto usato dall'intervistatore è meno recente o preferisce le sottoquery, la stessa logica può essere inserita in una tabella derivata nella clausola FROM. Ricordi che una tabella derivata deve avere un alias (r in questo caso), altrimenti si verifica un errore di sintassi.

Le forme con CTE e con tabella derivata sono intercambiabili per questo problema. Scelga quella che l'intervistatore ritiene più leggibile: sono entrambe corrette.

"Trovi il dipendente con lo stipendio più alto in ogni reparto" equivale semplicemente a N = 1. Imposti il filtro su rn = 1.

Perché non usare MAX(salary) con GROUP BY department? Perché MAX restituisce il valore dello stipendio, ma non il resto della riga di quel dipendente, come nome, data di assunzione e così via. ROW_NUMBER mantiene intatta l'intera riga vincente, che di solito è ciò che la domanda richiede realmente.

ROW_NUMBER restituisce sempre esattamente N righe, anche quando gli stipendi sono uguali. Tuttavia, quale riga a pari merito riceva rn = 1 è arbitrario se non si risolve il pari merito. Se due persone guadagnano 90000 e si mantiene solo rn = 1, la riga scelta può variare tra un'esecuzione e l'altra.

Aggiunga una chiave di ordinamento secondaria e univoca, ad esempio employee_id, per rendere il risultato stabile e riproducibile. Gli intervistatori apprezzano chi menziona spontaneamente il determinismo.

Data una tabella sales con region, product e revenue, restituisca i 2 prodotti con il fatturato più alto per regione. La procedura è la stessa: partizionare per region, ordinare per revenue DESC e mantenere rn <= 2.

Noti che cambiano solo la colonna di partizione e la colonna della metrica. La struttura è identica, indipendentemente dal settore.

Per dimostrare una preparazione che va oltre la correttezza, menzioni:

• Un indice su (department, salary DESC) aiuta il motore a produrre in modo efficiente righe ordinate per partizione.
• L'approccio con funzioni finestra esegue una sola scansione della tabella, risultando molto più efficiente di una sottoquery correlata eseguita per ogni riga.
• Per casi top-N molto grandi con N = 1, alcuni motori supportano DISTINCT ON (Postgres) come scorciatoia, ma ROW_NUMBER è lo standard portabile.

Dichiari sempre il criterio di spareggio e verifichi il valore di N richiesto.

Verifichi la Sua comprensione dello schema top-N per gruppo.

Lo schema in una frase: partizionare per gruppo, ordinare per metrica, assegnare ROW_NUMBER, quindi mantenere rn ≤ N in una query esterna.

• LIMIT limita l'intero insieme, mai i singoli gruppi.
• Non può filtrare l'alias della funzione finestra in WHERE; lo racchiuda in una CTE o sottoquery.
• Aggiunga un criterio di spareggio univoco per ottenere risultati deterministici.
• Top-1 mantiene l'intera riga vincente, a differenza di MAX + GROUP BY.

Modificando un solo numero, la stessa query risolve i casi top-1, top-5 o qualsiasi N.