Дедупликация записей базы данных, сравнивая значения в многочисленных полях

Поэтому я пытаюсь очистить некоторые записи телефона в таблице базы данных.

Я узнал, как найти точные совпадения в 2 полях, используя:

/* DUPLICATE first & last names */

SELECT 
    `First Name`, 
    `Last Name`, 
     COUNT(*) c 
FROM phone.contacts  
GROUP BY 
    `Last Name`, 
    `First Name` 
HAVING c > 1;

Вау, отлично.

Я хочу расширить его, чтобы посмотреть на многочисленные поля, чтобы узнать, является ли номер телефона в 1 из 3 телефонных полей дубликатом.

Поэтому я хочу проверить 3 поля (general mobile, general phone, business phone).

1. чтобы увидеть, что они не пусты ('') 2. чтобы увидеть, появляются ли данные (номер) в любом из них в двух других телефонных полях в любом месте таблицы.

Поэтому, когда мой ограниченный SQL превысил свой предел, я пришел к следующему, которое, похоже, возвращает записи с 3 пустыми телефонными полями, а также записи, которые не имеют повторяющихся телефонных номеров.

/* DUPLICATE general & business phone nos */

SELECT 
    id, 
   `first name`, 
   `last name`, 
   `general mobile`, 
   `general phone`, 
   `general email`, 
   `business phone`, 
    COUNT(CASE WHEN `general mobile` <> '' THEN 1 ELSE NULL END) as gen_mob, 
    COUNT(CASE WHEN `general phone` <> '' THEN 1 ELSE NULL END) as gen_phone,
    COUNT(CASE WHEN `business phone` <> '' THEN 1 ELSE NULL END) as bus_phone 
FROM phone.contacts 
GROUP BY 
   `general mobile`, 
   `general phone`, 
   `business phone` 
HAVING gen_mob > 1 OR gen_phone > 1 OR bus_phone > 1;

Ясно, что моя логика ошибочна, и я подумал, может ли кто-нибудь указать мне правильное направление / сжалиться и т.д....

Большое спасибо

Источник

3 ответа

Решение

Первое, что вы должны сделать, застрелить человека, который назвал ваши столбцы с пробелами в них.

Теперь попробуйте это:

SELECT DISTINCT
   c.id, 
   c.`first name`, 
   c.`last name`, 
   c.`general mobile`, 
   c.`general phone`, 
   c.`business phone`
from contacts_test c
join contacts_test c2
    on (c.`general mobile`!= '' and c.`general mobile` in (c2.`general phone`, c2.`business phone`))
    or (c.`general phone` != '' and c.`general phone` in (c2.`general mobile`, c2.`business phone`))
    or (c.`business phone`!= '' and c.`business phone` in (c2.`general mobile`, c2.`general phone`))

Смотрите живую демонстрацию этого запроса в SQLFiddle.

Обратите внимание на дополнительную проверку для phone != '', что необходимо, потому что номера телефонов не могут быть обнулены, поэтому их "неизвестное" значение пусто. Без этой проверки возвращаются ложные совпадения, потому что пустое значение равно пустому.

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

Источник

По моему опыту, при очистке данных гораздо лучше иметь понятное представление данных и простой способ управления ими, чем иметь большой и громоздкий запрос, который выполняет весь анализ сразу.

Вы также можете (более или менее) перенормировать базу данных, используя что-то вроде:

Create view VContactsWithPhones
as
Select id, 
       `Last Name` as LastName, 
       `First Name` as FirstName,
       `General Mobile` as Phone,
       'General Mobile' as PhoneType
From phone.contacts c
UNION
Select id, 
       `Last Name`, 
       `First Name`,
       `General Phone`,
       'General Phone'
From phone.contacts c
UNION
Select id, 
       `Last Name`, 
       `First Name`,
       `Business Phone`,
       'Business Phone'
From phone.contacts c

Это создаст представление с тройными строками исходной таблицы, но с Phone столбец, который может быть одного из трех типов.

Вы можете легко выбрать из этого представления:

//empty phones
SELECT * 
FROM VContactsWithPhones 
Where Phone is null or Phone = ''

//duplicate phones
Select Phone, Count(*)
from VContactsWithPhones 
where (Phone is not null and Phone <> '')  -- exclude empty values
group by Phone
having count(*) > 1

//duplicate phones belonging to the same ID (double entries)
Select Phone, ID, Count(*)
from VContactsWithPhones 
where (Phone is not null and Phone <> '')  -- exclude empty values
group by Phone, ID
having count(*) > 1

//duplicate phones belonging to the different ID (duplicate entries)
Select v1.Phone, v1.ID, v1.PhoneType, v2.ID, v2.PhoneType
from VContactsWithPhones v1
   inner join VContactsWithPhones v2 
     on v1.Phone=v2.Phone and v1.ID=v2.ID
where v1.Phone is not null and v1.Phone <> ''

и т. д.

Источник

Вы можете попробовать что-то вроде:

SELECT * from phone.contacts p WHERE `general mobile` IN (SELECT `general mobile` FROM phone.contacts WHERE id != p.id UNION SELECT `general phone` FROM phone.contacts WHERE id != p.id UNION SELECT `general email` FROM phone.contacts WHERE id != p.id)

Повторите 3 раза для каждого: general mobile, general phone а также general email, Он может быть помещен в один запрос, но будет менее читабельным.

Источник
Другие вопросы по тегам