Recipe 14.3. Counting and Identifying Duplicates


Problem

You want to determine whether a table contains duplicates, and to what extent they occur. Or you want to see the rows that contain the duplicated values.

Solution

Use a counting summary that looks for and displays duplicated values. To see the rows in which the duplicated values occur, join the summary to the original table to display the matching rows.

Discussion

Suppose that your web site includes a sign-up page that enables visitors to add themselves to your mailing list to receive periodic product catalog mailings. But you forgot to include a unique index in the table when you created it, and now you suspect that some people are signed up multiple times. Perhaps they forgot they were already on the list, or perhaps people added friends to the list who were already signed up. Either way, the result of having duplicate rows is that you mail out duplicate catalogs. This is an additional expense to you, and it annoys the recipients. This section discusses how to find out if duplicates are present in a table, how prevalent they are, and how to display the duplicated rows. (For tables that do contain duplicates, Section 14.4 describes how to eliminate them.)

To determine whether duplicates occur in a table, use a counting summary (a topic covered in Chapter 8). Summary techniques can be applied to identifying and counting duplicates by grouping rows with GROUP BY and counting the rows in each group using COUNT⁠(⁠ ⁠ ⁠). For the examples here, assume that catalog recipients are listed in a table named catalog_list that has the following contents:

mysql> SELECT * FROM catalog_list; +-----------+-------------+--------------------------+ | last_name | first_name  | street                   | +-----------+-------------+--------------------------+ | Isaacson  | Jim         | 515 Fordam St., Apt. 917 | | Baxter    | Wallace     | 57 3rd Ave.              | | McTavish  | Taylor      | 432 River Run            | | Pinter    | Marlene     | 9 Sunset Trail           | | BAXTER    | WALLACE     | 57 3rd Ave.              | | Brown     | Bartholomew | 432 River Run            | | Pinter    | Marlene     | 9 Sunset Trail           | | Baxter    | Wallace     | 57 3rd Ave., Apt 102     | +-----------+-------------+--------------------------+ 

Suppose that you want to define "duplicate" using the last_name and first_name columns. That is, recipients with the same name are assumed to be the same person. The following statements are typical of those used to characterize the table and to assess the existence and extent of duplicate values:

  • The total number of rows in the table:

    mysql> SELECT COUNT(*) AS rows FROM catalog_list; +------+ | rows | +------+ |    8 | +------+ 

  • The number of distinct names:

    mysql> SELECT COUNT(DISTINCT last_name, first_name) AS 'distinct names'     -> FROM catalog_list; +----------------+ | distinct names | +----------------+ |              5 | +----------------+ 

  • The number of rows containing duplicated names:

    mysql> SELECT COUNT(*) - COUNT(DISTINCT last_name, first_name)     ->   AS 'duplicate names'     -> FROM catalog_list; +-----------------+ | duplicate names | +-----------------+ |               3 | +-----------------+ 

  • The fraction of the rows that contain unique or nonunique names:

    mysql> SELECT COUNT(DISTINCT last_name, first_name) / COUNT(*)     ->   AS 'unique',     -> 1 - (COUNT(DISTINCT last_name, first_name) / COUNT(*))     ->   AS 'nonunique'     -> FROM catalog_list; +--------+------------+ | unique | nonunique  | +--------+------------+ | 0.6250 |     0.3750 | +--------+------------+ 

These statements help you characterize the extent of duplicates, but they don't show you which values are duplicated. To see the duplicated names in the catalog_list table, use a summary statement that displays the nonunique values along with the counts:

mysql> SELECT COUNT(*) AS repetitions, last_name, first_name     -> FROM catalog_list     -> GROUP BY last_name, first_name     -> HAVING repetitions > 1; +-------------+-----------+------------+ | repetitions | last_name | first_name | +-------------+-----------+------------+ |           3 | Baxter    | Wallace    | |           2 | Pinter    | Marlene    | +-------------+-----------+------------+ 

The statement includes a HAVING clause that restricts the output to include only those names that occur more than once. (If you omit the clause, the summary lists unique names as well, which is useless when you're interested only in duplicates.) In general, to identify sets of values that are duplicated, do the following:

  1. Determine which columns contain the values that may be duplicated.

  2. List those columns in the column selection list, along with COUNT(*).

  3. List the columns in the GROUP BY clause as well.

  4. Add a HAVING clause that eliminates unique values by requiring group counts to be greater than one.

Queries constructed that way have the following form:

SELECT COUNT(*), column_list FROM tbl_name GROUP BY column_list HAVING COUNT(*) > 1 

It's easy to generate duplicate-finding queries like that within a program, given database and table names and a nonempty set of column names. For example, here is a Perl function make_dup_count_query⁠(⁠ ⁠ ⁠) that generates the proper query for finding and counting duplicated values in the specified columns:

sub make_dup_count_query { my ($db_name, $tbl_name, @col_name) = @_;   return (         "SELECT COUNT(*)," . join (",", @col_name)       . "\nFROM $db_name.$tbl_name"       . "\nGROUP BY " . join (",", @col_name)       . "\nHAVING COUNT(*) > 1"   ); } 

make_dup_count_query⁠(⁠ ⁠ ⁠) returns the query as a string. If you invoke it like this:

$str = make_dup_count_query ("cookbook", "catalog_list",                              "last_name", "first_name"); 

the resulting value of $str is:

SELECT COUNT(*),last_name,first_name FROM cookbook.catalog_list GROUP BY last_name,first_name HAVING COUNT(*) > 1 

What you do with the query string is up to you. You can execute it from within the script that creates it, pass it to another program, or write it to a file for execution later. The dups directory of the recipes distribution contains a script named dup_count.pl that you can use to try the function (as well as some translations into other languages). Section 14.4 discusses use of the make_dup_count_query⁠(⁠ ⁠ ⁠) function to implement a duplicate-removal technique.

Summary techniques are useful for assessing the existence of duplicates, how often they occur, and displaying which values are duplicated. But if duplicates are determined using only a subset of a table's columns, a summary in itself cannot display the entire content of the rows that contain the duplicate values. (For example, the summaries shown thus far display counts of duplicated names in the catalog_list table or the names themselves, but don't show the addresses associated with those names.) To see the original rows containing the duplicate names, join the summary information to the table from which it's generated. The following example shows how to do this to display the catalog_list rows that contain duplicated names. The summary is written to a temporary table, which then is joined to the catalog_list table to produce the rows that match those names:

mysql> CREATE TABLE tmp     -> SELECT COUNT(*) AS count, last_name, first_name FROM catalog_list     -> GROUP BY last_name, first_name HAVING count > 1; mysql> SELECT catalog_list.*     -> FROM tmp INNER JOIN catalog_list USING(last_name, first_name)     -> ORDER BY last_name, first_name; +-----------+------------+----------------------+ | last_name | first_name | street               | +-----------+------------+----------------------+ | Baxter    | Wallace    | 57 3rd Ave.          | | BAXTER    | WALLACE    | 57 3rd Ave.          | | Baxter    | Wallace    | 57 3rd Ave., Apt 102 | | Pinter    | Marlene    | 9 Sunset Trail       | | Pinter    | Marlene    | 9 Sunset Trail       | +-----------+------------+----------------------+ 

Duplicate Identification and String Case Sensitivity

For strings that have a case-insensitive collation, values that differ only in lettercase are considered the same for comparison purposes. To treat them as distinct values, compare them using a case-sensitive or binary collation. Section 5.9 shows how to do this.





MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net