Задача
Вы хотите узнать, содержит ли таблица дубликаты, и насколько их много. Или хотите увидеть записи, содержащие повторяющиеся значения.
Решение
Используйте счетчик (суммарный запрос), который ищет и выводит дубликаты. Чтобы увидеть записи, в которых встречаются дубликаты, объедините результат с исходной таблицей для вывода совпадающих записей.
Обсуждение
Предположим, что ваш веб-сайт содержит страницу регистрации, позволяющую пользователям добавить себя в список рассылки для получения периодических сообщений с каталогами товаров. Но вы забыли при создании таблицы включить в нее уникальный индекс, и теперь вы полагаете, что некоторые пользователи подписались на рассылку по несколько раз. Можетбыть, они забыли, что уже присутствуют в списке, а может быть кто-то решил добавить своих друзей, которые уже подписались сами. В любом случае благодаря наличию повторяющихся записей вы дважды отправите каталоги. Это дополнительные расходы для вас и неудобство для получателей.
В этом разделе рассказано о том, как определить, есть ли в таблице дубликаты, много ли их и как вывести повторяющиеся записи.
Чтобы определить, есть ли в таблице дубликаты, используйте функции получения итоговой информации. Методы получения итогов могут оказаться полезными при выявлении и подсчете дубликатов: группируем записи с помощью GROUP BY и подсчитываем строки в каждой группе с помощью COUNT(). Например, предположим, что получатели каталога перечислены в таблице cat_mailing:
mysql> SELECT * FROM cat_mailing;
+-----------+--------------+-----------------------------+
| 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 |
+-----------+--------------+-----------------------------+
Предположим, что вы хотите выявить дубликаты для столбцов last_name и first_name, то есть получатели с одинаковыми именами принимаются за одного и того же человека (естественно, это упрощенная картина). Приведем запросы, часто используемые для описания таблицы и оценки существования и количества дубликатов:
• Общее количество строк таблицы:
mysql> SELECT COUNT(*) AS rows FROM cat_mailing;
+------+
| rows |
+------+
| 8 |
+------+
• Количество различных имен:
mysql> SELECT COUNT(DISTINCT last_name, first_name) AS 'distinct names'
-> FROM cat_mailing;
+----------------+
| distinct names |
+----------------+
| 5 |
+----------------+
• Количество строк, содержащих дубликаты:
mysql> SELECT COUNT(*) - COUNT(DISTINCT last_name, first_name)
-> AS 'duplicate names'
-> FROM cat_mailing;
+-----------------+
| duplicate names |
+-----------------+
| 3 |
+-----------------+
• Доля записей с уникальными и неуникальными значениями:
mysql> SELECT COUNT(DISTINCT last_name, first_name) / COUNT(*)
-> AS 'unique',
-> 1 - (COUNT(DISTINCT last_name, first_name) / COUNT(*))
-> AS 'non-unique'
-> FROM cat_mailing;
+--------+------------+
| unique | non-unique |
+--------+------------+
| 0.62 | 0.38 |
+--------+------------+
Эти запросы характеризуют меру распространения дубликатов в множестве, но не показывают, какие именно значения повторяются. Чтобы увидеть повторяющиеся записи таблицы cat_mailing, используйте суммарный запрос, выводящий неуникальные записи вместе с соответствующим счетчиком:
mysql> SELECT COUNT(*) AS repetitions, last_name, first_name
-> FROM cat_mailing
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1;
+------------+-----------+------------+
| repetitions | last_name | first_name |
+------------+-----------+------------+
| 3 | Baxter | Wallace |
| 2 | Pinter | Marlene |
+-----------+------------+------------+
Запрос содержит инструкцию HAVING, ограничивающую вывод только теми именами, которые встречаются несколько раз. (Если опустить эту инструкцию, то будут выведены и уникальные имена, которые нам не нужны, так как мы интересуемся только дубликатами.) Вообще, чтобы идентифицировать множество повторяющихся значений, сделайте следующее:
• Определите, какие столбцы содержат значения, которые могут повторяться.
• Перечислите эти столбцы в списке выбора, добавив COUNT(*).
• Перечислите эти столбцы и в инструкции GROUP BY.
• Добавьте инструкцию HAVING, удаляющую уникальные значения, требуя, чтобы возвращенные групповые счетчики были больше единицы.Построенные по этому плану запросы имеют такую форму:
SELECT COUNT(*), список_столбцов
FROM имя_таблицы
GROUP BY список_столбцов
HAVING COUNT(*) > 1
Можно без труда генерировать подобные запросы поиска дубликатов в программе для указанного имени таблицы и непустого множества имен столбцов. Рассмотрим, например, функцию на Perl make_dup_count_query(), которая формирует запрос для нахождения и подсчета дубликатов в указанных столбцах:
sub make_dup_count_query
{
my ($tbl_name, @col_name) = @_;
return (
"SELECT COUNT(*)," . join (",", @col_name)
. "\nFROM $tbl_name"
. "\nGROUP BY " . join (",", @col_name)
. "\nHAVING COUNT(*) > 1"
);
}
Функция make_dup_count_query() возвращает запрос в виде строки. Если вызвать ее так:
$str = make_dup_count_query ("cat_mailing", "last_name", "first_name");
то результирующим значением $str будет:
SELECT COUNT(*),last_name,first_name
FROM cat_mailing
GROUP BY last_name,first_name
HAVING COUNT(*) > 1
Теперь, когда строка запроса перед вами, можно выполнить запрос из создавшего строку сценария, передать другой программе или записать в файл для последующего выполнения. Каталог dups дистрибутива recipes содержит сценарий dup_count.pl, который можно использовать для опробования функции (там же можно найти и варианты на других языках).
Методы получения итогов позволяют оценить наличие дубликатов, их количество и вывести повторяющиеся значения. Но сам по себе суммарный запрос не может вывести полное содержимое записей, содержащих повторяющиеся значения. (Например, приведенные ранее суммарные запросы отображают счетчики повторяющихся имен из таблицы cat_mailing или сами имена, но не адреса, соответствующие этим именам.) Чтобы увидеть исходные записи, содержащие дубликаты, соедините итоговую информацию с исходной таблицей. Следующий пример показывает, как вывести записи таб-лицы cat_mailing, содержащие повторяющиеся имена. Итоговая информа-
ция записывается во временную таблицу, которая затем объединяется с таблицей cat_mailing для вывода записей, соответствующих этим именам:
mysql> CREATE TABLE tmp
-> SELECT COUNT(*) AS count, last_name, first_name
-> FROM cat_mailing GROUP BY last_name, first_name HAVING count > 1;
mysql> SELECT cat_mailing.*
-> FROM tmp, cat_mailing
-> WHERE tmp.last_name = cat_mailing.last_name
-> AND tmp.first_name = cat_mailing.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 |
+-----------+------------+------------------------+