MySQL / 14. Обработка повторяющихся записей

Подсчет и выявление дубликатов

Задача
Вы хотите узнать, содержит ли таблица дубликаты, и насколько их много. Или хотите увидеть записи, содержащие повторяющиеся значения.

Решение
Используйте счетчик (суммарный запрос), который ищет и выводит дубликаты. Чтобы увидеть записи, в которых встречаются дубликаты, объедините результат с исходной таблицей для вывода совпадающих записей.

Обсуждение
Предположим, что ваш веб-сайт содержит страницу регистрации, позволяющую пользователям добавить себя в список рассылки для получения периодических сообщений с каталогами товаров. Но вы забыли при создании таблицы включить в нее уникальный индекс, и теперь вы полагаете, что некоторые пользователи подписались на рассылку по несколько раз. Можетбыть, они забыли, что уже присутствуют в списке, а может быть кто-то решил добавить своих друзей, которые уже подписались сами. В любом случае благодаря наличию повторяющихся записей вы дважды отправите каталоги. Это дополнительные расходы для вас и неудобство для получателей.

В этом разделе рассказано о том, как определить, есть ли в таблице дубликаты, много ли их и как вывести повторяющиеся записи.

Чтобы определить, есть ли в таблице дубликаты, используйте функции получения итоговой информации. Методы получения итогов могут оказаться полезными при выявлении и подсчете дубликатов: группируем записи с помощью 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            |
+-----------+------------+------------------------+

Статьи по MySQL на эту тему:

Удаление дубликатов из таблицы
Устранение дубликатов из результата запроса
Устранение дубликатов из результата самообъединения