0Pricing
SQL Interview Prep · Lezione

Il reddito più alto per reparto

Combinare partizionamento e classificazione per risolvere problemi di stipendio Top-N raggruppati

Il reddito più alto per reparto è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 3 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 SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

Dal ranking globale al ranking per gruppo

Il passaggio successivo è: "Trovare il dipendente con lo stipendio più alto in ogni reparto." Questa richiesta combina ranking e raggruppamento ed è una domanda tipica per un livello intermedio.

Supponete di avere una tabella employee con id, name, department_id e salary. Vogliamo il dipendente con lo stipendio più alto per ogni reparto, oppure più dipendenti in caso di pari merito, non soltanto il massimo globale.

Il nuovo strumento fondamentale è PARTITION BY, che riavvia il ranking all'interno di ogni reparto.

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY reimposta il ranking

Aggiungere PARTITION BY department_id alla finestra indica al database di calcolare il ranking in modo indipendente all'interno di ogni reparto.

Ogni reparto inizia dal proprio rango 1. Pertanto, il dipendente con lo stipendio più alto nel reparto 1 e quello con lo stipendio più alto nel reparto 5 ricevono entrambi il rango 1. Senza il partizionamento, soltanto il massimo globale riceverebbe il rango 1.

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

Filtrare per il rango 1

Per mantenere soltanto i dipendenti con gli stipendi più alti, racchiudete la query con il ranking e filtrate per il rango 1. Come sempre, la funzione finestra deve essere calcolata in una sottoquery o in una CTE prima di poterla usare in un filtro.

Usare DENSE_RANK (o RANK) in questo caso significa che, se due dipendenti hanno lo stesso stipendio massimo in un reparto, vengono restituiti entrambi. Di solito questa è l'interpretazione corretta di "il dipendente con lo stipendio più alto".

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

ROW_NUMBER quando ne vuole esattamente uno

A volte l'intervistatore vuole esattamente una riga per dipartimento anche in caso di parità. In questo caso usi ROW_NUMBER e aggiunga un criterio di spareggio deterministico, ad esempio l'id più basso.

Senza il criterio di spareggio, le parità vengono risolte arbitrariamente e il risultato non è deterministico. Aggiungendo , id ASC la scelta diventa ripetibile.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

DENSE_RANK vs ROW_NUMBER vs RANK in questo caso

Scelga in base alla formulazione esatta:

  • DENSE_RANK = 1: tutti i dipendenti a pari merito per lo stipendio più alto in ogni dipartimento.
  • RANK = 1: è identico a DENSE_RANK per il primo posto; i salti contano solo sotto il primo posto.
  • ROW_NUMBER = 1: esattamente un dipendente per dipartimento, con le parità risolte dall'ORDER BY.

In un colloquio, è importante indicare quale soluzione ha scelto e perché.

L'approccio correlato precedente alle funzioni finestra

Prima delle funzioni finestra, la soluzione standard era una sottoquery correlata: si mantiene una riga solo se non esiste nessuno nello stesso dipartimento che guadagni di più.

In questo modo vengono naturalmente restituiti tutti i dipendenti con lo stipendio più alto a pari merito. È una soluzione portabile, ma può essere lenta perché il MAX interno viene valutato per ogni riga esterna, a meno che l'ottimizzatore non lo riscriva.

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

L'approccio con JOIN e GROUP BY

Un altro schema portabile consiste nel calcolare lo stipendio massimo per dipartimento con GROUP BY, quindi eseguire una JOIN per recuperare i dipendenti corrispondenti.

È una soluzione efficiente e chiara. La JOIN restituisce ogni dipendente il cui stipendio è uguale al massimo del proprio dipartimento, quindi le parità vengono mantenute.

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

I primi N per dipartimento

Lo schema si estende al caso «i 3 dipendenti con lo stipendio più alto per dipartimento» senza introdurre nuove idee. Basta modificare il filtro usando un intervallo.

Con DENSE_RANK, rnk <= 3 restituisce i tre livelli di stipendio distinti più alti, eventualmente con più di tre righe in caso di parità. Con ROW_NUMBER, rn <= 3 restituisce esattamente tre righe per dipartimento.

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

Esempio svolto

Dipartimento 1: Ana 120, Bob 120, Cara 90. Dipartimento 2: Dan 200, Eve 150.

  • DENSE_RANK = 1: Ana (120) e Bob (120) dal dipartimento 1; Dan (200) dal dipartimento 2. Tre righe.
  • ROW_NUMBER = 1 con id come criterio di spareggio: uno tra Ana e Bob, quello con l'id più basso, più Dan. Due righe.

Gli stessi dati possono produrre un numero diverso di righe a seconda della funzione. Scelga quella che corrisponde alla domanda.

Includere i dipartimenti e unire i nomi

Spesso gli intervistatori aggiungono una tabella department e chiedono il nome del dipartimento. Basta collegarla dopo aver eseguito il ranking.

Mantenga il ranking sulla tabella employee e colleghi la tabella di ricerca alla fine, così il partizionamento avviene ancora alla granularità corretta.

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

Errori da evitare

Errori comuni nel ranking per gruppo:

  • Dimenticare PARTITION BY ed eseguire il ranking globale, restituendo solo il dipendente con lo stipendio più alto dell'intera azienda.
  • Usare ROW_NUMBER quando la domanda implica che debbano comparire tutte le parità, eliminando così silenziosamente i dipendenti a pari merito per il primo posto.
  • Cercare di inserire direttamente la funzione finestra in WHERE invece di racchiuderla in una sottoquery.
  • Collegare la tabella dei dipartimenti prima del ranking e modificare accidentalmente la granularità del partizionamento.

Verifica rapida

Scelga la funzione di ranking corretta per il requisito.

Riepilogo

Per trovare il dipendente con lo stipendio più alto in ogni dipartimento, si usa lo schema del ranking globale aggiungendo PARTITION BY department_id:

  • DENSE_RANK = 1 restituisce tutti i dipendenti a pari merito per lo stipendio più alto di ogni dipartimento.
  • ROW_NUMBER = 1 con un criterio di spareggio restituisce esattamente un dipendente per dipartimento.
  • Alternative portabili: MAX correlato per dipartimento oppure il massimo calcolato con GROUP BY e poi collegato nuovamente alla tabella.

Per estendere la soluzione ai primi N, sostituisca = 1 con <= N. Dichiari esplicitamente come intende gestire le parità.

Domande Frequenti

La lezione «Il reddito più alto per reparto» è gratuita?

Sì — il testo completo di «Il reddito più alto per reparto» è 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 SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Il reddito più alto per reparto»?

Combinare partizionamento e classificazione per risolvere problemi di stipendio Top-N raggruppati Eserciti SQL 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 SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL 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 3 di 4.

Quanto tempo richiede la lezione «Il reddito più alto per reparto»?

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 SQL Interview Prep?

Sì. Ogni lezione SQL 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

  1. Il secondo stipendio più alto: cinque metodi
  2. L'ennesimo valore più alto con DENSE_RANK
  3. Il reddito più alto per reparto
  4. Restituire NULL quando non esiste l'ennesimo valore
← Torna a SQL Interview Prep