Chapter 5: Data Quality Part 2

Duplicates

Part of Database · page 5 of 5

Duplicates

Duplicates = Two or more rows that describe the same real thing. The same person entered twice for example.

"The same thing" is the decision we make ourselves, and we have to state it. What does "Same person" mean? Same name and same date of birth? Same email? The data cannot tell us which definition is right - it can only tell us how many duplicates each definition produces.

Step 1: Which values repeat

Groups that appear more than once:

SELECT first_name, last_name, date_of_birth, COUNT(*) AS n
FROM tutorial_library.member
GROUP BY first_name, last_name, date_of_birth,
HAVING COUNT (*) > 1
ORDER BY n DESC;

GROUP BY collapses rows that share the same values into one output row per group, so we can count or total each group.

WHERE vs HAVING: WHERE filters rows before grouping. HAVING filters groups after grouping. The duplicate test has to be in HAVING because the count doesn't exist until the grouping has happened.

Step 2: Get the actual rows back

Grouping tells us a duplicate exists but throws away the individual rows. A window function gets both in one pass:

SELECT * 
FROM (
  SELECT m.*,
  COUNT(*) OVER (PARTITION BY first_name, last_name, date_of_birth) AS group_size,
  ROW_NUMBER() OVER (PARTITION BY first_name, last_name, date_of_birth ORDER BY member_id) AS copy_no
FROM tutorial_library.member m
) t
WHERE group_size > 1
ORDER BY last_name, first_name, copy_no;

OVER (PARTITION BY...) means "Do this calculation separately within each group, and keep every row in the output." That last part is the difference from GROUP BY, which collapses each group into one row.

ROW_NUMBER() Numbers the rows within each group: 1,2,3. Combined with ORDER BY member_id, row 1 is the earliest record. Rows numbered 2 and above are the extra copies.

Step 3: Decide what counts as "The Same"

Exact matching misses most duplicates. JOHN SMITH and John Smith and john smith ar ethe same person and different strings. Clean up the values before comparison.

SELECT LOWER(TRIM(first_name)) AS f,
       LOWER(TRIM(last_name)) AS l,
       date_of_birth, 
       COUNT(*) AS n,
       GROUP_CONCAT(member_id ORDER BY member_id) AS ids
FROM tutorial_library.member
GROUP BY f, l, date_of_birth
HAVING COUNT(*) > 1
ORDER BY n DESC;

LOWER() makes everything lower case so cases stop mattering. GROUP CONCAT() returns the member IDs in each group as a comma-separated list, so we can quote exact row identifiers without running a second query.