Funzioni finestra principali: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Nella lezione precedente abbiamo capito a cosa servono le funzioni finestra. Ora vediamo alcune funzioni specifiche e i loro risultati. I dettagli della sintassi li vedremo nella prossima lezione.

Funzione ROW_NUMBER()

La funzione ROW_NUMBER() restituisce un numero unico per ogni riga all'interno della finestra. È semplicemente una numerazione delle righe nell'ordine definito da ORDER BY.

Sintassi:

ROW_NUMBER() OVER ([PARTITION BY colonna] ORDER BY colonna)

Dove:

• PARTITION BY colonna (opzionale): divide i dati in gruppi. Se lo salti, la numerazione sarà globale su tutto il set.
• ORDER BY colonna: definisce l'ordine delle righe per la numerazione.

Esempio. Numerazione delle righe in una tabella

Vediamo la tabella students, che contiene info sugli studenti e i loro voti.

SELECT * FROM students;

Ora numeriamo le righe in ordine decrescente di voto (score):

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Risultato:

Ogni riga ha ricevuto un numero d'ordine unico — considerando l'ordinamento decrescente dei voti.

È un'operazione semplice ma potente — aggiungere il numero di riga al risultato della query. Nel classico SELECT non puoi farlo senza funzioni finestra.

Funzione RANK()

La funzione RANK() è molto simile a ROW_NUMBER(), ma tiene conto dei valori uguali. Se le righe hanno lo stesso valore nell'ordinamento, ricevono lo stesso rank, e il successivo viene saltato.

Sintassi:

RANK() OVER ([PARTITION BY colonna] ORDER BY colonna)

Esempio. Ranking degli studenti in base ai voti

Usiamo RANK() sugli stessi dati:

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Risultato:

Qui le righe con lo stesso valore (95 e 87) hanno lo stesso rank, e i rank successivi sono saltati.

Funzione DENSE_RANK()

DENSE_RANK() è simile a RANK(), ma non salta i valori dei rank. Questo significa che se ci sono righe duplicate, il rank successivo sarà solo uno in più del precedente.

Sintassi:

DENSE_RANK() OVER ([PARTITION BY colonna] ORDER BY colonna)

Esempio. Ranking denso

Usiamo DENSE_RANK() sugli stessi dati:

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Risultato:

Qui, a differenza di RANK(), i valori dei rank crescono senza salti.

Funzione NTILE()

La funzione NTILE() divide le righe in gruppi uguali (quanti) e assegna a ogni riga il numero del gruppo.

Sintassi:

NTILE(n) OVER ([PARTITION BY colonna] ORDER BY colonna)

• n: il numero di gruppi in cui vuoi dividere i dati.

Esempio. Divisione degli studenti in 3 gruppi

Dividiamo gli studenti in 3 gruppi in base ai voti decrescenti:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Risultato:

Nota: se le righe non possono essere divise in gruppi perfettamente uguali, le righe in più vanno nei primi gruppi. In questo esempio i primi due gruppi hanno due righe ciascuno, l'ultimo solo una.

Quando usare quale funzione?

• ROW_NUMBER(): per numerare univocamente le righe nell'ordine scelto.
• RANK(): per ranking considerando i valori uguali e saltando il rank successivo.
• DENSE_RANK(): per ranking considerando i valori uguali senza saltare i rank.
• NTILE(): Per dividere le righe in gruppi uguali.

Tutte queste funzioni ti aiutano ad analizzare i dati a un livello completamente nuovo. Usale quando ti serve flessibilità nel calcolare numeri d'ordine o nel dividere i dati.