Come trovare l'ennesimo stipendio più alto per reparto con SQL

Imparate a trovare l'ennesimo stipendio più alto in SQL e imparerete come ottenere l'ennesimo valore in qualsiasi dato.

In questo articolo mi concentrerò su un problema e vi fornirò diverse soluzioni. Il problema è già indicato nel titolo: trovare l'ennesimo stipendio più alto per reparto utilizzando SQL.

Può sembrare troppo specifico. Ma imparando a risolverlo, sarete in grado di trovare l'ennesimo valore in qualsiasi dato, non solo negli stipendi. Avrete un'idea di come risolvere altri problemi simili in cui vi imbatterete.

Come ho detto, esamineremo un solo problema, ma esploreremo diverse soluzioni. In effetti, vi mostrerò quattro soluzioni utilizzando queste funzioni della finestra:

• NTH_VALUE()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Se avete bisogno di aiuto con la sintassi o con qualsiasi altro dettaglio relativo a queste funzioni, potete consultare la scheda informativa di SQL Window Functions . Potete anche dare un'occhiata a questo esempio per vedere come funzionano le funzioni finestra.

I dati utilizzati

Utilizzeremo i dati memorizzati nelle due tabelle seguenti:

• employee
• department

Ecco come appaiono i dati nella tabella employee come appaiono:

La tabella department ha i seguenti dati:

Qual è il compito?

Troviamo il terzo stipendio più alto per reparto. Ciò significa trovare il terzo valore più alto, non in generale ma all'interno di ogni sottoinsieme, dove un sottoinsieme contiene gli stipendi di un determinato reparto. Lo strumento più utile per farlo sono le funzioni finestra.

Ecco quindi la prima soluzione che utilizza una funzione finestra.

Utilizzo di NTH_VALUE()

Lo scopo della funzione NTH_VALUE() è quello di ottenere il valore dell'ennesima riga del dataset. Ecco come utilizzarla per ottenere il terzo stipendio più alto per reparto:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		NTH_VALUE (salary, 3) OVER (
PARTITION BY department_name ORDER BY salary DESC
			RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_highest_salary
FROM department d JOIN employee e ON d.id = e.department_id;

Questo codice seleziona innanzitutto alcune colonne dalle tabelle employee e department. Per utilizzare NTH_VALUE(), dobbiamo specificare la colonna e il valore di N. Poiché vogliamo ottenere il terzo stipendio più alto salary, la colonna è lo stipendio e N = 3; quindi, abbiamo NTH_VALUE(salary, 3). In questo modo otterremo il terzo stipendio più alto.

Affinché una funzione finestra funzioni, è necessario utilizzare una clausola OVER(). Ricordate che stiamo cercando il terzo stipendio più alto del dipartimento. ogni reparto. Per farlo, utilizziamo PARTITION BY per dividere i dati in sottoinsiemi in base alla colonna nome_dipartimento dalla tabella department. Poiché stiamo cercando il terzopiù alto stipendio, utilizziamo ORDER BY per far sì che le funzioni della finestra eseguano i calcoli dallo stipendio più alto a quello più basso. Questo è il motivo per cui ORDER BY stipendio DESC nel codice. Inoltre, abbiamo bisogno della clausola RANGE, in questo caso RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Ciò significa che la funzione finestra cercherà tra la prima e l'ultima riga della partizione.

Naturalmente, per ottenere tutto questo, dobbiamo unire le due tabelle.

Ecco il risultato:

Il risultato ci dice che il terzo stipendio più alto in Contabilità è 5.371,02, ovvero lo stipendio di Lois Skain. Lo stipendio di Abra Clemon (5.564,25) è il terzo più alto in Risorse umane. In Ricerca e sviluppo, lo stipendio di Zachariah Rapi è il terzo più alto (6.657,11).

Utilizzo di ROW_NUMBER()

La seconda opzione per ottenere il terzo stipendio più alto per reparto è utilizzare ROW_NUMBER(). Questa funzione della finestra restituisce i numeri di sequenza delle righe di un set di dati. Se ordiniamo gli stipendi all'interno di ciascun reparto, sarà facile scegliere il terzo più alto. Ecco come appare la soluzione:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		ROW_NUMBER() OVER (
PARTITION BY d.id ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
ORDER BY department_name;

Questa soluzione seleziona tutte le stesse colonne della soluzione precedente. La differenza è che qui usiamo ROW_NUMBER() invece di NTH_VALUE(). Tutti i criteri nella clausola OVER() sono gli stessi della soluzione precedente.

Il risultato appare così:

Il risultato è leggermente diverso da quello della soluzione precedente. L'ultima colonna non contiene il valore del terzo stipendio più alto. Contiene invece una classifica e si può facilmente notare che l'effetto è lo stesso. I valori del terzo stipendio più alto per reparto sono 5.371,02 (Contabilità), 5.564,25 (Risorse umane) e 6.657,11 (Ricerca e sviluppo), come nella prima soluzione.

Possiamo anche utilizzare ROW_NUMBER() in un CTE. Se non avete familiarità con le CTE, questo articolo spiega di cosa si tratta. Ad esempio, possiamo scrivere il codice seguente:

WITH salaries_ranks AS (
SELECT e.first_name,
 e.last_name,
 d.department_name,
 salary,
 ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY salary DESC
 ) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
)

SELECT *
FROM salaries_ranks
WHERE salary_rank = 3;

Si inizia la CTE con un WITH, seguito dal nome della CTE, salaries_ranks. Quindi, scriviamo un'istruzione SELECT, che è esattamente la stessa del codice precedente.

Ora che abbiamo scritto una CTE, possiamo usarla come qualsiasi altra tabella. Lo facciamo con un'istruzione separata SELECT che seleziona tutte le colonne dalla CTE salaries_ranksmostrando solo le righe in cui salary_rank = 3.

Il risultato di questo codice è:

Si tratta di una soluzione piuttosto elegante. Otteniamo solo i dati necessari: solo tre righe che mostrano il terzo stipendio più alto per ogni reparto.

Utilizzo di RANK()

La terza opzione consiste nell'utilizzare la funzione RANK(). È simile a ROW_NUMBER(), ma classifica anche le righe all'interno di una partizione. Simile, ma non uguale. Le differenze verranno spiegate più avanti, oppure si possono leggere qui.

Scriviamo il codice usando RANK():

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Anche in questo caso, il codice non è molto diverso dai precedenti, tranne che per la funzione finestra specifica utilizzata. In questo caso, si tratta di RANK(), con tutti gli stessi criteri nella clausola OVER(). Il risultato sarà esattamente lo stesso di quando abbiamo usato ROW_NUMBER(), quindi non credo sia necessario mostrare di nuovo la stessa tabella dei risultati.

Utilizzo di DENSE_RANK()

L'ultima soluzione che vi mostrerò è la funzione della finestra DENSE_RANK(). Proprio come ROW_NUMBER() e RANK(), classifica i valori all'interno di un set di dati.

Anche il codice non è molto diverso. È lo stesso di prima, solo che utilizza una funzione finestra diversa:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		DENSE_RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC 
		) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Quando eseguiamo questo codice, otteniamo esattamente lo stesso risultato delle due soluzioni precedenti.

Panoramica dei concetti e delle loro differenze

Come si è visto, è possibile utilizzare una qualsiasi delle quattro funzioni finestra per ottenere il terzo stipendio più alto per reparto.

La funzione NTH_VALUE() mostra esplicitamente il valore del terzo stipendio più alto per reparto.

Le funzioni ROW_NUMBER(), RANK() e DENSE_RANK() classificano gli stipendi all'interno di ciascun reparto. Quindi, è sufficiente trovare il valore dello stipendio associato al terzo posto. Queste tre funzioni sono simili ma non uguali. In questo esempio sembrano identiche, ma altri dati potrebbero evidenziarne le differenze.

Ecco una spiegazione di come sono diverse per evitare spiacevoli sorprese.

Le differenze emergono quando nei dati sono presenti legami (più righe con lo stesso valore). Siccome sapevo di non avere legami nei miei dati, tutte e tre le funzioni mi avrebbero dato la stessa soluzione.

Ma cosa succede se ci sono dei legami? In questo caso, ROW_NUMBER() assegnerebbe i ranghi in modo sequenziale; cioè, i legami avrebbero numeri di rango diversi, assegnati arbitrariamente. Al contrario, RANK() assegna lo stesso numero di rango agli stessi valori e salta un numero di rango per compensarlo quando arriva a una riga con un valore diverso. Infine, DENSE_RANK() assegna lo stesso numero di rango ai legami, senza saltare un numero di rango al valore successivo. Ecco un esempio per illustrare la situazione:

Se si cerca il valore del terzo stipendio più alto in questi dati, ROW_NUMBER() fornisce la soluzione corretta: lo stipendio di 4.000 è il terzo più alto. Tuttavia, se si cercano tutti i dipendenti con il terzo stipendio più alto, la soluzione non è corretta. Mostrerebbe solo l'impiegato 2, mentre dovrebbe essere mostrato anche l'impiegato 5. In questo caso, RANK() o DENSE_RANK() sarebbero una scelta migliore.

E se si cerca il valore del quarto stipendio più alto? La funzione ROW_NUMBER() fornirebbe una soluzione totalmente errata, poiché il terzo e il quarto valore più alto sono gli stessi. L'uso di RANK() non dà alcun risultato, perché salta il numero di rango quattro: come spiegato in precedenza, assegna lo stesso numero di rango ai legami e salta il numero di rango successivo per compensarlo. In questo caso, solo DENSE_RANK() fornisce una soluzione corretta.

Prima di decidere quale funzione utilizzare, assicuratevi di aver compreso i dati e cosa state cercando di ottenere come soluzione. Il modo più sicuro è usare NTH_VALUE(). Se si vuole usare una funzione di classificazione, in genere è meglio usare DENSE_RANK() quando non si sa se ci sono legami nei dati. Se invece si usa ROW_NUMBER() o RANK(), assicurarsi di sapere come sarà il risultato. Forse una o entrambe queste due funzioni vi daranno ciò di cui avete bisogno. Scegliete la funzione in base alle vostre esigenze.

Queste quattro funzioni possono essere utilizzate anche quando si vuole trovare il salario massimo o minimo per reparto. Oppure, ad esempio, il prodotto più o meno costoso per categoria di prodotto. O qualsiasi prodotto con l'ennesimo prezzo più alto. Si possono cercare le vendite più alte o più basse, i ricavi, le ore lavorate, i costi, il numero di "mi piace", i login, gli impegni, gli stream, i commenti, ecc.

Se utilizzate MySQL, questa introduzione completa vi mostrerà queste quattro e tutte le altre funzioni della finestra di MySQL.

Diversi modi per ottenere il valore N di SQL

La funzione NTH_VALUE() è perfetta per trovare l'ennesimo stipendio o l'ennesimo valore di qualsiasi altra colonna. Dopotutto, è stata progettata proprio per questo scopo. Vi ho mostrato come ottenere lo stesso effetto con altre tre funzioni della finestra: ROW_NUMBER(), RANK(), e DENSE_RANK(). Utilizzate quella più adatta alle vostre esigenze e ai vostri dati.

Ma queste sono solo quattro funzioni di finestra. Sono solo la punta dell'iceberg e c'è molto di più! Queste e altre funzioni finestra sono spiegate in questo corso sulle funzioni finestra.