Дедупликация таблицы SQL Server

Question

У меня есть проблема. У меня есть таблица с почти 2 миллиардами строк (да, я знаю...) и в ней много дублирующих данных, которые я хотел бы удалить из нее. Мне было интересно, как именно это сделать?

Столбцы: first, last, dob, address, city, state, zip, phone и находятся в таблице с именем PF_main, К счастью, каждая запись имеет уникальный идентификатор, и ее в столбце называется ID,

Как я могу дедуплицировать это и оставить 1 уникальную запись (строку) в пределах pf_main стол на каждого человека??

Спасибо всем заранее за ваши ответы...

Answer 1

Таблица с 2 миллиардами строк довольно большая. Позвольте мне предположить, что first, last, а также dob составляет "личность". Мое предложение состоит в том, чтобы построить индекс на "человека", а затем сделать truncate/ переставить подход.

На практике это выглядит так:

create index idx_pf_main_first_last_dob on pf_main(first, last, dob);

select m.*
into temp_pf_main
from pf_main m
where not exists (select 1
                  from pf_main m2
                  where m2.first = m.first and m2.last = m.last and m2.dob = m.dob and
                        m2.id < m.id
                 );

truncate table pf_main;

insert into pf_main
    select *
    from temp_pf_main;

Answer 2

SELECT 
   ID, first, last, dob, address, city, state, zip, telephone, 
   ROW_NUMBER() OVER (PARTITION BY first, last, dob, address, city, state, zip, telephone ORDER BY ID) AS RecordInstance
FROM PF_main

даст вам "номер" каждой уникальной записи (отсортировано по Id)

так что если у вас есть следующие записи:

id, last, first, dob, адрес, город, штат, почтовый индекс, телефон
006, trevelyan, alec, '1954-05-15', '85 Albert Embankment ',' London ',' UK ',' 1SE1 7TP ', 0064
007, bond, james, '1957-02-08', '85 Albert Embankment ',' London ',' UK ',' 1SE1 7TP ', 0074
008, bond, james, '1957-02-08', '85 Albert Embankment ',' London ',' UK ',' SE1 7TP ', 0074
009, bond, james, '1957-02-08', '85 Albert Embankment ',' London ',' UK ',' SE1 7TP ', 0074

Вы получите следующие результаты (обратите внимание на последний столбец)

006, trevelyan, alec, '1954-05-15', '85 Albert Embankment ',' London ',' UK ',' 1SE1 7TP ', 0064, 1
007, bond, james, '1957-02-08', '85 Albert Embankment ',' London ',' UK ',' 1SE1 7TP ', 0074, 1
008, bond, james, '1957-02-08', '85 Albert Embankment ',' London ',' UK ',' SE1 7TP ', 0074, 2
009, bond, james, '1957-02-08', '85 Набережная Альберта ',' Лондон ',' Великобритания ',' SE1 7TP ', 0074, 3

Так что вы можете просто удалить записи с RecordInstance > 1:

WITH Records AS
(
   SELECT 
      ID, first, last, dob, address, city, state, zip, telephone,
      ROW_NUMBER() OVER (PARTITION BY first, last, dob, address, city, state, zip, telephone ORDER BY ID) AS RecordInstance
   FROM PF_main
)
DELETE FROM Records
WHERE RecordInstance > 1

Answer 3

Другие ответы, безусловно, дадут вам представление о синтаксисе.

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

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

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

-- Pseudocode
counter = 0;
for each row in dup table       -- and if this takes long, break this into ranges
   delete from primary_tab where id = @id
   if counter++ > 1000 then
      commit;
      counter = 0;
   end if
end loop

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

Предостережение: Это не дефрагментирует свободные блоки / строки и не объединяет удаленное пространство, как полное построение новой таблицы, но позволяет делать это онлайн и без выделения новой копии. С другой стороны, если у вас есть свобода делать это онлайн и / или окно обслуживания, а количество повторяющихся строк превышает, например, 15-20% ваших данных, то вам следует выбрать "создать таблицу как выбрать * из "минус дубликаты", как в ответе Гордона, для сжатия сегмента данных в плотно используемый непрерывный сегмент и повышения производительности кэширования / ввода-вывода в долгосрочной перспективе. Однако редко встречаются дубликаты, превышающие долю процента пространства.

Причины для этого включают в себя:

1 - таблица слишком велика для создания временной дублированной копии.

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

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

В противном случае, смотрите ответ Гордона Линоффа.

Answer 4

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

ИМХО сложно полностью автоматизировать процесс устранения дубликатов в большой таблице лиц. Если сопоставление слишком ослаблено... тогда законные записи будут удалены. Если сопоставление слишком строгое... дубликаты останутся в.

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

Это может не сработать в вашем проекте (с миллиардами строк), но имеет смысл в среде, где необходимо избегать как дублирования, так и потери данных. Один оператор-человек может улучшить чистоту нескольких тысяч строк данных за несколько минут, и они могут делать это понемногу за несколько сеансов.

Answer 5

ИМХО, нет лучшего способа дедупликации, поэтому вы видите так много разных решений. Это зависит от вашей ситуации. Теперь у меня есть ситуация, когда у меня есть большой файл истории, в котором перечисляются ежемесячные метрики для каждой из десятков тысяч кредитных счетов по годам и годам. Каждая учетная запись представлена ​​в файле в течение многих месяцев, пока она остается активной, но когда она становится неактивной, она появится не позднее. Я хочу только последние или последние записи для каждой учетной записи. Меня не волнует запись, когда счет был открыт 20 лет назад, и все, что меня волнует, это последняя запись, когда счет был закрыт 5 лет назад. Для тех учетных записей, которые еще активны, я хочу получить запись за последний календарный месяц. Поэтому я думаю, что "дубликаты" - это записи для одной и той же учетной записи для всех, кроме последней ежемесячной записи для этой учетной записи, и я хочу от них избавиться.

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

(Я делаю большую часть своего SQL-кода в SAS PROC SQL, но я думаю, что вы поймете идею.)

Мое решение использует подзапрос...

 /* dedupe */ 
 proc sql ;   
   create table &delta. as       
     select distinct b.*, sub.mxasof
       from &bravo. b
       join ( select distinct Loan_Number, max(asof) as mxasof format 6.0
                from &bravo.
                group by Loan_Number
            ) sub
       on 1
       and b.Loan_Number = sub.Loan_Number
       and b.asof = sub.mxasof
       where 1
       order by b.Loan_Number, b.asof desc ;