Difference between revisions of "Fix duplicate artists"

From DHVLab

(Created page with "The [http://www.artigo.org ''ARTigo''] database was build ignoring duplicate artists, which is very bad for statistic results.<br/> To get an overview about the state, you can...")
 
Line 2: Line 2:
 
which is very bad for statistic results.<br/>
 
which is very bad for statistic results.<br/>
 
To get an overview about the state, you can create a new column containing the full name of the artists first.
 
To get an overview about the state, you can create a new column containing the full name of the artists first.
<syntaxhighlight lang="sql">ALTER TABLE artists ADD fullname VARCHAR(250) AFTER surname;</syntaxhighlight>
+
<syntaxhighlight lang="sql">
 +
ALTER TABLE artists  
 +
ADD fullname VARCHAR(250)  
 +
AFTER surname;
 +
</syntaxhighlight>
 
Now update the column as a concatenation of both forename and surname. As CONCAT returns an empty string if
 
Now update the column as a concatenation of both forename and surname. As CONCAT returns an empty string if
 
any field is empty, we replace empty values by '' using IFNULL.
 
any field is empty, we replace empty values by '' using IFNULL.
<syntaxhighlight lang="sql">UPDATE artists SET `fullname` = CONCAT(IFNULL(`firstname`,""), " ", IFNULL(`surname`,""))</syntaxhighlight>
+
<syntaxhighlight lang="sql">
Now we use MySQLs [http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex ''SOUNDEX''] function to generate
+
UPDATE artists  
 +
SET `fullname` = CONCAT(IFNULL(`firstname`,""), " ", IFNULL(`surname`,""))
 +
</syntaxhighlight>
 +
Let us check how many artists have the exact same name.
 +
<syntaxhighlight lang="sql">
 +
SELECT forename, surname, COUNT(*) c
 +
FROM artist
 +
GROUP BY fullname
 +
HAVING c > 1
 +
ORDER BY c desc;</syntaxhighlight>
 +
We get 393 results that need to be fixed. We now want to ignore slightly different writings.
 +
So we use MySQLs [http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex ''SOUNDEX''] function to generate
 
the [https://de.wikipedia.org/wiki/Soundex SOUNDEX representations] of the full names and store them in a different column.
 
the [https://de.wikipedia.org/wiki/Soundex SOUNDEX representations] of the full names and store them in a different column.
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
ALTER TABLE artists ADD soundex VARCHAR(35) AFTER fullname;
+
ALTER TABLE artists  
UPDATE artists SET `soundex` = SOUNDEX(fullname);
+
ADD soundex VARCHAR(35)  
 +
AFTER fullname;
 +
 
 +
UPDATE artists  
 +
SET `soundex` = SOUNDEX(fullname);
 
</syntaxhighlight>
 
</syntaxhighlight>
 
To get an overview we use
 
To get an overview we use
<syntaxhighlight lang="sql">SELECT forename, surname, COUNT(*) c FROM artist GROUP BY soundex HAVING c > 1 ORDER BY c desc;</syntaxhighlight>
+
<syntaxhighlight lang="sql">
 
+
SELECT forename, surname, COUNT(*) AS count
 +
FROM artist  
 +
GROUP BY soundex  
 +
HAVING count > 1  
 +
ORDER BY count desc;</syntaxhighlight>
 +
Which results in 1973 entries with duplicate artists with the same name, but different amounts of individual duplicate occurrences. Altogether around 25615 wrong, unnecessary entries. To clarify that further, there is only one entry needed without any name, not 17360 ones.
 +
<syntaxhighlight lang="sql">
 +
+--------------------------------+------------------------------------------------+-------+
 +
| forename                      | surname                                        | c    |
 +
+--------------------------------+------------------------------------------------+-------+
 +
|                                |                                                | 17360 |
 +
| NULL                          | Rembrandt Harmensz. van Rijn                  |  1379 |
 +
| NULL                          | Meissener Porzellan Manufaktur                |  1004 |
 +
| NULL                          | Manufactuur Oud-Loosdrecht                    |  241 |
 +
|                                | Unknown                                        |  236 |
 +
| NULL                          | Woodbury & Page                                |  200 |
 +
| NULL                          | Monogrammist CK (1590)                        |  142 |
 +
| NULL                          | Desguerrois & Co.                              |  108 |
 +
| NULL                          | Meester van Delft                              |    99 |
 +
.......
 +
</syntaxhighlight>
 
SELECT *, COUNT(*) c FROM artist GROUP BY soundex HAVING c > 1;
 
SELECT *, COUNT(*) c FROM artist GROUP BY soundex HAVING c > 1;

Revision as of 11:04, 20 May 2016

The ARTigo database was build ignoring duplicate artists, which is very bad for statistic results.
To get an overview about the state, you can create a new column containing the full name of the artists first.

ALTER TABLE artists 
ADD fullname VARCHAR(250) 
AFTER surname;

Now update the column as a concatenation of both forename and surname. As CONCAT returns an empty string if any field is empty, we replace empty values by using IFNULL.

UPDATE artists 
SET `fullname` = CONCAT(IFNULL(`firstname`,""), " ", IFNULL(`surname`,""))

Let us check how many artists have the exact same name.

SELECT forename, surname, COUNT(*) c 
FROM artist 
GROUP BY fullname 
HAVING c > 1 
ORDER BY c desc;

We get 393 results that need to be fixed. We now want to ignore slightly different writings. So we use MySQLs SOUNDEX function to generate the SOUNDEX representations of the full names and store them in a different column.

ALTER TABLE artists 
ADD soundex VARCHAR(35) 
AFTER fullname;

UPDATE artists 
SET `soundex` = SOUNDEX(fullname);

To get an overview we use

SELECT forename, surname, COUNT(*) AS count 
FROM artist 
GROUP BY soundex 
HAVING count > 1 
ORDER BY count desc;

Which results in 1973 entries with duplicate artists with the same name, but different amounts of individual duplicate occurrences. Altogether around 25615 wrong, unnecessary entries. To clarify that further, there is only one entry needed without any name, not 17360 ones.

+--------------------------------+------------------------------------------------+-------+
| forename                       | surname                                        | c     |
+--------------------------------+------------------------------------------------+-------+
|                                |                                                | 17360 |
| NULL                           | Rembrandt Harmensz. van Rijn                   |  1379 |
| NULL                           | Meissener Porzellan Manufaktur                 |  1004 |
| NULL                           | Manufactuur Oud-Loosdrecht                     |   241 |
|                                | Unknown                                        |   236 |
| NULL                           | Woodbury & Page                                |   200 |
| NULL                           | Monogrammist CK (1590)                         |   142 |
| NULL                           | Desguerrois & Co.                              |   108 |
| NULL                           | Meester van Delft                              |    99 |
.......

SELECT *, COUNT(*) c FROM artist GROUP BY soundex HAVING c > 1;