24
Rimuovere i record duplicati da una tabella
Vi è mai capitato di trovarvi a dover sistemare i dati di un database a colpi di istruzioni SQL? A me purtroppo si e fin troppo spesso!
Ultimamente mi è passato per le mani un database SQL Server progettato ad-minchiam (è un termine tecnico 😀): c'erano tabelle senza né chiavi né indici e di conseguenza un mucchio di record duplicati (avevano solo pochi campi diversi, tipo la data di inserimento).
Mentre riordinavo un po' quel macello di dati ho pensato che questa mia esperienza potrebbe tornare utile a qualcuno e mi è venuta l'idea di scrivere questo post per descrivere la procedura che ho adottato per rimuovere i duplicati dalla tabella. Ovviamente, per motivi di riservatezza non utilizzerò il database del Cliente ma ne inventerò uno a scopo didattico.
Nell'esempio che andremo ad analizzare utilizzeremo il nome ed il cognome come elemento di raggruppamento, ignorando i possibili casi di omonimia e altre variabili che si devono invece tenere in considerazione in un database di produzione.
Partiamo subito definendo il nostro database di partenza. Vi allego lo script per la creazione in modo che non perditate tempo a crearlo a mano.
USE [master];
GO
IF NOT EXISTS (SELECT 1 FROM sys.databases a WHERE a.[name] = 'Dummy')
CREATE DATABASE Dummy;
GO
USE Dummy;
GO
CREATE TABLE Employee
(
first_name NVARCHAR(100) NOT NULL,
last_name NVARCHAR(100) NOT NULL,
email NVARCHAR(255) NOT NULL,
creation_time DATETIME2(0) NOT NULL DEFAULT (GETDATE())
);
GO
INSERT INTO
Employee (first_name, last_name, email, creation_time)
VALUES
('Rossi', 'Mario', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Rossi', 'Mario', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Rossi', 'Mario', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Bianchi', 'Luigi', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Bianchi', 'Luigi', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Rossi', 'Mario', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Rossi', 'Mario', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Bianchi', 'Luigi', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE())),
('Bianchi', 'Luigi', '[email protected]', DATEADD(HOUR, CAST(RAND() * -10000 AS INT), GETDATE()));
GONello script appena proposto ho creato un database di nome Dummy, una tabella Employee ed infine ho popolato la tabella con dei dati (nell'esempio ho voluto casualizzare un po' l'orario di creazione del record). Inoltre, ho inserito volutamente l'email diversa per lo stesso utente in modo da simulare la situazione che mi è capitata, ovvero, che ad un certo punto alcuni record sono stati aggiornati nel tempo. Di seguito la tabella con i dati.
Il nostro compito ora è quello di eliminare tutti i duplicati dalla tabella mantenendo solamente l'ultimo record (che assumiamo essere quello corretto e più aggiornato).
La prima soluzione che mi è venuta in mente è stata quella di utilizzare una
SELECT DISTINCT first_name, last_name, email INTO NewEmployee FROM Employee (o usare una GROUP BY in alternativa) ma mi sono reso conto subito che non era una "strada percorribile" perché io volevo preservare l'ultimo record inserito e cancellare gli altri.Ho quindi deciso di utilizzare una Common Table Expression (di seguito solo CTE), funzionalità offerta da alcuni RDBMS molto interessante quando poco conosciuta.
Non mi dilungherò su cosa è una Common Table Expression perché non è tra gli obiettivi del post e perché ho anche in previsione di scrivere un articolo dedicato all'argomento.
SELECT
A.first_name,
A.last_name,
A.email,
A.creation_time
FROM
Employee A;
GO
;WITH cte AS
(
SELECT
A.first_name,
A.last_name,
A.email,
A.creation_time,
ROW_NUMBER() OVER (PARTITION BY A.first_name, A.last_name ORDER BY A.creation_time DESC) AS "duplicates"
FROM
Employee A
)
DELETE FROM
cte
WHERE
cte.duplicates > 1;
GO
SELECT
A.first_name,
A.last_name,
A.email,
A.creation_time
FROM
Employee A;
GOLa prima e la terza istruzione (le
SELECT) ignoriamole, ci servono solo per ispezionare il contenuto della tabella prima e dopo il nostro intervento. Concentriamoci invece sulla seconda istruzione dato che, come avrete già intuito, il lavoro di pulizia viene fatto qui. Grazie alla CTE, questa istruzione possiamo immaginarla suddivisa in due sotto-istruzioni: nella prima parte, viene definita la CTE (di nome cte e si, ho molta fantasia nel dare i nomi 😀) in cui vengono partizionati i record della tabella per i campi first_name e last_name aggiungendo "al volo" un campo numeratore, ordinando il risultato in maniera decrescente per il campo creation_time. Nella seconda parte invece, vengono cancellati tutti i record dalla tabella che hanno il campo duplicates (il numeratore definito "al volo") maggiore di 1.Ed ecco qui sotto il risultato!
Bene, questo è il modo che ho usato io per raggiungere il mio scopo. Come vi sembra? Avete qualche consiglio da darmi?