Panoramica delle funzioni di classificazione in SQL

Le funzioni di classificazione SQL facilitano il lavoro con i database relazionali, soprattutto per gli analisti di dati, gli esperti di marketing e gli specialisti finanziari. Queste funzioni vengono utilizzate per assegnare un numero di classifica a ciascun record e consentono di creare in modo efficiente rapporti utili.

Le funzioni di classificazione SQL sono funzioni finestra. Le funzioni finestra calcolano il risultato sulla base di un insieme di righe. La parola "finestra" si riferisce a questo insieme di righe. Vediamo la sintassi delle funzioni di classificazione:

rank_function OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY order_expression [ASC | DESC] ...
)

La sintassi inizia con il nome della funzione di classificazione, come RANK(), DENSE_RANK(), ROW_NUMBER(), o PERCENT_RANK(), e la clausola OVER(). Nella clausola OVER() si specificano le clausole PARTITION BY e ORDER BY. Per le funzioni di classificazione, la clausola ORDER BY, che include i nomi delle colonne o un'espressione, è obbligatoria.

Prima della clausola ORDER BY c'è la clausola opzionale PARTITION BY, che include i nomi delle colonne o un'espressione. La clausola PARTITION BY divide l'insieme di righe in gruppi di righe per classifiche separate.

Non preoccupatevi se questa sintassi sembra complicata. La spiegherò passo per passo nelle sezioni seguenti.

Gli esempi delle sezioni seguenti utilizzeranno la tabella saleche memorizza i dati nelle colonne salesman_id, sale_date e sale_amount. Si veda la vendita della tabella qui sotto:

NUMERO_ROW()

La prima funzione di classificazione di cui parlerò è ROW_NUMBER(). Essa restituisce il numero sequenziale di ogni record nell'insieme dei risultati o all'interno della partizione dell'insieme dei risultati, a partire da 1. Utilizzando ROW_NUMBER(), è possibile selezionare tutti i record e numerarli. Si veda l'esempio 1 qui sotto.

Esempio 1

SELECT 
ROW_NUMBER() OVER(ORDER BY sale_amount) 
  AS row_number, 
sale_date, 
salesman_id,
sale_amount 
FROM sale;

Questa query restituisce il risultato:

In questo caso, la clausola OVER contiene solo la clausola ORDER BY con la colonna sale_amount (questa clausola ordina le righe in base all'importo della vendita ascendente da 9.500 a 31.000 dollari). La query restituisce il numero sequenziale a partire da 1 nella colonna row_number.

Si noti che le righe che hanno lo stesso valore nella colonna sale_amount ricevono numeri diversi. Quindi, se si desidera classificare tutti i record con un numero unico, utilizzare ROW_NUMBER().

E se si desidera numerare gruppi separati di righe? È possibile utilizzare la clausola opzionale PARTITION BY prima della clausola ORDER BY. Si veda l'esempio 2 qui sotto.

Esempio 2

SELECT
ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sale_amount) 
  AS row_number, 
sale_date, salesman_id, sale_amount 
FROM sale;

I record vengono suddivisi in gruppi (chiamati "partizioni") in base alla data di vendita. All'interno di ogni partizione, i record sono numerati separatamente.

Questa query restituisce il risultato:

Per la data di vendita 2020-04-20, le righe hanno i numeri 1 e 2, anche se i valori dell'importo di vendita sono gli stessi. Nella partizione successiva, la riga con l'importo di vendita più basso ha il numero 1 e quella con l'importo di vendita più alto ha il numero 2. I record all'interno di ogni partizione vengono ordinati in base alla colonna in ORDER BY.

RANK()

La seconda funzione di ordinamento è RANK(). Questa funzione aggiunge un numero di rango, che è un numero sequenziale, a ogni riga dell'insieme di risultati o della partizione dell'insieme di risultati.

La differenza tra RANK() e ROW_NUMBER() è che RANK() salta i valori duplicati. In presenza di valori duplicati, viene assegnata la stessa classifica e per ogni classifica duplicata viene visualizzato uno spazio nella sequenza.

Si veda l'esempio 1 qui sotto.

Esempio 1

SELECT
RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Questa query restituisce il risultato:

In questo caso, RANK() assegna un numero di classifica per ogni record come ROW_NUMBER(), ma per lo stesso valore in sale_amount, il numero di classifica è lo stesso. I venditori 11 e 13 il 2020-04-22 hanno realizzato lo stesso importo di vendita di 11.000 dollari. Pertanto, hanno lo stesso numero di rango, 2. In questo caso, ROW_NUMBER() ha assegnato un numero di rango diverso.

Si noti che il record successivo non ha il numero 3. RANK() salta i numeri di rango delle righe aggiuntive con lo stesso valore. Quindi, dopo due righe con il numero di rango 2, il numero di rango successivo è 4, non 3.

Naturalmente, RANK() assegna i numeri anche all'interno delle partizioni. Si veda l'esempio 2 qui sotto.

Esempio 2

SELECT
RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Questa query restituisce il risultato:

La query precedente ha suddiviso il set di risultati in insiemi di righe con la stessa data di vendita. Per esempio, una partizione contiene le vendite del 2020-04-22. Le righe sono numerate separatamente per ogni data di vendita.

Il 2020-04-22, i venditori 11 e 13 hanno lo stesso importo di vendita di 11.000 dollari. Pertanto, questi record hanno entrambi il numero di rango 1, mentre il record successivo ha il numero di rango 3 perché la riga aggiuntiva viene saltata.

DENSE_RANK()

La terza funzione di classificazione è DENSE_RANK(). Se si desidera assegnare lo stesso numero alle righe con lo stesso valore in una determinata colonna, ma senza saltare i numeri successivi, si può usare DENSE_RANK().

DENSE_RANK() è simile a RANK(), ma con DENSE_RANK(), il numero di classifica non viene saltato per gli stessi valori. Si veda l'esempio 1 qui sotto.

Esempio 1

SELECT
DENSE_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Questa query restituisce il risultato:

Per lo stesso importo di vendita, le righe hanno lo stesso numero. Tuttavia, le righe successive non vengono saltate e hanno il numero sequenziale successivo.

Si noti che per lo stesso importo di vendita di 11.000 dollari da parte dei venditori 11 e 13 il 2020-04-22, il numero di rango assegnato è 2, ma per i due record successivi con l'importo di vendita di 12.500 dollari, il numero di rango è 3. Questa funzione non salta il numero successivo.

RANK() funziona in modo diverso. In questo caso, per l'importo di vendita di 12.500 dollari, RANK() assegnerebbe il numero di rango 4, saltando il 3 perché due righe avevano 2.

DENSE_RANK() funziona anche con le partizioni. Vedere l'esempio 2 qui sotto.

Esempio 2

SELECT
DENSE_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Questa query restituisce il risultato:

In questo caso, il 2020-04-22, i venditori con un importo di vendita di 11.000 dollari hanno il numero di rango 1, ma il record successivo ha il numero di rango 2, non 3 come con RANK().

RANK_PERCENT()

L'ultima funzione di classificazione di cui parlerò è PERCENT_RANK(). Questa funzione restituisce i ranghi percentuali. Si veda l'esempio 1 qui sotto.

Esempio 1

SELECT
PERCENT_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Questa query restituisce il risultato:

Questa query calcola il rango relativo di ogni riga del set di risultati. Al valore più alto dell'importo di vendita viene assegnato 1 come rango percentuale, mentre al valore più basso viene assegnato 0. I valori intermedi vengono restituiti come rango da un intervallo di valori, maggiori di 0 e minori di 1.

A metà strada tra il valore più alto e quello più basso, il numero del rango percentuale è 0,5. In questo caso, ai venditori 11 e 12 al 2020-04-20 viene assegnato il numero di rango percentuale 0,5. I record dei venditori 11 e 13 del 2020-04-22 sono compresi tra 0 e 0,5, quindi hanno il numero di rango percentuale 0,1666666.

PERCENT_RANK() funziona in modo simile per le partizioni dei record. Si veda l'esempio 2 qui sotto.

Esempio 2

SELECT
PERCENT_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Questa query restituisce il risultato:

In ogni partizione, la riga più alta ha rango percentuale 1 e quella più bassa 0. In queste partizioni, non ci sono record tra il più alto e il più basso. Pertanto, non esiste un numero di rango percentuale compreso tra 0 e 1 come nell'esempio precedente.

Riepilogo

In questo articolo ho parlato delle funzioni di classificazione SQL, spiegandone la sintassi e utilizzandole in esempi reali. Se volete saperne di più sulle funzioni di classificazione, consultate il corso "Window Functions" su LearnSQL.it o leggete gli articoli "Esempio di funzione SQL Window con spiegazioni", "Come usare le funzioni di classificazione in SQL" e "SQL comune Window Functions: usare le partizioni con le funzioni di classificazione".