В большой базе данных пользователей со следующим форматом и примерными данными мы пытаемся идентифицировать дублированных людей:
id first_name last_name email
---------------------------------------------------
1 chris baker
2 chris baker [email protected]
3 chris baker [email protected]
4 chris baker [email protected]
5 carl castle [email protected]
6 mike rotch [email protected]
Я использую следующий запрос:
SELECT
GROUP_CONCAT(id) AS "ids",
CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
COUNT(*) AS "duplicate_count"
FROM
users
GROUP BY
name
HAVING
duplicate_count > 1
Это отлично работает; Я получаю список дубликатов с номерами идентификаторов входящих строк.
Мы повторно назначили бы связанные данные, привязанные к дубликату, фактическому человеку (set user_id = 2 where user_id = 3
), а затем удалим повторяющуюся строку пользователя.
Проблема возникает после того, как мы сделаем этот отчет в первый раз, так как мы очищаем список после проверки вручную, что они действительно дубликаты - некоторые из них НЕ дублируются. Есть 2 Chris Bakers, которые являются законными пользователями.
Мы не хотим видеть Криса Бейкера в последующих дублированных отчетах до конца времени, поэтому я ищу способ указать, что идентификатор пользователя 1 и идентификатор пользователя 4 НЕ являются дубликатами друг друга для будущих отчетов, но они могут быть дублированы новыми пользователями, добавленными позже.
Что я пробовал
Я добавил в таблицу пользователя поле is_not_duplicate
, но если в базу данных будет добавлен новый дубликат "Chris Baker", это приведет к тому, что эта ситуация не будет отображаться в дублированном отчете; is_not_duplicate
неправильно исключает одну из учетных записей. Оператор HAVING
не будет соответствовать порогу > 1
, пока не появятся -дво-дубликаты Криса Бейкера, плюс "реальный", отмеченный is_not_duplicate
.
Вопрос суммирован
Как я могу создать исключения в указанном выше запросе без результатов цикла или нескольких запросов?
Подзапросы в порядке, но размер набора данных делает каждый подсчет количества запросов, и я хотел бы, чтобы решение было максимально эффективным.