Какой лучший способ дедуплировать таблицу?

Ответ 1

SELECT DISTINCT <insert all columns but the PK here> FROM foo. Создайте временную таблицу с использованием этого запроса (синтаксис зависит от СУБД, но обычно имеется шаблон SELECT … INTO или CREATE TABLE AS), затем сбрасывает старую таблицу и накачивает данные из таблицы temp обратно.

Ответ 2

Использование аналитической функции row_number:

WITH CTE (col1, col2, dupcnt)
AS
(
SELECT col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1) AS dupcnt
FROM Youtable
)
DELETE
FROM CTE
WHERE dupcnt > 1
GO                                                                 

Ответ 3

Дедупин редко бывает простым. Это потому, что учетные записи, которые должны быть дедуплированы, часто имеют несколько разные значения, это некоторые из полей. Поэтому выберите, какая запись сохранить может быть проблематичной. Кроме того, обманщики часто являются людьми, и трудно определить, являются ли два Джона Смита двумя людьми или одним человеком, который дублируется. Так что потратьте много (50% или более всего проекта) вашего времени, определяя, что составляет дуп, и как справляться с различиями и дочерними записями.

Откуда вы знаете, что является правильным значением? Дальнейшая дедупликация требует, чтобы вы обрабатывали все дочерние записи, а не сироты. Что происходит, когда вы обнаруживаете, что, изменив идентификатор на дочерней записи, вы внезапно нарушаете один из уникальных индексов или ограничений - это произойдет в конечном итоге, и ваш процесс должен обработать его. Если вы выбрали глупо, чтобы применить все свои ограничения только для тщательного приложения, вы можете даже не знать, что ограничения нарушены. Когда у вас есть 10 000 записей для дедупликации, вы не будете проходить через приложение для дедуплирования по одному за раз. Если ограничение не находится в базе данных, удачи в сохранении целостности данных при выводе.

Еще одно осложнение заключается в том, что дубликаты не всегда точно соответствуют имени или адресу. Например, salesrep по имени Джоан Мартин может быть дубликом репутации продавца Joan Martin-Jones, особенно если у них одинаковый адрес и адрес электронной почты. Или вы могли бы иметь Джона или Джонни в названии. Или тот же адрес улицы, кроме одной записи, аббревиатура ST. и одну простую улицу. В SQL-сервере вы можете использовать SSIS и нечеткую группировку, чтобы также идентифицировать ближние совпадения. Они часто являются наиболее распространенными дубликами, поскольку тот факт, что не были точными совпадениями, - это то, почему они попали в качестве дубликатов в первую очередь.

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

Ответ 4

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

delete t
from tablename t
inner join  
(
    select date_time, min(id) as min_id
    from tablename
    group by date_time
    having count(*) > 1
) t2 on t.date_time = t2.date_time
where t.id > t2.min_id

В этом примере date_time является критерием группировки, если у вас есть более одного столбца, обязательно присоединитесь ко всем из них.

Ответ 5

Добавление фактического кода здесь для дальнейшего использования

Итак, есть 3 шага и, следовательно, 3 оператора SQL:

Шаг 1: Переместите не дубликаты (уникальные кортежи) во временную таблицу

CREATE TABLE new_table as
SELECT * FROM old_table WHERE 1 GROUP BY [column to remove duplicates by];

Шаг 2: удалите старую таблицу (или переименуйте ее) Нам больше не нужна таблица со всеми повторяющимися записями, поэтому оставьте ее!

DROP TABLE old_table;

Шаг 3: переименуйте new_table в имя old_table

RENAME TABLE new_table TO old_table;

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

Ответ 6

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

В этом примере говорят, что у меня есть 3 записи с одним и тем же app_id, и я хочу сохранить только ту, которая имеет самую высокую дату:

DELETE t
FROM @USER_OUTBOX_APPS t
INNER JOIN  
(
    SELECT 
         app_id
        ,max(processed_date) as max_processed_date
    FROM @USER_OUTBOX_APPS
    GROUP BY app_id
    HAVING count(*) > 1
) t2 on 
    t.app_id = t2.app_id
WHERE 
    t.processed_date < t2.max_processed_date

Ответ 7

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

ALTER IGNORE TABLE TABLE_NAME ADD UNIQUE (column1, column2, column3)

Вы можете удалить уникальные индексные слова.

Ответ 8

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

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

• Найдите дубликатов
• Выберите один из них как dupeA, чтобы устранить
• Найти все данные, зависящие от dupeA
• Измените эти данные для ссылки на dupeB
• удалить dupeA.

Это может быть легко или сложно, в зависимости от вашей существующей модели данных.

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

Ответ 9

Для SQL вы можете использовать INSERT IGNORE INTO table SELECT xy FROM unkeyed_table;

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

Ответ 10

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

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

Ответ 11

Это может привести к дублированию значений в c1:

select * from foo
minus
select f1.* from foo f1, foo f2
where f1.c1 = f2.c1 and f1.c2 > f2.c2

Ответ 12

Вот один, с которым я столкнулся, в реальной жизни.

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

    ;WITH Logins AS
    (
        SELECT [LoginId],[UserId],[Provider],[ProviderKey]
        FROM [dbo].[UserLogin] 
        WHERE [UserId][email protected] -- is the user we're deleting
              OR [UserId][email protected] -- is the user we're moving data to
    ), Ranked AS 
    (
        SELECT Logins.*
            , [Picker]=ROW_NUMBER() OVER (
                       PARTITION BY [Provider],[ProviderKey]
                       ORDER BY CASE WHEN [UserId][email protected] THEN 1 ELSE 0 END)
        FROM Logins
    )
    MERGE Logins AS T
    USING Ranked AS S
    ON S.[LoginId]=T.[LoginID]
    WHEN MATCHED AND S.[Picker]>1 -- duplicate Provider/ProviderKey
                 AND T.[UserID][email protected] -- safety check 
    THEN DELETE
    WHEN MATCHED AND S.[Picker]=1 -- the only or best one
                 AND T.[UserID][email protected]
    THEN UPDATE SET T.[UserID][email protected]
    OUTPUT $action, DELETED.*, INSERTED.*;

Ответ 13

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

Ответ 14

Для дедупликации/дедупликации/удаления дублирования/удаления повторяющихся строк /重 除 重/数据库 去除 重复 记录 ， существует несколько способов.

1. Если дублирующиеся строки одинаковы, используйте group by

   создать таблицу TABLE_NAME_DEDUP
   как выберите column1, column2,... (все имена столбцов) из группы TABLE_NAME по column1, column2, - все имена столбцов

Тогда TABLE_NAME_DEDUP является дедуплицированной таблицей.

Например,

create table test (t1 varchar(5), t2 varchar(5));
insert into test  values ('12345', 'ssdlh');
insert into test  values ('12345', 'ssdlh');
create table test_dedup as
select * from test 
group by t1, t2;
-----optional
--remove original table and rename dedup table to previous table
--this is not recommend in dev or qa. DROP table test; Alter table test_dedup rename to test;

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

   создать таблицу test_dedup как select column1, column2,... (все имена столбцов) из (select *, row_number() over (разделить по порядку rowid по column1, column2,... (все имена столбцов, кроме rowid)) как cn из test) где cn = 1

Это использует функцию, которая, когда вы используете order by, нулевое значение будет упорядочено за ненулевым значением.

create table test (rowid_ varchar(5), t1 varchar(5), t2 varchar(5));
insert into test  values ('12345', 'ssdlh', null);
insert into test  values ('12345', 'ssdlh', 'lhbzj');
create table test_dedup as
select rowid_, t1, t2 from
（select *
  , row_number() over (partition by rowid_ order by t1, t2) as cn
  from  test)
 where cn =1
 ;

-----optional
--remove original table and rename dedup table to previous table
--this is not recommend in dev or qa. DROP table test; Alter table test_dedup rename to test;

Ответ 15

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

Шаг 1: Создать новую таблицу с дедуплированными значениями

Если этот код был заимствован где-то еще в StackOverflow, но, похоже, не может найти его снова Он отлично работает против PostgreSQL. Он создает таблицу your_table_deduped где (col1, col2) являются уникальными.

CREATE TABLE your_table_deduped AS
SELECT * FROM your_table WHERE ctid NOT IN
(SELECT ctid FROM
  (SELECT ctid, ROW_NUMBER() OVER
    (PARTITION BY col1, col2 ORDER BY ctid) AS rnum
  FROM your_table) t
WHERE t.rnum > 1);

Шаг 2: замените свою первую таблицу дедуплицированной копией

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

DELETE FROM your_table;
INSERT INTO your_table
SELECT * FROM your_table_deduped;

Шаг 3: Удалить дедуплицированную копию

DROP TABLE site_daily_kpis_dedup;

И вуаля, вы дедуплицировали свой стол!

Ответ 16

Я описываю мой личный метод здесь: https://hg-map.fr/astuces/66-sql-automatic-deduplication Жорж

Ответ 17

delete from yourTable 
where Id not in (
    select min(id) 
    from yourTable
    group by <Unique Columns>
)

где id - это ваш уникальный идентификатор в таблице. (Может быть customerNumber или что-то еще)

Если у вас нет уникального идентификатора, вы можете добавить его (у каждой таблицы SQL уже должен быть идентификатор в качестве первого столбца, но

ALTER TABLE yourTable
ADD Id int identity(1,1)

Удалите (выше), а затем опустите столбец.

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