Как удалить дубликаты в mysql с учетом значения по полю?

Question

[Сергей]

Добрый день!
Есть таблица по сотрудникам в базе данных:
Id, FIO, Phone
Типа такой:
1, Иванов Иван Иванович, 9999999999
2, Иванов Иван Иванович,
3, Петров Петр Петрович,
4, Петров Петр Петрович,
5, Сидоров Сидор Сидорович, 7777777777
6, Сидоров Сидор Сидорович, 7777777777
Внезапно оказалось, что многие ФИО задублированы. При этом у одних записей есть контактный телефон, у других нет.
Мне нужно найти и удалить все дубли (знаю как). Но при этом должна учитываться логика:
1. если у одной записи есть номер телефона, то удаляем тот дубль, у которого нет телефона.
2. Если телефонов нет ни у одной записи, то удаляем любую.
3. Если телефоны есть у обоих записей, то удаляем любую.

Answer 1

[Владимир Коротенко]

DELETE FROM phones
 WHERE Id NOT IN
(
 SELECT MIN(Id)
FROM phones
	where Phone <> ''
    GROUP BY FIO 
 );

Comments

[Сергей]

У некоторых дублирующихся записей тоже есть телефоны. Т.е.:
5, Сидоров Сидор Сидорович, 7777777777
6, Сидоров Сидор Сидорович, 7777777777

У других пар нет телефона ни у основной, ни у дубля. Т.е.:
3, Петров Петр Петрович,
4, Петров Петр Петрович,

Получится, что в одном случае не удалится ничего, в другом - удалится все

[Владимир Коротенко]

Прогоните 2 раза, первый без условия, а второй с условием.
В общем то можно написать и обработчик на любом языке, все же sql немного для другого.
Кстати почему приложение не обрабатывает дубли само?

[Сергей]

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

[Antonio Solo]

Владимир Коротенко, не всегда дубли -это "дубли"

например если идут заявки, то в течении 15 минут от одного человека - это дубль, а если с разницей в 12 часов - то это совсем не дубли.

[Владимир Коротенко]

Antonio Solo, Почему вы сделали такое предположение?
По всем правилам нормализации фио это первичный ключ (в первом приближении), телефон это атрибут этой записи.

А таблица callhistory содержит только

callid, userid, starttime, endtime, operatorid, --- что то еще

А еще есть CallHistoryView в котором джойнами и создается сводная таблица для морды.

[Antonio Solo]

Владимир Коротенко, это вы делаете предположение (непонятно на чем основываясь) что что-то является ключом.
говорить о нормализации БД можно только когда вы ясно представляете физический смысл данных и их физические взаимосвязи. в конкретном случае можно только говорить об абстрактном наборе данных.

Answer 2

[xmoonlight]

Изначально, структура БД - некорректна.
Нужно, чтобы к одному ФИО можно было присвоить несколько контактов, различимых по типу.
Один из них - номер телефона.
И проблемы бы не было.
Переделайте структуру, если ещё не поздно.

Comments

[Сергей]

Структура БД корректна. Чтобы это понять придется разбирать структуру всей БД и взаимосвязей таблиц. Чтобы этого не делать, прошу помочь с одной атомарной задачей.
Приведенная структура таблицы приведена для примера. Реальная структура сложнее, под нее потом просто переделаю запрос.

[xmoonlight]

sergey_privacy, Некорректна, т.к. у Вас возникла данная задача.
Иначе бы - она не возникала. Хотите поспорить?))

[Сергей]

Я хочу задачу решить, а не сваливаться в схоластические споры с троллями. Знаешь - подскажи, как решить данную атомарную задачу. Не знаешь - не забивай ветку бесполезными комментариями. Только время на них теряю.

[xmoonlight]

sergey_privacy, твоя задача также бесполезна, как и ты сам.
Подумай над этим.
Всех благ.

[Antonio Solo]

xmoonlight, неправ ты. ничего некорректного тут нет.

у меня практически такая же задача есть - фиксация лидов в системе со 100500 сайтов. один и тот же заказчик может отметиться на 10 сайтах, оставить там заявки на звонки, пообщаться с ботами в чатах, а еще и позвонить на 2 АТС. все действия сыплются в таблицу - лиды. соответственно также встает задача чистки (в моем случае - склейки) дубликатов. и уже менеджер на основании обработки этого пакета создает заказчика (либо цепляет новый контакт к уже существующему).

[xmoonlight]

Antonio Solo, ну вот: обработка у тебя ДО и раскладывается всё так, как нужно перед помещением в базу.
А у автора - уже сразу трэш в базе из-за несоблюдения ДНФ3.

Answer 3

[mletov]

-- Пишем телефоны в пустые значения
UPDATE table AS t1
SET t1.Phone=t2.Phone
FROM table AS t1
INNER JOIN table AS t2
ON t1.FIO=t2.FIO
AND t1.phone IS NULL
AND t2.phone IS NOT NULL 

-- Удаляем лишнее
DELETE 
FROM table
WHERE Id NOT IN
(
	SELECT Id
	FROM
	(
		SELECT MAX(Id) AS id, FIO, Phone
		FROM table
		GROUP BY FIO,
				   Phone
	) AS t1
)

PS Отталкиваемся от допущения, что не может быть людей с одинаковыми ФИО
PPS Можно еще упростить, убрав одну обертку

Answer 4

[d-stream]

На самом деле вот так вот "в лоб" удалять дублей, ибо не исключено что по ним уже были в системе какие-либо дейсвия. Соответственно удаления должно предшествовать "слияние" в виде замены в отражающих какие-либо операции id удаляемого контрагента на id с кем он сливается.
Ну и само выявление дублей - я бы не стал вот так однозначно отдавать это скрипту. Потому что действительно Васи́лий Алибаба́евич Али-Баба́ в двух записях - скорее всего дубль, а вот Кузнецов Иван Сергеевич - ну совсем не факт.

Но если базу не жалко... то можно просто вначале отобрать фио-клоны (group by fio having count(*) > 1) и потом их использовать для отбора данных для удаления задав обратный ордеринг в соответствии с критериями "информативности" (например наличие и длина телефона, дата создания и т.п.) и отобрав оттуда top 1 (limit 1)
[правда в таком раскладе из троиц будут удалены только по одному... но можно вывернуть запрос мехом внутрь и использовать его как инверсию - не удалять top 1]

Answer 5

[Antonio Solo]

Собственно задача раскладывется на 2:
1) выбрать "основные уникальные" записи
2) удалить записи, не попадающие в множество "основных уникальных"

2-я задача тривиальна

1-я задача довольно просто решается в МySQL 8 при помощи "оконных функций"
https://dev.mysql.com/doc/refman/8.0/en/window-fun...

в mySQL более ранних версий придется поизвращаться:

1) сделать уникальную выборку имен, телефонов имеющих телефон
2) сделать уникальную выборку имен не имеющих телефона и не попадающих в первую выборку
3) объединить их

вот пример такого запроса

SELECT DISTINCT surname, name_ FROM `sdtm_users_items` WHERE (patro='' OR patro IS NULL) AND (surname, name_) NOT IN (SELECT DISTINCT surname, name_ FROM `sdtm_users_items` WHERE patro<>'' AND patro IS NOT NULL) UNION SELECT DISTINCT surname, name_ FROM `sdtm_users_items` WHERE patro<>'' AND patro IS NOT NULL ORDER BY surname, name_