ROW_NUMBER() di MySQL spiegato (MySQL 8.0): classificazione, query Top-N e deduplicazione

1. Introduzione

La versione 8.0 di MySQL ha introdotto molte nuove funzionalità, e una delle più notevoli è il supporto per le funzioni di finestra. In questo articolo, ci concentreremo su una delle funzioni più frequentemente utilizzate: ROW_NUMBER().

La funzione ROW_NUMBER() offre potenti capacità per l’analisi e il reporting dei dati, rendendo facile ordinare e classificare i dati in base a condizioni specifiche. Questo articolo spiega tutto, dall’uso base e esempi pratici ad approcci alternativi per versioni precedenti di MySQL.

Lettori target

• Utenti principianti a intermedi con conoscenze base di SQL
• Ingegneri e analisti di dati che elaborano e analizzano dati utilizzando MySQL
• Chiunque stia considerando la migrazione alla versione più recente di MySQL

Vantaggi di ROW_NUMBER()

Questa funzione ti permette di assegnare un numero univoco a ogni riga in base a condizioni specifiche. Ad esempio, puoi scrivere facilmente query come “crea una classifica in ordine decrescente delle vendite” o “estrai e organizza dati duplicati” in modo conciso.

Nelle versioni precedenti, dovevi spesso scrivere query complesse utilizzando variabili definite dall’utente. Con ROW_NUMBER(), il tuo SQL diventa più semplice e leggibile.

In questo articolo, utilizzeremo esempi concreti di query e li spiegheremo in modo accessibile per principianti. Nella sezione successiva, esamineremo più da vicino la sintassi base e il comportamento di questa funzione.

2. Cos’è la funzione ROW_NUMBER()?

La funzione ROW_NUMBER(), aggiunta di recente in MySQL 8.0, è un tipo di funzione di finestra che assegna numeri sequenziali alle righe. Può numerare le righe in un ordine specifico e/o all’interno di ciascun gruppo, il che è estremamente utile per l’analisi e il reporting dei dati. Qui spiegheremo la sintassi base in dettaglio con esempi pratici.

Sintassi base di ROW_NUMBER()

Innanzitutto, il formato base di ROW_NUMBER() è il seguente.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Significato di ciascun elemento

• ROW_NUMBER() : Assegna un numero sequenziale a ogni riga.
• OVER : Parola chiave utilizzata per definire la finestra per una funzione di finestra.
• PARTITION BY : Raggruppa i dati per la colonna specificata. Opzionale. Se omessa, la numerazione viene applicata a tutte le righe.
• ORDER BY : Definisce l’ordinamento utilizzato per assegnare i numeri, ovvero i criteri di ordinamento.

Esempio base

Ad esempio, supponi di avere una tabella chiamata “sales” con i seguenti dati.

Per assegnare numeri sequenziali all’interno di ciascun dipartimento in ordine decrescente delle vendite, utilizza la seguente query.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Risultato

Da questo risultato, puoi vedere che le classifiche per vendite all’interno di ciascun dipartimento sono visualizzate.

Come utilizzare PARTITION BY

Nell’esempio precedente, i dati sono raggruppati per la colonna “department”. Questo assegna una sequenza separata per ciascun dipartimento.

Se ometti PARTITION BY, la numerazione viene assegnata a tutte le righe come una singola sequenza.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Risultato

Caratteristiche e avvertenze di ROW_NUMBER()

• Numerazione univoca : Anche se i valori sono uguali, i numeri assegnati sono univoci.
• Gestione dei NULL : Se ORDER BY include NULL, appaiono per primi in ordine ascendente e per ultimi in ordine decrescente.
• Impatto sulle prestazioni : Per dataset grandi, ORDER BY può essere costoso, quindi un’indicizzazione adeguata è importante.

3. Casi d’uso pratici

Ecco scenari pratici che utilizzano la funzione ROW_NUMBER() di MySQL. Questa funzione è utile in molti casi reali, come la classificazione dei dati e la gestione dei duplicati.

3-1. Classifica all’interno di ciascun gruppo

Ad esempio, considera il caso in cui desideri “classificare i dipendenti per vendite all’interno di ciascun dipartimento” utilizzando i dati di vendita. Usa il seguente set di dati come esempio.

Esempio di query: Classifica delle vendite per dipartimento

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Risultato:

In questo modo, ogni dipartimento ottiene la propria sequenza in ordine decrescente di vendite, facilitando la generazione delle classifiche.

3-2. Estrarre le prime N righe

Successivamente, esaminiamo un caso in cui desideri “estrarre i primi 3 dipendenti per vendite all’interno di ciascun dipartimento”.

Esempio di query: Estrarre le prime N righe

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Risultato:

Questo esempio recupera solo le prime 3 righe per vendite all’interno di ciascun dipartimento. Come puoi vedere, ROW_NUMBER() è adatto non solo per la classificazione ma anche per filtrare i risultati migliori.

3-3. Trovare e rimuovere dati duplicati

I database a volte contengono record duplicati. In tali casi, è possibile gestirli facilmente usando ROW_NUMBER().

Esempio di query: Rilevare i duplicati

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Questa query rileva i duplicati quando esistono più record per lo stesso nome del dipendente.

Esempio di query: Eliminare i duplicati

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Riepilogo

ROW_NUMBER() è utile in una varietà di scenari, come:

1. Classificazione all’interno di ogni gruppo
2. Estrarre le prime N righe
3. Rilevare ed eliminare i duplicati

Ciò rende l’elaborazione e l’analisi dei dati complessi più semplici ed efficienti.

4. Confronto con altre funzioni di finestra

In MySQL 8.0, oltre a ROW_NUMBER(), esistono funzioni di finestra come RANK() e DENSE_RANK() che possono essere utilizzate per classifiche e calcoli di posizione. Sebbene abbiano ruoli simili, il loro comportamento e i risultati differiscono. Qui confronteremo ciascuna funzione e spiegheremo quando usarle.

4-1. Funzione RANK()

La funzione RANK() assegna ranghi, dando lo stesso rango a valori uguali e saltando il numero di rango successivo.

Sintassi di base

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Esempio

Utilizzando i seguenti dati, calcola i ranghi delle vendite.

Esempio di query: Utilizzare RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Risultato:

Punti chiave:

• A e B con lo stesso importo di vendite (800) sono entrambi trattati come rango “1”.
• Il rango successivo “2” è saltato, quindi C diventa rango “3”.

4-2. Funzione DENSE_RANK()

La funzione DENSE_RANK() assegna anch’essa lo stesso rango a valori uguali, ma non salta il numero di rango successivo.

Sintassi di base

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Esempio

Utilizzando gli stessi dati di sopra, prova la funzione DENSE_RANK().

Esempio di query: Utilizzare DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Risultato:

Punti chiave:

• A e B con lo stesso importo di vendite (800) sono entrambi trattati come rango “1”.
• A differenza di RANK(), il rango successivo inizia da “2”, preservando la continuità del ranking.

4-3. Come ROW_NUMBER() differisce

La funzione ROW_NUMBER() differisce dalle altre due in quanto assegna un numero univoco anche quando i valori sono gli stessi.

Esempio

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Risultato:

Punti chiave:

• Anche se i valori sono gli stessi, ogni riga ottiene un numero univoco, quindi non ci sono ranghi duplicati.
• Questo è utile quando è necessario un controllo rigoroso dell’ordinamento o un’unicità per riga.

4-4. Riepilogo rapido dei casi d’uso

Riepilogo

ROW_NUMBER(), RANK() e DENSE_RANK() devono essere utilizzati in modo appropriato a seconda dello scenario.

1. ROW_NUMBER() è ideale quando è necessario un numero univoco per riga.
2. RANK() è utile quando si desidera che i pareggi condividano un rango e si vuole enfatizzare le lacune nei ranghi.
3. DENSE_RANK() è adatto quando si vogliono ranghi continui senza lacune.

5. Alternative per versioni di MySQL inferiori a 8.0

Nelle versioni precedenti a MySQL 8.0, ROW_NUMBER() e altre funzioni di finestra non sono supportate. Tuttavia, è possibile ottenere un comportamento simile utilizzando variabili definite dall’utente. Questa sezione spiega alternative pratiche per versioni di MySQL inferiori a 8.0.

5-1. Numerazione sequenziale utilizzando variabili definite dall’utente

In MySQL 5.7 e precedenti, è possibile utilizzare variabili definite dall’utente per assegnare numeri sequenziali per riga. Vediamo l’esempio seguente.

Esempio: Ranking delle vendite per reparto

Dati di esempio:

Query:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Risultato:

5-2. Estrazione delle prime N righe

Per recuperare le prime N righe, è possibile utilizzare variabili definite dall’utente in modo simile.

Query:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Risultato:

Questa query assegna ranghi per reparto e quindi estrae solo le righe all’interno delle prime 3.

5-3. Rilevamento ed eliminazione dei duplicati

È anche possibile gestire i dati duplicati utilizzando variabili definite dall’utente.

Esempio di query: Rilevamento duplicati

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Esempio di query: Eliminazione duplicati

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Avvertenze nell’uso delle variabili definite dall’utente

1. Dipendenza dalla sessione

• Le variabili definite dall’utente sono valide solo all’interno della sessione corrente. Non possono essere riutilizzate tra query o sessioni diverse.

1. Dipendenza dall’ordine di elaborazione

• Le variabili definite dall’utente dipendono dall’ordine di esecuzione, quindi impostare correttamente ORDER BY è critico.

1. Leggibilità e manutenibilità di SQL

• Le query possono diventare complesse, quindi in MySQL 8.0 e successive, l’uso delle funzioni di finestra è raccomandato.

Riepilogo

Nelle versioni di MySQL precedenti alla 8.0, è possibile utilizzare variabili definite dall’utente per implementare la numerazione sequenziale e il ranking al posto delle funzioni finestra. Tuttavia, poiché le query tendono a diventare più complesse, è consigliabile considerare la migrazione a una versione più recente ogni volta che è possibile.

6. Avvertenze e Buone Pratiche

Le funzioni ROW_NUMBER() di MySQL e le alternative basate su variabili sono molto comode, ma ci sono punti importanti da tenere presente per eseguirle in modo accurato ed efficiente. Questa sezione spiega le avvertenze pratiche e le buone pratiche per l’ottimizzazione delle prestazioni.

6-1. Considerazioni sulle prestazioni

1. Costo di ORDER BY

ROW_NUMBER() è sempre usato con ORDER BY. Poiché richiede l’ordinamento, il tempo di elaborazione può aumentare notevolmente per grandi set di dati.

Mitigazione:

• Usa indici: Aggiungi indici alle colonne utilizzate in ORDER BY per velocizzare l’ordinamento.
• Usa LIMIT: Recupera solo il numero di righe di cui hai realmente bisogno per ridurre la quantità di dati elaborati.

Esempio:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Aumento dell’uso della memoria e I/O su disco

Le funzioni finestra vengono elaborate utilizzando tabelle temporanee e memoria. Con l’aumento del volume dei dati, il consumo di memoria e l’I/O su disco possono aumentare.

Mitigazione:

• Dividi le query: Suddividi l’elaborazione in query più piccole ed estrai i dati passo passo per ridurre il carico.
• Usa tabelle temporanee: Memorizza i dati estratti in una tabella temporanea ed esegui l’aggregazione da lì per distribuire il carico di lavoro.

6-2. Suggerimenti per l’ottimizzazione delle query

1. Controlla il piano di esecuzione

In MySQL, è possibile utilizzare EXPLAIN per controllare il piano di esecuzione della query. Questo aiuta a verificare se gli indici vengono utilizzati correttamente.

Esempio:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Output di esempio:

Se vedi Using index, indica che l’indice è utilizzato in modo appropriato.

2. Ottimizza gli indici

Assicurati di aggiungere indici alle colonne usate in ORDER BY e WHERE. Presta particolare attenzione a quanto segue.

• Indici a colonna singola: Buoni per condizioni di ordinamento semplici
• Indici compositi: Efficaci quando più colonne sono coinvolte nelle condizioni

Esempio:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Usa l’elaborazione a batch

Invece di elaborare un enorme set di dati tutto in una volta, puoi ridurre il carico elaborando i dati a batch.

Esempio:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Mantenere la coerenza dei dati

1. Aggiornamenti e ricalcolo

Quando le righe vengono inserite o eliminate, la numerazione può cambiare. Costruisci un meccanismo per ricalcolare i numeri secondo necessità.

Esempio:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Utilizzare una vista ti aiuta a mantenere i ranking aggiornati in base agli ultimi dati.

6-4. Esempio di query con le migliori pratiche

Di seguito è riportato un esempio di migliori pratiche che considerano prestazioni e manutenibilità.

Esempio: Estrarre le prime N righe

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Questa struttura utilizza un’espressione di tabella comune (CTE) per migliorare la leggibilità e il riutilizzo.

Riepilogo

Quando si utilizza ROW_NUMBER() o le sue alternative, tenere presenti questi punti:

1. Migliora la velocità attraverso l’ottimizzazione degli indici.
2. Identifica i colli di bottiglia controllando il piano di esecuzione.
3. Pianifica gli aggiornamenti dei dati e mantieni la consistenza.
4. Usa l’elaborazione batch e le CTE per distribuire il carico.

Applicando queste best practice si abiliterà un’elaborazione efficiente per l’analisi e il reporting di dati su larga scala.

7. Conclusione

In questo articolo, ci siamo concentrati sulla funzione ROW_NUMBER() di MySQL, spiegando tutto dall’uso base ed esempi pratici alle alternative per versioni precedenti, più avvertenze e best practice. In questa sezione, ricapitoleremo i punti chiave e riassumeremo i takeaway pratici.

7-1. Perché ROW_NUMBER() è utile

La funzione ROW_NUMBER() è particolarmente comoda per l’analisi e il reporting dei dati nei seguenti modi:

1. Numerazione sequenziale all’interno di gruppi: Crea facilmente classifiche di vendite per dipartimento o classifiche basate su categoria.
2. Estrazione delle prime N righe: Filtra ed estrae efficientemente i dati in base a condizioni specifiche.
3. Rilevamento ed eliminazione di duplicati: Utile per la pulizia e l’organizzazione dei dati.

Poiché semplifica le query complesse, migliora significativamente la leggibilità e la manutenibilità di SQL.

7-2. Confronto con altre funzioni finestra

Rispetto alle funzioni finestra come RANK() e DENSE_RANK(), ROW_NUMBER() si differenzia in quanto assegna un numero univoco anche per valori identici.

Scegliere la funzione giusta:
Selezionare la migliore funzione per il tuo scopo abilita un’elaborazione efficiente dei dati.

7-3. Gestione di versioni MySQL precedenti

Per ambienti inferiori a MySQL 8.0, abbiamo anche introdotto approcci usando variabili definite dall’utente. Tuttavia, dovresti considerare queste avvertenze:

• Ridotta leggibilità a causa di SQL più complesso
• L’ottimizzazione delle query può essere più difficile in alcuni casi
• Potrebbe essere richiesta una gestione aggiuntiva per mantenere la consistenza dei dati

Se possibile, considera fortemente la migrazione a MySQL 8.0 o successivo e l’uso di funzioni finestra.

7-4. Punti chiave per l’ottimizzazione delle prestazioni

1. Usa indici: Aggiungi indici alle colonne usate in ORDER BY per migliorare la velocità.
2. Controlla i piani di esecuzione: Valida le prestazioni in anticipo con EXPLAIN.
3. Adotta l’elaborazione batch: Elabora grandi dataset in chunk più piccoli per distribuire il carico.
4. Usa viste e CTE: Migliora la riutilizzabilità e semplifica le query complesse.

Applicando queste tecniche, puoi ottenere un’elaborazione efficiente e stabile dei dati.

7-5. Note finali

ROW_NUMBER() è uno strumento potente che può migliorare significativamente l’efficienza dell’analisi dei dati.
In questo articolo, abbiamo coperto tutto dalla sintassi base ed esempi pratici alle avvertenze e alternative.

Ti incoraggiamo a eseguire le query tu stesso mentre segui questo articolo. Migliorare le tue abilità SQL ti aiuterà ad affrontare analisi e reporting di dati più complessi con fiducia.

Appendice: Risorse di riferimento

• Documentazione ufficiale: MySQL Window Functions
• Ambiente SQL online: SQL Fiddle (uno strumento che ti permette di eseguire e testare SQL online)