SQL Rimuovere Duplicati: Metodi completi e Best Practice

I record duplicati sono un problema comune che può compromettere l'integrità dei dati e le prestazioni del database. Rimuoverli è essenziale per mantenere l'accuratezza dei dati, ottimizzare lo storage e migliorare le prestazioni delle query. In questo articolo esploreremo varie tecniche per rimuovere righe duplicate in SQL, calibrate su diversi casi d'uso e sistemi di gestione dei database.

Per iniziare, ti consiglio vivamente i corsi di DataCamp Introduction to SQL e Learn SQL per apprendere le basi dell'estrazione e analisi dei dati con SQL. Inoltre, trovo utile l'SQL Basics Cheat Sheet, che puoi scaricare, come riferimento rapido perché contiene tutte le funzioni SQL più comuni.

TL;DR

• Usa SELECT DISTINCT o GROUP BY per recuperare righe uniche senza modificare la tabella
• Usa ROW_NUMBER() con una CTE e DELETE per controllare con precisione quali duplicati rimuovere in modo permanente
• Usa DELETE con una sottoquery (NOT IN / MIN()) per un approccio semplice alla deduplicazione in qualsiasi DBMS
• Per dataset di grandi dimensioni, usa tabelle temporanee per elaborare la rimozione dei duplicati in batch in modo sicuro
• Previeni i duplicati in modo proattivo con chiavi primarie, vincoli univoci e un'adeguata normalizzazione del database

Capire le righe duplicate in SQL

Le righe duplicate in SQL sono record all'interno di una tabella che contengono valori identici su tutte o su alcune colonne selezionate. Le cause comuni delle righe duplicate in SQL includono:

• Chiavi primarie mancanti: quando le tabelle non hanno una chiave primaria o un vincolo univoco, non c'è alcun meccanismo per impedire l'inserimento di dati duplicati. Questo può accadere quando una tabella non è normalizzata e/o ci sono problemi di dipendenza transitiva.
• Problemi di integrazione dei dati: durante l'unione di dataset da diverse fonti, join non corretti o inconsistenze nei formati dei dati possono introdurre duplicati per errore.
• Errori di inserimento manuale: l'errore umano, come inserire lo stesso record più volte, è un'altra causa comune di righe duplicate.

Nel resto dell'articolo vedremo come rimuovere i duplicati in SQL, dividendo il contenuto in due blocchi. Nella prima sezione tratteremo come rimuovere i duplicati nei dati che recuperi per un report o una dashboard; nella seconda sezione vedremo come rimuovere i duplicati direttamente nel database.

Come identificare le righe duplicate

Prima di rimuovere i duplicati, individua quali righe sono duplicate. Usa GROUP BY con HAVING COUNT(*) > 1 per trovare le righe che compaiono più di una volta:

SELECT Name, COUNT(*) AS duplicate_count
FROM customers
GROUP BY Name
HAVING COUNT(*) > 1;

Questa query restituisce ogni Name che compare più di una volta, insieme al numero di occorrenze. Puoi estenderla a più colonne aggiungendole sia alla clausola SELECT sia alla GROUP BY.

Per vedere tutte le righe con un rango che indica la loro posizione all'interno di ciascun gruppo di duplicati, usa ROW_NUMBER():

SELECT ID, Name,
       ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID) AS row_num
FROM customers;

Le righe in cui row_num > 1 sono duplicati. Una volta identificati, scegli il metodo di rimozione appropriato dalle sezioni seguenti.

Metodi per rimuovere i duplicati nei dati che recuperi

Esistono diversi metodi per rimuovere i duplicati durante il recupero dei record in SQL. Ogni metodo dipende dal DBMS, come SQL Server, MySQL e PostgreSQL. In questa sezione vedremo i metodi per rimuovere i duplicati evidenziando eventuali considerazioni specifiche per ciascun database. Ricorda che questi metodi filtrano i dati e restituiscono record unici, ma non modificano la tabella sottostante.

Uso della keyword DISTINCT

La keyword DISTINCT viene usata in un'istruzione SELECT per recuperare righe uniche. La sintassi di DISTINCT per rimuovere i duplicati è simile per i database MySQL, PostgreSQL e SQL Server. La query seguente recupera i nomi dei clienti unici dalla tabella customers.

SELECT DISTINCT Name 
FROM customers;

Uso di GROUP BY con funzioni di aggregazione

La clausola GROUP BY, combinata con funzioni di aggregazione come MAX(), MIN() o COUNT(), può aiutare a rimuovere record duplicati dalle tabelle. La clausola GROUP BY aiuta a selezionare i record specifici da conservare mentre si eliminano gli altri duplicati.

Supponiamo tu voglia eliminare i record duplicati dei clienti ma tenere quello con l'ID più alto. Userai la clausola GROUP BY con la funzione MAX(), come mostrato sotto.

-- Delete duplicate rows from the 'customers' table (aliased as c1)
DELETE c1
FROM customers c1
-- Find the maximum ID for each unique Name
JOIN (
    SELECT Name, MAX(ID) AS MaxID
    FROM customers
    GROUP BY Name
) c2
-- Match rows based on 'Name' and keep the row with the maximum ID
ON c1.Name = c2.Name 
AND c1.ID < c2.MaxID;

MySQL e SQL Server supportano la sintassi sopra di GROUP BY con funzioni di aggregazione e la clausola JOIN. 

Uso di ROW_NUMBER() con Common Table Expressions (CTE)

Con la funzione ROW_NUMBER() combinata con una Common Table Expression (CTE), puoi filtrare i duplicati in base ai tuoi criteri. La funzione ROW_NUMBER, usata con le clausole PARTITION BY e ORDER BY, assegna un numero sequenziale univoco a ciascuna riga. Questo metodo consente di filtrare le righe che non soddisfano i criteri richiesti.

La query seguente identifica i duplicati e rimuove tutte le occorrenze tranne la prima.

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Select only the unique records where RowNum = 1
SELECT ID, Name
FROM CTE
WHERE RowNum = 1;

Questo metodo funziona bene con le versioni moderne di SQL Server, MySQL e PostgreSQL. È utile per dataset più grandi o condizioni più complesse, perché ti permette di specificare esattamente quale duplicato mantenere.

Rimozione dei duplicati con self-JOIN

Un self-join ti consente di confrontare una tabella con sé stessa, risultando utile per identificare e rimuovere righe duplicate confrontando i record in base a criteri specifici. Nell'esempio seguente si usa il self-join per eliminare la riga con l'ID più alto, mantenendo solo la prima occorrenza di ciascun nome.

-- Delete duplicate rows using self-join
DELETE c1
FROM customers c1
JOIN customers c2
ON c1.Name = c2.Name AND c1.ID > c2.ID;

Il metodo sopra funziona nei principali database, inclusi SQL Server, MySQL e PostgreSQL. Dai un'occhiata al nostro corso Intermediate SQL per saperne di più sull'uso di funzioni di aggregazione e join per filtrare i dati.

Metodi per rimuovere i duplicati nel database

Oltre a rimuovere i record duplicati tramite query, puoi anche eliminarli in modo permanente dal database. Questo approccio è importante per mantenere la qualità dei dati. I seguenti metodi vengono usati per rimuovere i duplicati dal database.

Uso di ROW_NUMBER() e DELETE

La funzione ROW_NUMBER() assegna un numero sequenziale alle righe all'interno di una partizione definita. Usata con l'istruzione DELETE, aiuta a identificare i duplicati classificando le righe in base a colonne specifiche e rimuovendo i record indesiderati. Questo metodo si applica alle versioni moderne di MySQL (dalla 8.0), PostgreSQL e SQL Server.

Supponiamo tu voglia rimuovere i record duplicati dei clienti in base alla colonna Name, mantenendo solo la prima occorrenza (il ID più piccolo):

-- Common Table Expression (CTE) to rank rows based on 'Name'
WITH CTE AS (
    SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY ID ASC) AS RowNum
    FROM customers
)
-- Delete rows from the 'customers' table where the row number is greater than 1
DELETE FROM customers
WHERE ID IN (SELECT ID FROM CTE WHERE RowNum > 1);

Uso di DELETE con sottoquery

A volte, una semplice operazione DELETE usando una sottoquery può rimuovere i duplicati dal database. Questo metodo è adatto per le versioni più vecchie di MySQL o PostgreSQL in cui ROW_NUMBER() potrebbe non essere disponibile.

La query seguente elimina le righe dalla tabella customers in cui l'ID non è il minimo per ciascun Name, mantenendo solo la riga con l'ID più piccolo per ogni Name univoco.

-- Delete rows from the 'customers' table
DELETE FROM customers
WHERE ID NOT IN (
    -- Subquery to find the minimum ID for each unique Name
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Uso di GROUP BY con clausola HAVING

Quando devi verificare valori duplicati in colonne specifiche, la clausola GROUP BY combinata con la clausola HAVING può essere usata per identificare i duplicati. Questo metodo ti consente di eliminare righe specifiche in base ai criteri indicati. È compatibile con SQL Server, MySQL e PostgreSQL.

Le query seguenti prima identificano quali nomi compaiono più di una volta, poi eliminano i duplicati mantenendo la riga con l'ID più piccolo per ciascun Name.

-- Step 1: Identify which Names have duplicates
SELECT Name, COUNT(*) AS duplicate_count
FROM customers
GROUP BY Name
HAVING COUNT(*) > 1;

-- Step 2: Delete duplicate rows, keeping the smallest ID for each Name
DELETE FROM customers
WHERE ID NOT IN (
    SELECT MIN(ID)
    FROM customers
    GROUP BY Name
);

Uso di tabelle temporanee per l'elaborazione in batch

Le tabelle temporanee sono efficienti per l'elaborazione in batch e la rimozione di duplicati in dataset di grandi dimensioni. Questo metodo è utile quando singole query possono causare problemi di prestazioni. La query seguente crea una tabella temporanea per memorizzare l'ID minimo per ciascun Name ed elimina le righe dalla tabella customers in cui l'ID non è presente nella tabella temp_customers.

-- Create a temporary table with unique records
CREATE TEMPORARY TABLE temp_customers AS
SELECT MIN(ID) AS KeepID, Name
FROM customers
GROUP BY Name;

-- Delete duplicates not in the temporary table
DELETE FROM customers
WHERE ID NOT IN (SELECT KeepID FROM temp_customers);

-- Clean up
DROP TABLE temp_customers;

La sintassi sopra con CREATE TEMPORARY TABLE è supportata solo nei database MySQL e PostgreSQL. 

Rimuovere i duplicati in SQL Server

SQL Server offre diversi metodi per rimuovere record duplicati dal database. Questi includono l'uso di DISTINCT con INTO, ROW_NUMBER() e tabelle temporanee.

Uso di DISTINCT con INTO

Puoi usare la keyword DISTINCT in una SELECT per creare una nuova tabella con record unici. Puoi eliminare la vecchia tabella dopo aver verificato che la nuova contenga i record desiderati. Nell'esempio seguente viene creata la tabella unique_customers con i record unici ricavati da customers.

-- Select distinct rows from 'customers' and create a new table 'unique_customers'
SELECT DISTINCT *
INTO unique_customers
FROM customers;
-- Drop the original 'customers' table to remove it from the database
DROP TABLE customers;
-- Rename the 'unique_customers' table to 'customers' to replace the original table
EXEC sp_rename 'unique_customers', 'customers';

Uso di ROW_NUMBER()

Puoi anche usare la funzione ROW_NUMBER() per rimuovere record duplicati in SQL Server. Supponi di avere una tabella Customers con righe duplicate in base alla colonna CustomerName e di voler eliminare tutte le occorrenze tranne la prima per ciascun gruppo di duplicati.

-- Common Table Expression (CTE) to assign a row number to each customer 
WITH CTE AS (
    SELECT CustomerID, CustomerName, ROW_NUMBER() OVER (PARTITION BY CustomerName ORDER BY CustomerID ASC) AS RowNum
    FROM Customers
)
-- Delete rows from the CTE
DELETE FROM CTE
WHERE RowNum > 1;

Uso di tabella temporanea

Poiché SQL Server non supporta CREATE TEMPORARY TABLE, si usa SELECT INTO. Le tabelle temporanee in SQL Server usano il prefisso # per il nome della tabella.

-- Create a temporary table
SELECT MIN(CustomerID) AS ID, CustomerName
INTO #temp_customers
FROM customers
GROUP BY CustomerName;
-- Delete rows from the 'customers' table where the ID is not in the temporary table
DELETE FROM customers
WHERE CustomerID NOT IN (SELECT ID FROM #temp_customers);
-- Optionally drop the temporary table after use
DROP TABLE #temp_customers;

Ti suggerisco di provare il nostro skill track SQL Server Fundamentals per migliorare le tue abilità nel join delle tabelle e nell'analisi dei dati. Il career track SQL Server Developer ti fornirà le competenze per scrivere, risolvere i problemi e ottimizzare le tue query con SQL Server.  

Riferimento rapido: Metodi di deduplicazione SQL

La tabella seguente riassume tutti i metodi di deduplicazione trattati in questo articolo, così puoi scegliere rapidamente l'approccio giusto per la tua situazione.

Best practice

Le righe duplicate sono un problema comune che influisce sulla qualità dei dati e sulle prestazioni del database. Considera le seguenti best practice per evitare l'inserimento di record duplicati nel tuo database.

• Usa chiavi primarie: la colonna di chiave primaria garantisce che ogni record contenga informazioni uniche, impedendo l'ingresso di valori duplicati nella tabella.
• Implementa vincoli univoci: applicare vincoli univoci a qualsiasi colonna assicura che non esistano duplicati tra le colonne non chiave primaria, come indirizzi email o numeri di telefono.
• Progettazione e normalizzazione corrette del database: una progettazione efficace dello schema e la normalizzazione del database aiutano a ridurre ridondanza e dati duplicati. Questo approccio assicura che ogni record sia memorizzato nelle tabelle appropriate.
• Usa indici univoci: usa indici univoci per garantire che determinate combinazioni di colonne siano uniche senza richiedere vincoli a livello di tabella sull'intero dataset.
• Audit regolari dei dati: esegui audit regolari dei dati lanciando query per identificare potenziali duplicati in base alle tue regole di business.

Conclusione

Identificare e rimuovere righe duplicate è importante per mantenere l'efficienza del database e l'accuratezza dei dati. È sempre buona norma eseguire un backup dei dati prima di apportare modifiche per evitare perdite accidentali.

Se sei interessato a diventare un data analyst competente, dai un'occhiata al nostro career track Associate Data Analyst in SQL per apprendere le competenze necessarie. Il corso Reporting in SQL è adatto anche se vuoi imparare a creare dashboard professionali usando SQL. Infine, ti consiglio di ottenere la SQL Associate Certification per dimostrare la tua padronanza di SQL per l'analisi dei dati e distinguerti tra gli altri professionisti del settore.