Cos'è la funzione RANK() in SQL e come si usa?

Chi sono i 10 clienti più importanti? Chi sono i dipendenti con le migliori prestazioni in ogni reparto? Per rispondere a domande come queste, è necessario classificare il risultato della query SQL. Vediamo come la funzione RANK() aiuta a classificare i risultati in SQL.

Sono molti i casi in cui è necessario classificare le righe in SQL. Si potrebbe voler classificare i clienti in base al volume delle vendite, gli studenti in base ai risultati degli esami, le attività aziendali in base al loro valore attuale, i dipartimenti in base al numero di dipendenti o gli utenti in base alla loro data di registrazione.

La classificazione è uno strumento importante e comune nell'analisi dei dati. In questo tutorial vi spiegherò in dettaglio come classificare le righe in SQL.

Introduzione alla funzione RANK()

La funzione RANK() è una delle funzioni finestra di SQL. Le funzioni finestra esaminano una parte dei dati e calcolano i risultati per questa parte.

La funzione RANK(), in particolare, assegna un rango a ogni riga in base a una colonna fornita. RANK() viene inclusa nell'istruzione SELECT con la seguente sintassi:

RANK() OVER (ORDER BY column ASC|DESC)

In pratica, si aggiunge un'altra colonna al set di risultati. Questa colonna include il rango di ciascun record in base all'ordine specificato dopo la parola chiave ORDER BY. Ciò significa specificare (1) la colonna da utilizzare per l'ordinamento delle righe e (2) se l'ordine deve essere crescente o decrescente.

La prima riga ottiene il rango 1, mentre le righe successive ottengono ranghi più alti. Se alcune righe hanno lo stesso valore nella colonna utilizzata per l'ordinamento, vengono classificate allo stesso modo. La funzione RANK() lascia degli spazi vuoti in questi casi.

Ad esempio, se la prima e la seconda riga hanno lo stesso valore nella colonna fornita, entrambe ottengono il rango 1. Quindi, la terza riga ottiene il rango 2. Quindi, la terza riga otterrà il rango 3 (cioè, il rango 2 sarà mancante).

Per prendere confidenza con la funzione RANK(), date un'occhiata al nostro corso interattivo Window Functions corso interattivo. Il corso tratta anche altre funzioni di classificazione che hanno approcci diversi per elaborare gli stessi valori nella colonna fornita.

Vediamo ora la funzione RANK() in azione.

Classificazione di base

Per vedere come funziona in pratica la funzione RANK(), facciamo un esempio. Immaginiamo di avere un team di sviluppatori. Vogliamo classificarli in base alla loro esperienza. Ecco la tabella di partenza:

Ora, il nostro compito è mostrare il nome, il cognome, il livello di anzianità, gli anni di esperienza e il grado degli sviluppatori. Vogliamo che lo sviluppatore con maggiore esperienza ottenga il rango 1.

Ecco la nostra query SQL e l'insieme dei risultati:

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC)
FROM developers;

Per classificare gli sviluppatori in base alla loro esperienza, abbiamo incluso il seguente campo nell'istruzione SELECT:

RANK() OVER (ORDER BY years_experience DESC)

Definiamo l'ordine dopo la clausola ORDER BY specificando la colonna da utilizzare per l'ordinamento (years_experience) e l'ordine (decrescente). Quindi, classifichiamo gli sviluppatori in base a questo ordine.

Vediamo che lo sviluppatore con 8 anni di esperienza ottiene il primo posto, quello con 5 anni di esperienza il secondo e i due sviluppatori con 4 anni di esperienza il terzo. Si noti che la funzione RANK() lascia uno spazio vuoto in questo punto e gli sviluppatori successivi ottengono il rango 5 (cioè, il rango 4 è mancante).

Ora avete un'idea di come funziona la funzione RANK(), compreso il modo in cui elabora gli stessi valori nella colonna utilizzata per l'ordinamento. Potete trovare altri casi d'uso in questa guida completa alla classificazione delle righe in SQL.

Passiamo ora a esempi più avanzati.

Classificazione all'interno di gruppi diversi

Nel primo esempio abbiamo classificato l'intero insieme di risultati. Ora, diciamo che vogliamo avere una classifica separata per ogni livello di anzianità (cioè, junior, medio e senior).

Con la funzione RANK() di SQL, possiamo farlo aggiungendo la parola chiave PARTITION BY e specificando la colonna da utilizzare per raggruppare i record. Ecco la nostra query SQL:

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (PARTITION BY level ORDER BY years_experience    DESC)
FROM developers;

Nella clausola OVER() definiamo prima come raggruppare i record (usando la colonna level ) e poi come ordinare i record all'interno di questi gruppi (in base agli anni di esperienza, partendo dallo sviluppatore più esperto).

Ecco la tabella risultante:

Si noti che abbiamo classifiche separate per i livelli junior, medio e senior. Tuttavia, lo sviluppatore più esperto di ogni gruppo ottiene il primo posto, mentre gli sviluppatori con meno esperienza ottengono posizioni più alte.

Inoltre, si noti che mentre Kate Williams e Max Weber hanno gli stessi 4 anni di esperienza, Max ottiene il primo posto come sviluppatore di medio livello più esperto, mentre Kate ottiene il terzo posto come sviluppatore senior meno esperto.

Ora che sapete come funziona PARTITION BY, è il momento di conoscere le altre funzioni di classificazione.

Altre funzioni di classificazione in SQL

Per classificare le righe in SQL, si possono usare anche le funzioni DENSE_RANK() e ROW_NUMBER(). La differenza sta nel modo in cui queste funzioni gestiscono gli stessi valori.

Come abbiamo appreso in precedenza, la funzione RANK() assegna lo stesso grado ai record con lo stesso valore e lascia uno spazio per tenere conto di questi record. Al contrario:

• la funzione DENSE_RANK() non lascia uno spazio vuoto;
• la funzione ROW_NUMBER() classifica tutti i record in modo diverso, anche se i valori sono gli stessi.

È più facile capire questa differenza con un esempio. Torniamo quindi al primo caso d'uso in cui abbiamo classificato l'intero set di risultati. Questa volta, però, utilizzeremo anche DENSE_RANK() e ROW_NUMBER():

SELECT first_name, last_name, level, years_experience,
       RANK() OVER (ORDER BY years_experience DESC),
       DENSE_RANK() OVER (ORDER BY years_experience DESC),
       ROW_NUMBER() OVER (ORDER BY years_experience DESC)
FROM developers;

Analogamente alla funzione RANK(), la funzione DENSE_RANK() assegna la stessa posizione agli sviluppatori con gli stessi anni di esperienza (ad esempio, posizione 3 a Kate Williams e Max Weber). Tuttavia, il grado successivo nella colonna dense_rank è 4, senza lasciare spazi vuoti nella classifica.

D'altra parte, la funzione ROW_NUMBER() assegna un numero diverso a tutti gli sviluppatori, compresi quelli con gli stessi anni di esperienza. Allora, perché Max ottiene il grado 3 e Kate il grado 4? Si tratta di una scelta arbitraria e non si può mai sapere a quale dei record con lo stesso valore verrà assegnato un rango più alto.

Ora che si conosce la differenza tra le funzioni di classificazione, si può decidere quale sia più adatta ai propri casi d'uso. Se non si è sicuri, si possono vedere altri esempi delle funzioni RANK(), DENSE_RANK() e ROW_NUMBER() in questo articolo riassuntivo.

È ora di fare pratica con le funzioni di classificazione!

Le funzioni di classificazione sono tra gli strumenti più comuni utilizzati dagli analisti di dati. Quindi, dovreste sentirvi sicuri con le funzioni di classificazione in SQL. E la confidenza arriva con la pratica.

Date un'occhiata a questo Window Functions che comprende 218 esercizi interattivi sulle funzioni di ranking, sulle funzioni di analisi, sul calcolo statistico avanzato con diverse finestre, ecc. Per ulteriori informazioni sul corso, consultare questo articolo.

Ed ecco un bonus: questo Cheat Sheet di 2 pagine di SQL Window Functions vi aiuterà a fare pratica con le funzioni di classificazione e altre funzioni a finestra.

Grazie per aver letto e buon apprendimento!