Come utilizzare il metodo SQL RANK OVER (PARTITION BY)

Classificare i dati in SQL è un gioco da ragazzi se si sa come usare RANK() per classificare su una partizione. Questo articolo mostra come farlo e come RANK() si differenzia da DENSE_RANK() e ROW_NUMBER().

Se lavorate con SQL in ambito professionale, vi sarà capitato almeno una volta di dover classificare dei dati. Pensate alla classifica dei mesi/anni/trimestri in base alle entrate o ai costi, ai prodotti più venduti, ai post più visti o alle canzoni ascoltate in streaming, ai dipendenti in base allo stipendio, alle filiali più redditizie, e così via.

O alla classifica dei libri in base alle loro vendite.

Il fatto è che spesso è necessario classificare i dati all'interno di una determinata categoria, o partizione, come la chiamiamo noi. L'SQL RANK OVER (PARTITION BY) entra in gioco!

Questo è un tipico esempio di funzioni finestra in SQL. Per una spiegazione più dettagliata delle funzioni finestra, il nostro corso sulle funzioni finestra è il migliore. Attraverso 218 esercizi interattivi, imparerete a conoscere meglio PARTITION BY e altre clausole di funzioni finestra, come ORDER BY, ROWS e RANGE. Queste funzioni sono utilizzate non solo nelle classifiche, ma anche nelle funzioni di aggregazione e di analisi.

Torniamo alle vendite di libri. Guardate questa tabella, con il codice per crearla qui. Come classifichiamo le vendite di libri in ciascuna lingua?

La soluzione è semplice se si sa che la lingua, in questo esempio, agisce come una sorta di partizione dei dati.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

Ed eccola qui!

Ora analizziamo questo esempio.

Che cos'è RANK()?

In termini più semplici, RANK() è una funzione finestra.

Le funzioni finestra di SQL sono simili alle funzioni aggregate in quanto vengono applicate a un gruppo di righe. Una grande differenza: le funzioni finestra mantengono i dettagli delle singole righe, a differenza delle funzioni aggregate con GROUP BY.

Cosa fa RANK()?

Come suggerisce il nome, classifica i dati. Questo la rende una funzione finestra di classificazione, insieme a DENSE_RANK() e ROW_NUMBER().

Quando si usa una di queste funzioni finestra, deve essere accompagnata dalla clausola OVER (ORDER BY). La clausola OVER() è obbligatoria per qualsiasi funzione finestra. È quella che trasforma una funzione "normale" in una funzione finestra.

Per queste funzioni di classificazione, ORDER BY tra parentesi definisce l'ordine in cui viene eseguita la classificazione. L'ordine può essere ascendente o discendente. Si tenga presente che non influisce sull'ordine delle righe nel risultato; questo viene fatto con un ORDER BY alla fine della query.

Per saperne di più, consultare il nostro articolo sulla funzione RANK().

Come funziona RANK() con OVER (ORDER BY)

Utilizziamo il set di dati di cui sopra. Prendiamo l'intera tabella e classifichiamo i libri in base alle vendite. Vediamo cosa succede.

SELECT
  title,
  author,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM books;

Questa è la stessa query della precedente, solo che questa non seleziona la lingua e non usa PARTITION BY. Quindi, la funzione RANK() è seguita da OVER(). La clausola ORDER BY indica alla funzione di classificare i dati in base alle vendite in ordine decrescente, cioè dai libri più venduti a quelli meno venduti. Poiché la clausola PARTITION BY è omessa, la funzione classifica l'intera tabella.

Ecco le prime dieci righe dell'output.

L'aspetto critico da notare è che ci sono quattro libri con 50 milioni di copie vendute, tutti classificati al quinto posto.

Questo è il modo in cui RANK() si differenzia dalle altre due funzioni di classificazione a finestra: assegna lo stesso rango a valori uguali. Quando la funzione raggiunge il valore di vendita successivo (in questo caso, 36 milioni di copie vendute), non assegna il valore di rango successivo (6), ma salta per aggiustare il conteggio dei valori di vendita legati. Come già detto, il grado "5" compare quattro volte; pertanto, il grado successivo assegnato è il nove.

DENSE_RANK() Anche classifica i legami con la stessa posizione. Tuttavia, a differenza di RANK(), non salta i valori di rango basati sui legami. Gli stessi dati classificati con DENSE_RANK() sono i seguenti.

Dopo diversi libri classificati al quinto posto, la posizione successiva è la sesta, non la nona come in RANK().

Che dire di ROW_NUMBER()? Non si preoccupa dei legami o dei salti. Si limita a classificare le righe in modo sequenziale. Di seguito sono riportate le prime dieci righe ottenute con ROW_NUMBER().

Le differenze tra queste funzioni sono spiegate nella panoramica delle funzioni di classificazione.

Come funziona RANK() con OVER (PARTITION BY)

La clausola PARTITION BY divide i dati in partizioni o sottoinsiemi. Se utilizzata con RANK(), significa che i dati vengono classificati all'interno della partizione. Quando si raggiunge la seconda partizione, la classifica viene ripristinata a partire da uno.

Per chiarire questo aspetto, esaminiamo di nuovo la query dell'inizio di questo articolo.

SELECT
  original_language,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY original_language ORDER BY sales DESC)
    AS sales_rank
FROM books;

In questa query, RANK() viene utilizzato insieme a PARTITION BY. La partizione, in questo caso, è original_language. La classificazione avviene per vendite in ordine decrescente, come specificato nella clausola ORDER BY.

Il modo in cui abbiamo scritto PARTITION BY e ORDER BY significa che i libri sono classificati in base alle vendite, ma all'interno di ogni categoria linguistica. Una volta che la funzione ha classificato tutti i libri in una lingua, riparte quando raggiunge la seconda lingua e così via.

Lo vediamo nell'output della query.

Abbiamo contrassegnato ogni partizione con un colore diverso per vedere facilmente le diverse partizioni. Il libro più venduto in inglese è Harry Potter e la pietra filosofale di J.K. Rowling. Poi c'è Lo Hobbit. Lolita e Watership Down sono entrambi al terzo posto, poiché RANK() assegna lo stesso grado ai libri con lo stesso valore di vendita. Poi si salta un valore di classifica e Diciannove Ottantaquattro è al quinto posto.

La partizione successiva riguarda la lingua francese e la classifica ricomincia. Il Piccolo Principe è il libro più venduto in francese.

La stessa logica funziona per i libri in italiano, russo e spagnolo.

Esempio bonus

Abbiamo studiato queste query per mostrare come funziona RANK() OVER (PARTITION BY). Ora facciamo pratica!

La seguente non è molto diversa dalla prima query. Non dovreste avere problemi ad applicare ciò che avete imparato.

C'è una colonna nella tabella books denominata classify_under. Essa specifica la categoria in cui ogni libro deve essere inserito nella libreria.

Classifichiamo i libri in base alle vendite per ogni categoria.

SELECT
  classify_under,
  title,
  author,
  sales,
  RANK() OVER (PARTITION BY classify_under ORDER BY sales DESC)
    AS sales_rank
FROM books;

La query seleziona la colonna classify_under invece di original_language nella prima query. Tutte le altre colonne selezionate sono le stesse.

C'è anche una differenza in RANK(). Dal momento che stiamo classificando per classify_under, questa è la colonna che deve essere presente nella clausola PARTITION BY.

Ancora una volta, vogliamo classificare i libri in base alle vendite in ordine decrescente. Ecco la classifica:

Per pura coincidenza, ci sono di nuovo cinque partizioni. Nella categoria "Classici", Don Chisciotte è il libro più venduto. Poi ci sono tre libri al secondo posto. La sequenza di classifica viene saltata fino ad arrivare a Guerra e pace al quinto posto. Il resto dei classici viene classificato in sequenza, poiché non ci sono più pareggi.

In altre categorie, non ci sono pareggi: "Poesia" e "Fantascienza" hanno un solo libro per categoria. Quindi, c'è solo la prima classifica.

Per ulteriori esempi con altre funzioni della finestra, consultate il nostro articolo che spiega come utilizzare PARTITION BY.

Aggiungere RANK() al vocabolario SQL

Abbiamo visto gli usi più tipici della funzione finestra RANK(). Sebbene richieda una OVER (ORDER BY), la clausola PARTITION BY ne sblocca le possibilità. Essa rende RANK() uno strumento sofisticato per classificare i dati in una o più partizioni con facilità nel lavoro quotidiano.

Per saperne di più e fare pratica con RANK() e con altre funzioni finestra (di classificazione), utilizzate il nostro corso Window Functions corso. Avrete una spiegazione ancora più dettagliata del ranking e la possibilità di scrivere molto codice nei nostri esercizi.

Buona classificazione!