Підрахунок і виявлення дублікатів

Попередня ↔ Наступна

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

Рішення
Використовуйте лічильник (сумарний запит), який шукає і виводить дублікати. Щоб побачити записи, в яких зустрічаються дублікати, об'єднайте результат з вихідною таблицею для виведення співпадаючих записів.

Обговорення
Припустимо, що ваш веб-сайт містить сторінку реєстрації, що дозволяє користувачам додати себе в список розсилки для отримання періодичних повідомлень з каталогами товарів. Але ви забули при створенні таблиці внести в неї унікальний індекс, і тепер ви вважаєте, що деякі користувачі підписалися на розсилку по кілька разів. Можетбить, вони забули, що вже присутні в списку, а може бути хтось вирішив додати своїх друзів, які вже підписалися самі. У будь-якому випадку завдяки наявності повторюваних записів ви двічі відправите каталоги. Це додаткові витрати для вас і незручність для одержувачів.

У цьому розділі розказано про те, як визначити, чи є в таблиці дублікати, чи багато їх і як вивести повторювані записи.

Щоб визначити, чи є в таблиці дублікати, використовуйте функції отримання підсумкової інформації.

Методи отримання підсумків можуть виявитися корисними при виявленні і підрахунку дублікатів: групуємо записи за допомогою GROUP BY і підраховуємо рядки в кожній групі за допомогою COUNT (). Наприклад, припустимо, що одержувачі каталогу перераховані в таблиці cat_mailing:

mysql> SELECT * FROM cat_mailing;

Припустимо, що ви хочете виявити дублікати для стовпців last_name і first_name, тобто одержувачі з однаковими іменами приймаються за одного і того ж людини (природно, це спрощена картина).

Наведемо запити, часто використовувані для опису таблиці і оцінки існування і кількості дублікатів:

• Загальна кількість рядків таблиці:

mysql> SELECT COUNT (*) AS rows FROM cat_mailing;

• Кількість різних імен:

mysql> SELECT COUNT (DISTINCT last_name, first_name) AS 'distinct names'
-> FROM cat_mailing;

• Кількість рядків, що містять дублікати:

mysql> SELECT COUNT (*) - COUNT (DISTINCT last_name, first_name)
-> AS 'duplicate names'
-> FROM cat_mailing;

• Частка записів з унікальними і неунікальні значеннями:

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;

Ці запити характеризують міру поширення дублікатів в безлічі, але не показують, які саме значення повторюються.

Щоб побачити повторювані записи таблиці cat_mailing, використовуйте сумарний запит, що виводить неунікальні записи разом з відповідним лічильником:

mysql> SELECT COUNT (*) AS repetitions, last_name, first_name
-> FROM cat_mailing
-> GROUP BY last_name, first_name
-> HAVING repetitions> 1;

Запит містить інструкцію 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, який можна використовувати для випробування функції (там же можна знайти і варіанти на інших мовах).

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;