Trovare ed eliminare duplicati

La duplicazione di dati non dovrebbe mai avvenire in un database bene organizzato, ma talvolta, quando si ha a che fare con dati ereditati da applicazioni poco accurate, può accadere di ritrovarsi con una tabella che contiene record duplicati.

In questa poco piacevole situazione, ci sono tre tipi di operazioni che si vorrebbero eseguire:

  • Elencare i record senza duplicati;
  • Elencare i valori duplicati;
  • Eliminare i duplicati.

Prendiamo ad esempio una tabella con qualche record duplicato (per esigenze di spazio, la facciamo corta, ma supponete di avere svariate migliaia di record).


mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 5 | aaa | xxx |
| 6 | ddd | zzz |
| 7 | aaa | xxx |
| 8 | bbb | yyy |
| 9 | ccc | www |
+---+------+-----+
9 rows in set (0.18 sec)

La prima esigenza, vedere i record senza doppi, viene risolta facilmente usando la clausola DISTINCT:


mysql> SELECT DISTINCT a, b FROM condoppi;
+------+-----+
| a | b |
+------+-----+
| aaa | xxx |
| aaa | yyy |
| bbb | yyy |
| ccc | www |
| ddd | zzz |
+------+-----+
5 rows in set (0.12 sec)

Facilissimo. Basta premettere DISTINCT alla lista dei campi per avere un elenco pulito, senza duplicati.

Notate che se avessi inserito fra i campi anche la chiave primaria (campo “i”), il DBMS mi avrebbe stampato tutti i record, perché ovviamente sono differenziati grazie al campo univoco. La clausola DISTINCT è anche utile per avere un elenco dei valori distinti (appunto) per una data colonna. Per la seconda esigenza, vedere quali valori sono doppiati, non c’è formula magica immediata, ma bisogna sforzarsi un po’ di più.

Il metodo consiste nel raggruppare i valori con un conteggio, e considerare solo quelli maggiori di 1. Si noti la clausola HAVING che (a differenza di WHERE) viene valutata dopo l’operazone di raggruppamento.


mysql> SELECT a, b COUNT(*) AS quanti
FROM condoppi
GROUP BY a, b
HAVING quanti > 1;
+------+-----+--------+
| a | b | quanti |
+------+-----+--------+
| aaa | xxx | 2 |
| bbb | yyy | 2 |
| ccc | www | 2 |
+------+-----+--------+
3 rows in set (0.39 sec)

Così possiamo vedere quali sono i valori (o le combinazioni di valori) che sono registrati più di una volta.

Infine, se vogliamo cancellare dalla tabella i valori doppi, esiste un metodo molto semplice e pratico: aggiungere un indice univoco alla tabella, ma usando la clausola IGNORE, per far sì che il DBMS non si fermi al primo errore di record duplicato, ma vada avanti, di fatto saltando l’inserimento dei record con valori già inseriti.


mysql> ALTER IGNORE TABLE condoppi ADD UNIQUE KEY (a, b) ;
Query OK, 9 rows affected (0.54 sec)
Records: 9 Duplicati: 4 Avvertimenti: 0
mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 6 | ddd | zzz |
+---+------+-----+
5 rows in set (0.02 sec)

Aggiornare più condizioni con una sola query

PW scrive che vorrebbe trovare un modo di eseguire diversi UPDATE su una tabella usando una sola query. Nella pratica, si trova ad avere record di prodotti di diverse aziende, che vuole aggiornare in maniera omogenea, ma senza dover ripetere la stessa query diverse volte.

Per esempio, vorrebbe cambiare lo sconto offerta sui libri di informatica, di finanze, di attualità, con quantità differenti per ogni categoria. Una possibilità è la seguente:

UPDATE libri
SET sconto =
CASE
WHEN sezione = 'informatica' THEN 12
WHEN sezione = 'attualita' THEN 10
WHEN sezione = 'finanze' THEN 8
ELSE sconto
END
WHERE id_editore IN (5,6,8,11);

Questa query agisce solo sui libri degli editori considerati (clausola WHERE) e al loro interno modifica il campo sconto solo nelle sezioni indicate. Per le altre sezioni, il valore viene lasciato uguale, grazie alla clausola ELSE della funzione CASE.

Comporre query di questo tipo può essere più difficile che comporre una query per ogni condizione, ma talora si guadagna in efficienza, ed è bene sapere che ci sono alternative!

Lista record con numero progressivo

Questa richiesta è arrivata su un forum di programmazione: Come faccio a elencare i risultati di una query con un numero progressivo per ogni record?

Ci sono diverse risposte a questa domanda. La più immediata sarebbe di implementare il conteggio nell’ambito dell’applicazione che riceve i dati. Questa soluzione, però, richiede dettagli diversi in ogni linguaggio e potrebbe non essere quel che si cerca.

Invece, esiste un modo per far contare i record a MySQL, in modo da avere un risultato che sia lo stesso, a prescindere dal linguaggio usato.

Forse non tutti sanno che MySQL consente di usare delle variabili “globali” che sono attive per tutta la durata di una connessione. Per esempio, un uso frequente di questa funzionalità si ha per ovviare alla mancanza di subquery nelle versioni inferiori alla 4.1:


mysql> SELECT @massimo := max(stipendio) from paghe;
mysql> SELECT nome, cognome, stipendio from paghe where stipendio = @massimo;

In maniera simile, possiamo usare una variabile per creare un contatore. Poiché ogni variabile ha valore NULL finché non le viene assegnato esplicitamente qualcosa, è necessario eseguire due query per poter raggiungere lo scopo. Prima, vediamo la query senza contatore.


mysql> SELECT nome,stipendio, genere FROM persone;
+--------+-----------+--------+
| nome | stipendio | genere |
+--------+-----------+--------+
| John | 5000 | m |
| Mario | 6000 | m |
| Frank | 5000 | m |
| Otto | 6000 | m |
| Susan | 5500 | f |
| Martin | 5500 | m |
| Mary | 5500 | f |
| Bill | 5000 | m |
| June | 6000 | f |
+--------+-----------+--------+
9 rows in set (0.01 sec)

Poi, eseguiamo le due query. Innanzitutto inizializziamo il contatore.


mysql> set @N = 0;
Query OK, 0 rows affected (0.27 sec)

Infine, eseguiamo la query includendo il contatore con una formula per incrementarlo.


mysql> SELECT @N := @N +1 AS numero, nome,stipendio,genere FROM persone;
+--------+--------+-----------+--------+
| numero | nome | stipendio | genere |
+--------+--------+-----------+--------+
| 1 | John | 5000 | m |
| 2 | Mario | 6000 | m |
| 3 | Frank | 5000 | m |
| 4 | Otto | 6000 | m |
| 5 | Susan | 5500 | f |
| 6 | Martin | 5500 | m |
| 7 | Mary | 5500 | f |
| 8 | Bill | 5000 | m |
| 9 | June | 6000 | f |
+--------+--------+-----------+--------+
9 rows in set (0.01 sec)

Notate che ogni variabile in una query può essere calcolata una volta sola per ogni record.

Infine, se adottate una soluzione simile, ricordatevi di inizializzare il contatore alla query successiva, altrimenti i vostri record avranno un conteggio in continua crescita!