Come usare ROW_NUMBER OVER() in SQL per classificare i dati

A volte è necessario conoscere la posizione delle righe in un insieme di risultati. Scoprite come utilizzare ROW_NUMBER e OVER in SQL!

Avete mai avuto bisogno di aggiungere un numero sequenziale ai record restituiti da una query SQL? O forse avete bisogno di creare un report "top n" basato su una classifica specifica. In tutti questi casi, è necessario calcolare la posizione della riga nella classifica. Per farlo, è necessaria la funzione ROW_NUMBER(). La funzione assegna un numero intero sequenziale a qualsiasi riga dell'insieme di risultati.

In questo articolo vedremo come utilizzare la funzione ROW_NUMBER() in SQL.

Cos'è la funzione ROW_NUMBER()?

ROW_NUMBER è una funzione di SQL. Viene utilizzata per aggiungere numeri sequenziali alle righe di un insieme di risultati. Come qualsiasi altra funzione finestra, deve essere utilizzata con la clausola OVER(). Ecco la sintassi:

SELECT
  ROW_NUMBER() OVER (...) as athlete_num
  …
FROM athletes;

La clausola OVER() ha due sottoclausole opzionali: PARTITION BY e ORDER BY. Mostreremo degli esempi utilizzando diverse clausole OVER.

Prima di iniziare, facciamo due parole sulle funzioni di finestra in generale. Le funzioni finestra sono una parte molto potente di SQL, ma non sono molto conosciute dall'utente medio di SQL. Per questo motivo vi consiglio il nostro corso interattivo su Window Functions. In questo corso, passo dopo passo, vi guideremo attraverso le funzioni finestra con oltre 200 esercizi pratici. Alla fine del corso, vi sentirete a vostro agio nell'uso delle funzioni finestra sui database SQL.

Utilizzo di ROW_NUMBER() con OVER(): Un esempio introduttivo

Mostriamo una semplice query SQL che utilizza la funzione finestra ROW_NUMBER. Non c'è niente di meglio dello sport per illustrare le classifiche, quindi supponiamo di lavorare per un'azienda che organizza competizioni sportive in molti paesi.

Per prima cosa, vogliamo assegnare un numero sequenziale a ogni atleta; questo numero sarà utilizzato come ID dell'atleta nella nostra azienda. Per evitare conflitti, non vogliamo che ci siano criteri per determinare l'ordine della numerazione sequenziale. Vogliamo che i numeri sequenziali siano assegnati a ciascun atleta in modo casuale, non in ordine alfabetico per nome, paese o sport.

Abbiamo una tabella chiamata athlete con le colonne firstname, lastname, sport e country. La query per generare un report che includa un numero sequenziale per ogni atleta è:

SELECT
  ROW_NUMBER() OVER () as athlete_id,
  firstname
  lastname,
  sport, 
  country
FROM athletes;

L'espressione ROW_NUMBER() OVER () assegna un valore intero sequenziale che inizia con 1 a ogni riga del risultato della query. L'ordine dei numeri assegnati alle righe nel risultato non è deterministico se si utilizza la semplice clausola OVER(). (Si noti che non ci sono clausole aggiuntive come ORDER BY o PARTITION BY in OVER()) Il primo record può essere un qualsiasi record della tabella; per questo record, ROW_NUMBER restituirà 1. Poi lo stesso per il secondo record, che è un numero che inizia con 1. Lo stesso vale per il secondo record, che sarà il numero 2, e così via. Di seguito è riportato un risultato parziale della query:

Prima di concludere questa sezione, vorrei suggerire l'articolo Che cos'è la clausola OVER in SQL, dove si possono trovare diversi esempi di funzioni finestra che utilizzano diverse combinazioni della clausola OVER.

Creazione di classifiche con ROW_NUMBER() e ORDER BY

Supponiamo che l'azienda debba creare un'etichetta con il numero di partecipanti per tutti gli atleti che partecipano a una maratona. Gli atleti devono essere ordinati per cognome e l'azienda vuole assegnare un numero sequenziale a ogni atleta; gli atleti indosseranno questi numeri come etichette sulle loro magliette durante la maratona. Le etichette devono iniziare da 1001. La query è:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon';

Questa query è simile all'esempio precedente. Una differenza è la clausola WHERE, che restituisce solo gli atleti che partecipano alla maratona. L'altra differenza (che è la principale) è la clausola OVER(ORDER BY lastname). Questa indica a ROW_NUMBER() che il numero sequenziale deve essere assegnato nell'ordine di lastname- ad esempio, 1 al primo lastname, 2 al secondo e così via.

Nel set di risultati precedente, i partecipanti sono stati ordinati per lastname. Tuttavia, se due partecipanti hanno lo stesso cognome (ad esempio, Smith), l'ordine di queste due righe non è deterministico; le righe possono essere in qualsiasi ordine. Se si desidera ordinare sia per lastname che per firstname, è necessario utilizzare l'espressione:

ROW_NUMBER() OVER (ORDER BY lastname, firstname)

Utilizzo di ORDER BY due volte in una query

Nella query precedente, utilizziamo la clausola ORDER BY nella funzione ROW_NUMBER(). Tuttavia, il risultato della query non segue alcun ordine, ovvero le righe sono ordinate in modo casuale. Se si vuole, si può aggiungere una seconda clausola ORDER BY alla fine della query per definire l'ordine di visualizzazione dei record.

Modifichiamo la query precedente aggiungendo una singola modifica: Inseriamo un ORDER BY country:

SELECT
  ROW_NUMBER() OVER (ORDER BY lastname ASC) + 1000 as participant_label,
  firstname,
  lastname, 
  country
FROM athletes
WHERE sport = 'Marathon'
ORDER BY country;

Le righe del risultato sottostante sono le stesse righe della query precedente, ma sono mostrate in ordine diverso. Ora sono ordinate in base al Paese dell'atleta. Tuttavia, se due o più atleti provengono dallo stesso paese, vengono mostrati in qualsiasi ordine. Lo si può vedere qui sotto nei due atleti del Canada:

In questa query, abbiamo usato la clausola ORDER BY due volte. La prima volta è stata utilizzata nella funzione ROW_NUMBER per assegnare il numero sequenziale che segue l'ordine del cognome. La seconda volta è stata utilizzata per definire l'ordine di visualizzazione delle righe dei risultati, basato sul nome del paese.

Uso di ROW_NUMBER() con PARTITION BY e ORDER BY

Nel prossimo esempio di query, useremo ROW_NUMBER() combinato con le clausole PARTITION BY e ORDER BY. Mostreremo una query per assegnare i numeri di stanza agli atleti. Supponiamo che l'azienda voglia ospitare gli atleti dello stesso Paese in camere d'albergo contigue. L'idea è di creare un'etichetta con il Paese e un numero sequenziale per ogni atleta e di apporre questa etichetta sulla porta di ogni camera d'albergo. Ad esempio, se il Paese è il Canada e ha 3 atleti, vogliamo che le etichette delle camere siano "Canada_1", "Canada_2" e "Canada_3".

La query per generare le etichette delle camere con il nome dell'atleta assegnato a questa camera è:

SELECT
  country || '_' || 
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY lastname ASC) 
                                                            as room_label,
  firstname,
  lastname, 
  country
FROM athletes;

Il nuovo elemento introdotto nella query è OVER(PARTITION BY country). Raggruppa le righe dello stesso country e genera una serie di numeri sequenziali diversi (a partire da 1) per ogni Paese.

Nel risultato della query seguente, si può notare che le righe raggruppate dalla clausola PARTITION BY hanno lo stesso colore. Un gruppo di righe riguarda il Canada (azzurro), un altro la Francia (viola) e così via.

All'interno di ciascun gruppo di righe, la clausola ORDER BY lastname viene utilizzata per assegnare numeri sequenziali agli atleti in base al cognome. Per "Irlanda", abbiamo tre righe; la prima è per "Barry", la seconda per "Fox" e così via.

Si consiglia di leggere l'articolo Come usare SQL PARTITION BY con OVER, dove si possono trovare altri esempi delle clausole OVER e PARTITION BY.

Altre classificazioni Window Functions: RANK e DENSE_RANK

Oltre a ROW_NUMBER, SQL fornisce altre due funzioni a finestra per calcolare le classifiche: RANK e DENSE_RANK. La funzione RANK funziona in modo diverso da ROW_NUMBER quando ci sono legami tra le righe. In caso di parità, RANK assegna lo stesso valore a entrambe le righe e salta il grado successivo (ad esempio, 1, 2, 2, 2, 5 - i gradi 3 e 4 vengono omessi). La funzione DENSE_RANK non salta i ranghi successivi.

Esaminiamo un semplice esempio per vedere le differenze tra queste tre funzioni:

SELECT 
  lastname AS athlete_name, 
  time, 
  ROW_NUMBER() OVER (ORDER BY time) AS position_using_row_number, 
  RANK OVER() (ORDER BY time) AS position_using_rank,
  DENSE_RANK() OVER (ORDER BY time) AS position_using_dense_rank
 FROM competition_results
 WHERE sport = ‘Marathon men’; 

I risultati sono:

Se siete interessati alle funzioni a finestra RANK e DENSE_RANK, vi suggerisco questi articoli per maggiori dettagli ed esempi:

• Panoramica delle funzioni di classificazione in SQL
• Come utilizzare RANK Funzioni
• Cos'è e come si usa la funzione RANK in SQL

Uso di ROW_NUMBER() nella clausola WHERE

In SQL non è possibile utilizzare le funzioni finestra nella clausola WHERE. Tuttavia, in alcuni scenari potrebbe essere necessario. In un report Top 10, ad esempio, sarebbe molto utile poter utilizzare una condizione del tipo WHERE ROW_NUMBER OVER() <= 10.

Sebbene non sia possibile utilizzare ROW_NUMBER() direttamente in WHERE, è possibile farlo indirettamente attraverso un'espressione di tabella comune, o CTE. Ad esempio, supponiamo di voler ottenere le prime 3 posizioni nella maratona e nei 100 metri. Per prima cosa, si scrive la CTE, che inizia con WITH:

-- CTE starts
WITH positions AS (
  SELECT 
    lastname AS athlete_name,
    sport,
    country,
    time, 
    ROW_NUMBER OVER (PARTITION BY sport ORDER BY time) AS position
  FROM competition_results
  WHERE sport IN (‘Marathon men’, ‘Marathon women’)
)
	--CTE ends

	--main query starts
SELECT 
  sport, 
  athlete_name, 
  time, 
  country, 
  position
FROM positions
WHERE position <= 3
ORDER BY sport, position;

Nella query precedente, abbiamo creato una CTE chiamata positions. Essa ha una colonna chiamata posizione che viene popolata con il risultato della funzione ROW_NUMBER().

Nella query principale (cioè la seconda istruzione SELECT ), possiamo usare la colonna position nella clausola WHERE per filtrare gli atleti che terminano la gara nelle prime tre posizioni.

Nota: in caso di parità tra due concorrenti, la funzione RANK() potrebbe essere più appropriata della funzione ROW_NUMBER() in questo report.

I risultati della query sono mostrati di seguito:

Se volete esercitarvi con le funzioni finestra di SQL, vi consiglio il nostro set interattivo di esercizi Window Functions . Il set contiene 100 esercizi pratici sulle funzioni finestra, tra cui la creazione di classifiche utilizzando diverse funzioni finestra di classificazione.

La pseudocolonna ROWNUM di Oracle

Oracle SQL consente di inserire una pseudocolonna chiamata ROWNUM in qualsiasi query. Una pseudocolonna si comporta come una colonna della tabella, ma non è effettivamente memorizzata nella tabella. È possibile selezionare da una pseudocolonna come se fosse una colonna della tabella.

La pseudocolonna ROWNUM restituisce la posizione della riga nell'insieme dei risultati. Inizia con 1 per la prima riga e tutti i record successivi vengono incrementati di 1.

Tuttavia, Oracle ROWNUM non ha la stessa potenza della funzione ROW_NUMBER. Ad esempio, non è possibile utilizzare la sottoclausola PARTITION BY per creare diverse sequenze, come abbiamo fatto nella query sulle camere d'albergo. Un'altra limitazione è che non si può usare la clausola ORDER BY per specificare un ordine della sequenza diverso da quello dell'insieme dei risultati. Il motivo di queste limitazioni è semplice: ROWNUM non è una funzione finestra, ma una semplice pseudocolonna.

Pronti a fare pratica con ROW_NUMBER() e OVER() in SQL?

Abbiamo analizzato diversi modi per aggiungere una sequenza numerica al risultato di una query utilizzando la funzione ROW_NUMBER. Abbiamo inoltre mostrato diversi modi per utilizzare la clausola OVER(). Abbiamo anche introdotto altre due funzioni di classificazione SQL: RANK e DENSE_RANK.

Le funzioni Windows sono una risorsa potente in SQL. Se volete approfondire, vi suggerisco di seguire il nostro corso interattivo online. Window Functions interattivo online. Si tratta di un'esercitazione passo-passo che illustra le funzioni SQL Windows con esempi ed esercizi. Vi consiglio anche il nostro foglio informativo gratuito sulle funzioni SQL Windows, che è il mio foglio preferito. L'ho appeso alla parete del mio ufficio come aiuto rapido per la sintassi delle funzioni di Windows.