Come numerare le righe in un insieme di risultati SQL

Vi è mai capitato di dover aggiungere un numero sequenziale ai record del risultato di una query SQL? Non è così semplice come sembra! Scoprite come farlo correttamente in questo articolo.

Per numerare le righe di un set di risultati, è necessario utilizzare una funzione della finestra SQL chiamata ROW_NUMBER(). Questa funzione assegna un numero intero sequenziale a ogni riga di risultati. Tuttavia, può essere utilizzata anche per numerare i record in modi diversi, ad esempio per sottoinsiemi. Si può anche usare per numerare i record per altri scopi interessanti, come vedremo.

Una richiesta frequente: Potresti numerare i record?

Supponiamo di lavorare per un'azienda di vendita di automobili e di voler produrre il seguente report. Si noti che la prima colonna (row_num) non è una colonna della tabella; la generiamo usando ROW_NUMBER() nella query.

Tabella dei risultati

La query per ottenere il report è la seguente:

SELECT ROW_NUMBER() OVER () AS row_num,
       article_code,
       article_name,
       branch,
       units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Nella query precedente, la sintassi della funzione ROW_NUMBER() è molto semplice: utilizziamo una clausola OVER vuota. Ciò significa che vogliamo numerare tutti i record del set di risultati utilizzando un'unica sequenza di numeri, assegnando i numeri ai record senza alcun ordine. Questo è il modo più semplice di utilizzare la funzione ROW_NUMBER():

ROW_NUMBER() OVER () AS row_num

Tuttavia, esistono altri modi per utilizzare ROW_NUMBER(). Si può aggiungere una clausola PARTITION BY e/o una ORDER BY alla funzione OVER, come vedremo nella prossima sezione. La clausola PARTITION BY permette di numerare diversi gruppi di record in modo indipendente, mentre la clausola ORDER BY permette di numerare i record in un ordine specifico. Nella prossima sezione vedremo alcuni esempi.

Prima di continuare con la clausola ROW_NUMBER(), è opportuno spendere qualche parola sulle funzioni SQL window. Come abbiamo detto in precedenza, ROW_NUMBER() è una funzione finestra. Esistono molte altre funzioni finestra che si possono usare nelle query, come AVG(), MAX(), LEAD(), LAG() e FIRST_VALUE(). Se volete approfondire, vi consiglio il corso Window Functions di LearnSQL. Si tratta di un tutorial passo-passo che illustra le funzioni finestra di SQL con esempi ed esercizi.

Approfondimento: Le clausole Partition By e Order By

Nella sezione precedente abbiamo illustrato il modo più semplice di utilizzare la funzione finestra ROW_NUMBER(), cioè numerando tutti i record del set di risultati senza un ordine particolare. Nei prossimi paragrafi vedremo tre esempi con alcune clausole aggiuntive, come PARTITION BY e ORDER BY.

Nel primo esempio, numereremo i record utilizzando una sequenza diversa per ogni filiale dell'azienda, che sarà ordinata in base alle unità vendute in quella filiale. Nella query successiva, la clausola PARTITION BY raggruppa i record che hanno lo stesso valore in ramo, assegnando una sequenza ROW_NUMBER diversa a ciascun gruppo/ramo. (La clausola ORDER BY units_sold definisce l'ordine di elaborazione delle righe all'interno della partizione. In questo caso, le righe appartenenti a ciascuna partizione saranno ordinate per unità vendute in ordine decrescente.

SELECT 
   ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC) AS row_num,
   article_code,
   article_name,
   branch,
   units_sold
FROM  Sales
WHERE article_code IN ( 101, 102, 103 )

Si noti che in questa query le sequenze sono assegnate per ramo - nell'immagine sottostante, ogni gruppo di record ha un colore diverso - e ordinate per units_sold. La clausola utilizzata è:

ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC)

Questa clausola ci permette di numerare i gruppi di record che hanno lo stesso valore nella colonna ramo. Nel nostro esempio, ci sono due gruppi di record: New York (rosso) e San Francisco (blu). Ora ciascun gruppo di record sarà numerato (clausola ORDER BY ) in base alla colonna units_sold. I valori sono mostrati in ordine decrescente.

Tabella dei risultati

Le clausole OVER, PARTITION BY e ORDER BY sono molto comuni nelle funzioni finestra; se volete approfondire, vi consiglio di leggere l'articolo Esempi di funzioni finestra, dove troverete diversi esempi di funzioni finestra spiegati in dettaglio.

Uso di ROW_NUMBER per rimuovere i duplicati

Un altro caso d'uso interessante della funzione ROW_NUMBER() è quando in una tabella ci sono record completamente duplicati. I record duplicati completi si verificano quando la tabella ha più di un record con gli stessi valori in tutte le colonne (di solito a causa di un errore precedente). Mostreremo del codice SQL per risolvere questa situazione; inoltre, questo codice può essere adattato a qualsiasi caso di record completamente duplicati.

Prima di tutto, inseriamo alcuni record completamente duplicati nella tabella Sales tabella. Supponiamo di non avere una chiave primaria nella tabella Sales e che uno sviluppatore SQL esegua per errore la seguente istruzione INSERT:

INSERT INTO sales 
SELECT * FROM sales WHERE branch = 'San Francisco';

Dopo l'esecuzione di INSERT, la tabella Sales ha questo aspetto. Le ultime due righe sono duplicati completi:

Tabella: Sales

Per rimuovere i record duplicati, aggiungeremo una nuova colonna chiamata row_num e la popoleremo con la seguente INSERT che usa la funzione ROW_NUMBER(). Si noti che PARTITION BY tutte le colonne della tabella. Ecco il codice SQL:

ALTER TABLE sales ADD COLUMN row_num INTEGER;
INSERT INTO sales 
SELECT 
 article_code, article_name, branch, units_sold, period, 
 ROW_NUMBER() OVER (PARTITION BY article_code,article_name,branch, units_sold,period) 
FROM sales ;

Quindi, dopo aver aggiunto la nuova colonna e averla popolata con ROW_NUMBER(), la nostra tabella si presenta così:

Tabella: Sales

È facile capire che dobbiamo rimuovere tutti i record con un NULL o un 2 nella colonna row_num. Lo facciamo con il comando DELETE. Successivamente, dobbiamo rimuovere la colonna row_num. Ecco il codice:

DELETE FROM sales WHERE row_rank IS NULL OR row_rank = 2;
ALTER TABLE sales DROP COLUMN row_rank;

Dopo l'esecuzione delle istruzioni DELETE e ALTER, la tabella è corretta, senza record duplicati. Sales è fissa, senza record duplicati.

Questo approccio presenta uno svantaggio che va chiarito. A un certo punto del processo, il numero di record nella tabella Sales tabella verrà raddoppiato. Ciò può rendere questo metodo inefficiente, soprattutto con le tabelle di grandi dimensioni. Pertanto, si consiglia di utilizzarlo solo per tabelle di piccole e medie dimensioni.

Creazione di un rapporto di classificazione con ROW_NUMBER

In questa sezione, utilizzeremo la funzione ROW_NUMBER() per creare una classifica. Vedremo che ci sono funzioni migliori per le classifiche, come RANK e DENSE_RANK; tuttavia, possiamo creare un rapporto di classifica abbastanza buono usando ROW_NUMBER().

Supponiamo che una volta all'anno la nostra azienda di vendita di automobili dia tre bonus ai suoi venditori: un bonus è per la persona che ha venduto il maggior numero di unità, un altro bonus è per la persona che ha realizzato il maggior numero di entrate e il terzo bonus è per la persona che ha realizzato il maggior profitto. Se una categoria di bonus viene vinta da due venditori, entrambi ricevono il 50% del bonus. Utilizzeremo la tabella Sellers_2019 per ottenere le classifiche e definire il vincitore di ciascun bonus.

Tabella: Sellers_2019

La seguente query restituirà le classifiche necessarie per definire i vincitori dei bonus. Queste classifiche saranno sulle colonne units_ranking, revenue_ranking e profit_ranking.

SELECT  seller_name,
	  ROW_NUMBER() OVER (ORDER BY units_sold desc) units_ranking,
	  ROW_NUMBER() OVER (ORDER BY revenue desc) revenue_ranking,
	  ROW_NUMBER() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

L'immagine successiva mostra i risultati della query precedente con la funzione ROW_NUMBER(). Inizialmente, i valori delle classifiche sembrano corretti. Ma se li esaminiamo in dettaglio, possiamo trovare un errore con Susan Graue in Units_ranking. Susan (e John Doyle) ha vinto il bonus delle unità vendute con 123 unità. Tuttavia, la funzione ROW_NUMBER assegna la posizione 1 a John e la posizione 2 a Susan, il che non è corretto.

Tabella dei risultati

In questo caso, quindi, la funzione ROW_NUMBER() non è la scelta migliore per il calcolo della classifica. Fortunatamente, SQL fornisce due funzioni specifiche per la classificazione: RANK() e DENSE_RANK(). La prossima query utilizza la funzione RANK() invece di ROW_NUMBER():

SELECT  seller_name,
	  RANK() OVER (ORDER BY units_sold desc) units_ranking,
	  RANK() OVER (ORDER BY revenue desc) revenue_ranking,
	  RANK() OVER (ORDER BY profit desc) profit_ranking
FROM sellers;

Nell'immagine successiva, possiamo vedere i risultati della query RANK(). Si può verificare che il problema del rango di Susan è stato risolto.

Tabella dei risultati

Se siete interessati alle funzioni RANK() e DENSE_RANK(), vi suggerisco di leggere l'articolo Come usare le funzioni RANK. Vi saranno forniti diversi esempi e query.