Поиск и работа с дублирующимися пользователями

Ответ 1

Попробуйте добавить логическое поле is_not_duplicate и изменить код следующим образом:

SELECT 
    GROUP_CONCAT(id) AS "ids",
    CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
    COUNT(*) AS "duplicate_count",
    SUM(is_not_duplicate) AS "real_count"
FROM 
    users 
GROUP BY 
    name 
HAVING 
    duplicate_count > 1
AND
    duplicate_count - real_count > 0

Добавленные дубликаты будут иметь is_not_duplicate=0, поэтому real_count для этого имени будет меньше duplicate_count, и строка будет показана

Ответ 2

Мой мозг слишком обжарен, чтобы придумать фактический запрос для этого на данный момент, но я мог бы дать вам толчок в пути, который должен работать:)

Что делать, если вы добавили еще один столбец (может быть, таблица действительных дублированных пользователей вместо этого?... оба будут выполнять одно и то же) и выполнили подзапрос, который будет подсчитывать все допустимые дубликаты, а затем вы можете сравнить с счет в текущем запросе. Вы исключили бы всех пользователей, у которых есть соответствующие счетчики, и будут тянуть любые с более высокими значениями. Надеюсь, это имеет смысл; Я создам прецедент:

• Крис Бейкер с идентификаторами 1 и 4 помечены как valid_duplicates
• В системе есть 4 Chris Baker
• Вы получаете счет действительного Криса Бейкера
• Вы получаете кол-во всех вещей Криса Бейкера
• valid_count < > total_count, поэтому верните Chris Baker

* Возможно, вы даже можете изменить запрос таким образом, чтобы он даже не отображал дублированный идентификатор (даже если вы получили дублирующее обозначение всего 1 id). Вместо того, чтобы перепроверять, какие именно валиды. Это было бы немного сложнее. Без этого, по крайней мере, вы игнорируете Криса Бейкера, пока другой не войдет в систему.

Я написал основной запрос, касающийся исключения конкретного идентификатора, который я попытаюсь выполнить сегодня вечером. Но это, по крайней мере, решает вашу первоначальную потребность. Если вам не нужен более сложный запрос, сообщите мне, чтобы я не тратил время на это:)

SELECT 
    GROUP_CONCAT(id) AS "ids",
    CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
    COUNT(*) AS "duplicate_count" 
FROM 
    users 
WHERE NOT EXISTS
    (
        SELECT 1 
        FROM
        (
            SELECT 
                CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
                COUNT(*) AS "valid_duplicate_count" 
            FROM 
                users
            WHERE 
                is_valid_duplicate = 1 --true
            GROUP BY 
               name 
            HAVING 
               valid_duplicate_count > 1 
        ) AS duplicate_users
        WHERE 
            duplicate_users.name = users.name 
                AND valid_duplicate_count = duplicate_count
    )    
GROUP BY 
    name 
HAVING 
    duplicate_count > 1

Ниже приведен запрос, который должен делать то же самое, что и выше, но в конечном списке будет отображаться только идентификатор, который не находится в допустимом списке. На самом деле это оказалось намного проще, чем я думал. И это в основном то же самое, что и выше, но единственная причина, по которой я держал выше, - это сохранить два варианта, и в случае, если я испортил вышеописанное... он усложняется, так как много вложенных запросов. Если CTE доступны для вас или даже временные таблицы. Это может сделать запрос более выразительным, чтобы разбить его на временные таблицы:). Надеюсь, это поможет и является тем, что вы ищете.

SELECT GROUP_CONCAT(id) AS "ids", 
    CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
    COUNT(*) AS "final_duplicate_count" 
    --This count could actually be 1 due to the nature of the query 
FROM 
    users
--get the list of duplicated user names
WHERE EXISTS
    (
        SELECT 
            CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
            COUNT(*) AS "total_duplicate_count"
        FROM 
            users AS total_dup_users
        --ignore valid_users whose count still matches
        WHERE NOT EXISTS
            (
                SELECT 1 
                FROM
                (
                    SELECT 
                        CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
                        COUNT(*) AS "valid_duplicate_count" 
                    FROM 
                        users AS valid_users
                    WHERE 
                        is_valid_duplicate = 1 --true
                    GROUP BY 
                        name 
                    HAVING 
                        valid_duplicate_count > 1 
                ) AS duplicate_users
                WHERE 
                    --join inner table to outer table
                    duplicate_users.name = total_dup_users.name  
                        --valid count check
                        AND valid_duplicate_count = total_duplicate_count
            )   
            --join inner table to outer table
            AND total_dup_users.Name = users.Name 
        GROUP BY 
            name 
        HAVING 
            duplicate_count > 1
    ) 
    --ignore users that are valid when doing the actual counts
    AND NOT EXISTS
    (
        SELECT 1
        FROM users AS valid
        WHERE 
            --join inner table to outer table
            users.name = 
                CONCAT(UPPER(valid.first_name), UPPER(valid.last_name))
            --only valid users
            AND valid.is_valid_duplicate = 1 --true
    )
GROUP BY 
    FinalDuplicates.Name

Ответ 3

Так как это в основном отношение "многие ко многим", я бы добавил новую таблицу not_duplicate с полями user1 и user2.

Я бы добавил две строки для каждого отношения not_duplicate, так что у меня есть одна строка для 2 -> 3 и симметричная строка для 3 -> 2, чтобы облегчить запрос, но это может привести к несогласованности данных, поэтому убедитесь, что вы удаляете обе строки в то же время (или иметь только одну строку и сделать правильный запрос в script).

Ответ 4

Мне кажется, что столбец is_not_duplicate недостаточно сложный, чтобы хранить информацию, которую вы хотите сохранить, - из того, что, как я понимаю, вы хотите вручную сообщить своему детектору, что два разных пользователя не являются дубликатами друг друга. так что либо вы создаете столбец типа is_not_duplicate_of = other-user-id, либо если вы хотите сохранить возможность открывать, что один пользователь может быть вручную определен не дублировать более чем одного пользователя, вам нужна отдельная таблица с двумя столбцами идентификатора пользователя.

запрос, говорящий вам, что неперекрываемые дубликаты, вероятно, должны быть немного сложнее, чем тот, который вы предложили, я не могу думать о том, что работает с группой и имеет логику. Единственное, что мне пришло в голову, это что-то вроде

SELECT u1.* FROM users u1
INNER JOIN users u2
ON u1.id <> u2.id
AND u2.name = u1.name
WHERE NOT EXISTS (
  SELECT *
  FROM users_non_dups un
  WHERE (un.id1 = u1.id AND un.id2 = u2.id)
  OR (un.id1 = u2.id AND un.id2 = u1.id)
)

Ответ 5

Если вы будете исправлять все дубликаты при каждом запуске отчета, то очень простым решением может быть изменение запроса:

SELECT 
    GROUP_CONCAT(id) AS "ids",
    MAX(id) AS "max_id",
    CONCAT(UPPER(first_name), UPPER(last_name)) AS "name",
    COUNT(*) AS "duplicate_count" 
FROM 
    users 
GROUP BY 
    name 
HAVING 
    duplicate_count > 1
    AND
    max_id > MAX_ID_LAST_TIME_DUPLICATE_REPORT_WAS_GENERATED;

Ответ 6

Я бы продолжил и сделал столбец "confirm_unique", по умолчанию "False".

Чтобы избежать проблем, о которых вы говорили,

Затем я выберу все элементы, которые могут выглядеть как дубликаты, и введите "False" для "confirm_unique".

Ответ 7

Я не уверен, что это сработает, но вы могли бы рассмотреть обратную логику добавления столбца * is_duplicate_of *? Таким образом, вы можете пометить дубликаты, введя идентификатор первой записи в этом столбце, который будет больше нуля. Записи, которые вы хотите сохранить, будут иметь значение 0 в этом поле. Вы можете установить значения по умолчанию (непроверенные записи) на -1, чтобы отслеживать статус проверки для каждой записи.

Впоследствии вы можете продолжать выполнять SQL, который будет сравнивать новые записи только с правильными записями с is_duplicate_of = 0.

Ответ 8

Если вы согласитесь внести небольшое изменение в формат отчета. Вы можете сделать это самостоятельно -

SELECT 
    CONCAT(u1.id,",", u2.id) AS "ids",
    CONCAT(UPPER(u1.first_name), UPPER(u1.last_name)) AS "name"
FROM 
    users u1, users u2
WHERE
    u1.id < u2.id AND
    UPPER(u1.first_name) = UPPER(u2.first_name) AND
    UPPER(u1.last_name) = UPPER(u2.last_name) AND
    CONCAT(u1.id,",", u2.id) NOT IN (SELECT ids from not_dupe)

который сообщает о дубликатах следующим образом:

ids | name
----|--------
1,2 | CHRISBAKER
1,3 | CHRISBAKER
...

И таблица not_dupe имела бы строки, как показано ниже:

ids
------
1,2
3,4
...

Ответ 9

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

например, в этом примере мы имели бы

id 1 | id 2

 2      4

если [email protected] и [email protected] являются разными людьми.

Ответ 10

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

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

Удачи.

Ответ 11

Я предлагаю вам создать пару вещей:

• Булевский столбец для подтверждения подтвержденных пользователей
• Строковый столбец для сохранения идентификаторов
• Триггер, который проверяет, находятся ли уже имя и фамилия, чтобы заполнить этот флаг, и сохраните в столбце строки все идентификаторы, для которых это возможно.

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

Ответ 12

Я дал Джастину Пихони +1 в качестве 1-го, чтобы предложить сравнить дублирующее количество с не дублирующимся счетом, а Грант Хачатрян +1 за то, что он первым показал эффективный способ сделать это.

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

Я бы назвал новый столбец "CONFIRMED_UNIQUE" вместо "IS_NOT_DUPLICATE". Как и Грант, я бы сделал его логическим (tinyint (1) с 0 = FALSE и 1 = TRUE).

"потенциал_duplicate_count" - это максимальное количество записей, которые необходимо удалить.

select
    group_concat(case when not confirmed_unique then id end) as potential_duplicate_ids,
    group_concat(case when confirmed_unique then id end) as confirmed_unique_ids,
    concat(upper(first_name), upper(last_name)) as name,
    sum( case when not confirmed_unique then 1 end ) - (not max(confirmed_unique)) as potential_duplicate_count
from
    users
group by
    name
having
    potential_duplicate_count > 0

Ответ 13

Я вижу, что кто-то еще проголосовал за предложение о слиянии, но ничего о вашем заявлении о проблемах не говорит о том, что данные должны быть на месте. OP следит за их решением, которое, оказывается, является SQL-кодом, что не означает, что каждое решение должно быть ограничено этим.

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

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

Чтобы включить это, вам понадобится четыре новых столбца:

• Статус, который будет либо открытым, объединенным, разделенным
• RelatedId, который будет содержать значение того, с кем запись была объединена с
• ChainId, новый идентификатор записи
• DateStatusChanged, достаточно очевидно

Открыть будет статус по умолчанию Объединенный будет, когда запись будет объединена (эффективно закрыта и заменена) Разделить будет, если слияние было отменено

Итак, в качестве примера, просмотрите все записи, которые, например, имеют одно и то же имя. Объедините их парами. Итак, если у вас есть три Chris Bakers, записи 1, 2 и 3, объедините 1 и 2, чтобы сделать запись 4, а затем 3 и 4, чтобы сделать запись 5. В вашей таблице будет что-то вроде:

ID  NAME        STATUS  RELATEDID  CHAINID DATESTATUSCHANGED [other rows omitted]
 1  Chris Baker MERGED          2        4       27-AUG-2012
 2  Chris Baker MERGED          1        4       27-AUG-2012
 3  Chris Baker MERGED          4        5       28-AUG-2012
 4  Chris Baker MERGED          3        5       28-AUG-2012
 5  Chris Baker   OPEN

Таким образом, у вас есть полная запись о том, что произошло с вашими данными, можно отменить любые изменения путем несмывания, если, например, контакты 1 и 2 не были одинаковыми, вы отменили слияние 3 и 4, измените слияние 1 и 2, вы получите следующее:

ID  NAME        STATUS  RELATEDID  CHAINID DATESTATUSCHANGED
 1  Chris Baker  SPLIT          2        4       29-AUG-2012
 2  Chris Baker  SPLIT          1        4       29-AUG-2012
 3  Chris Baker  SPLIT          4        5       29-AUG-2012
 4  Chris Baker CLOSED          3        5       29-AUG-2012
 5  Chris Baker CLOSED                           29-AUG-2012

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

Ответ 14

Есть ли веская причина не объединять дубликаты учетных записей в одну учетную запись?

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

Ответ 15

Добавьте is_not_duplicate по типу datatype в таблицу и используйте ниже запрос после значения is_not_duplicate данных:

SELECT  GROUP_CONCAT(id) AS "ids",
        CONCAT(UPPER(first_name), UPPER(last_name)) AS "name"
FROM users 
GROUP BY name 
HAVING COUNT(*) > SUM(CAST(is_not_duplicate AS INT))

выше запрос сопоставляет полные повторяющиеся строки по полному количеству повторяющихся строк.

Ответ 16

Почему бы вам не сделать столбец электронной почты уникальным идентификатором в этом случае, и после того, как вы очистите свои записи один раз, вы не разрешаете дубликаты оттуда?