Coding Interview Prep · Lezione

Deduplicare le righe in sicurezza

Rimuovere righe duplicate esatte o quasi duplicate conservando un record canonico

Deduplicare le righe in sicurezza è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Il problema della deduplicazione

"Questa tabella contiene righe duplicate. Le rimuova mantenendo una copia di ciascuna." Quasi ogni colloquio di data engineering include una variante di questo problema. La difficoltà consiste nel farlo in modo sicuro: mantenere esattamente una riga canonica senza eliminare per errore record distinti che sembrano soltanto simili.

In questa lezione vedremo come rilevare i duplicati, scegliere quale copia mantenere, deduplicare in una SELECT ed eliminare fisicamente i duplicati da una tabella.

Definire prima cosa è un duplicato

La prima domanda da porre all'intervistatore è: "Che cosa rende due righe duplicate?" Le possibilità includono:

Duplicati esatti: ogni colonna è identica.
Duplicati per chiave: stessa chiave di business, ad esempio lo stesso email, ma le altre colonne possono differire.

La tecnica cambia in ciascun caso. Non faccia supposizioni: chiarire la definizione di duplicato è il passaggio più importante e gli intervistatori si aspettano che lo chieda.

Rilevare i duplicati

Per trovare le chiavi duplicate, raggruppi in base alle colonne che definiscono un duplicato e mantenga i gruppi con un conteggio superiore a uno. In questo modo saprà quali chiavi sono coinvolte e quante copie esistono prima di modificare qualsiasi dato.

Eseguire prima una query di rilevamento è una buona pratica da esplicitare: consente di verificare l'entità del problema prima di eliminare i dati.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Duplicati esatti: DISTINCT

Se i duplicati sono davvero identici in ogni colonna, una vista deduplicata in sola lettura si ottiene semplicemente con SELECT DISTINCT *. Anche UNION (senza ALL) rimuove le righe duplicate.

Tuttavia, DISTINCT è utile solo quando desidera deduplicare l'intera riga e non deve scegliere quale copia mantenere. Per i duplicati per chiave, in cui le colonne differiscono, serve il ranking.

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

Duplicati per chiave: ROW_NUMBER

Quando le righe condividono una chiave ma differiscono nelle altre colonne, effettui il partitioning per chiave e numeri ogni copia. rn = 1 identifica la riga da mantenere; rn > 1 identifica le copie aggiuntive da eliminare.

L'ORDER BY all'interno della finestra decide quale copia è quella canonica. Lo scelga deliberatamente: ad esempio, può mantenere la riga aggiornata più di recente.

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

Selezionare la copia canonica

Inserisca la numerazione in una CTE e mantenga solo rn = 1. In questo modo otterrà una riga per chiave, precisamente quella che il suo ORDER BY ha classificato per prima.

Questa forma di SELECT non è distruttiva: è perfetta per creare una vista pulita o alimentare una tabella di destinazione deduplicata con un INSERT ... SELECT, senza modificare la sorgente.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

La scelta dell'ordine è importante

L'ORDER BY all'interno del partitioning è una decisione di business, non una formalità:

ORDER BY updated_at DESC mantiene il record più recente.
ORDER BY created_at ASC mantiene il record originale.
ORDER BY id ASC mantiene la chiave surrogata più bassa, una scelta arbitraria ma stabile.

Aggiunga un criterio di spareggio univoco, così la riga scelta sarà deterministica quando anche la colonna dell'ordinamento principale presenta un pari merito.

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

Eliminare fisicamente i duplicati

Per rimuovere effettivamente i duplicati dalla tabella, identifichi le righe aggiuntive (rn > 1) ed elimini queste ultime. In Postgres e SQL Server può eliminare i dati usando una CTE; in MySQL sono comuni un self-join o una sottoquery.

Esegua sempre prima la SELECT corrispondente per visualizzare in anteprima esattamente quali righe scompariranno. È l'eliminazione alla cieca a far fallire i candidati su questa domanda.

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

Il pattern di eliminazione con self-join

Un approccio classico e portabile mantiene la riga con il valore id più piccolo per ogni chiave duplicata ed elimina le altre usando un self-join. Non richiede funzioni finestra, un aspetto importante nei motori più datati.

La condizione di join associa ogni riga a un'altra riga che condivide la stessa chiave ma ha un id più piccolo; ogni riga che possiede un gemello con id inferiore è un duplicato da eliminare.

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

Checklist di sicurezza

Prima di eliminare i dati, si protegga:

Racchiuda l'eliminazione in una transazione, così potrà eseguire ROLLBACK se il conteggio non è corretto.
Esegua prima SELECT COUNT(*) sulle righe da eliminare e ne verifichi la ragionevolezza.
Valuti la possibilità di creare una tabella di backup: CREATE TABLE users_bak AS SELECT * FROM users.
Confermi che le colonne di PARTITION BY definiscano davvero un duplicato, altrimenti potrebbe eliminare record distinti.

BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

Quasi duplicati e normalizzazione

A volte le righe non sono esattamente uguali, ma logicamente rappresentano lo stesso dato: 'Ann@X.com' rispetto a 'ann@x.com', oppure presentano spazi finali. Esegua il partitioning su un'espressione normalizzata anziché sulla colonna non elaborata.

Citare la normalizzazione dimostra esperienza: i duplicati del mondo reale spesso si nascondono dietro differenze di maiuscole e minuscole, spazi o formattazione che un semplice confronto tra chiavi non rileva.

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

Verifica rapida

Scelga l'approccio sicuro per la deduplicazione.

Riepilogo: deduplicazione sicura

Deduplichi in modo metodico:

Definisca innanzitutto che cosa sia un duplicato, quindi lo rilevi con GROUP BY / HAVING COUNT(*) > 1.
Duplicati esatti → DISTINCT. Duplicati per chiave → ROW_NUMBER partizionato per chiave, mantenendo rn = 1.
L'ORDER BY della finestra sceglie la copia canonica; aggiunga un criterio di spareggio univoco.
Elimini le righe con rn > 1 all'interno di una transazione, dopo aver verificato in anteprima il conteggio.
Normalizzi le chiavi per intercettare i quasi duplicati.

Domande Frequenti

La lezione «Deduplicare le righe in sicurezza» è gratuita?

Sì — il testo completo di «Deduplicare le righe in sicurezza» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Deduplicare le righe in sicurezza»?

Rimuovere righe duplicate esatte o quasi duplicate conservando un record canonico Eserciti Coding Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare Coding Interview Prep?

Non è richiesta alcuna esperienza precedente. Coding Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.

Quanto tempo richiede la lezione «Deduplicare le righe in sicurezza»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione Coding Interview Prep?

Sì. Ogni lezione Coding Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a Coding Interview Prep