Oracle, как удалить почти одинаковые строки из таблицы

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

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

select first_name from customers 
where UTL_MATCH.edit_distance_similarity(first_name,'Oleary') > 60
order by first_name;

Однако я хотел бы создать запрос, который автоматически возвращал бы все возможные дубликаты без необходимости указывать имя. Может ли кто-нибудь указать мне правильное направление, пожалуйста?


sql oracle plsql oracle11gr2
person K.I    schedule 13.10.2016    source источник
comment
Логическая нестыковка в постановке задачи. Можно иметь три first_names, назовите их fn1, fn2, fn3, где fn1 и fn3 достаточно похожи на fn2, но недостаточно похожи друг на друга. В этом случае вы можете оставить fn2 и удалить две другие, ИЛИ вы можете сохранить обе fn1 и fn3, но удалить среднюю. И тогда fn3 может быть похожа на fn4, но fn4 недостаточно похожа на fn1 и т. д. Вам нужна более четко сформулированная постановка проблемы, прежде чем вы сможете думать о ЛЮБОМ решении.   -  person mathguy    schedule 13.10.2016
comment
Извини, математик, возможно, разница между нашими уровнями IQ помешала (я не понял, что ты только что сказал), однако я сделал все возможное, чтобы объяснить свою проблему как можно проще, я вижу некоторые ответы ниже этого почти ответа мой запрос, так что, похоже, я не полностью провалился. Но спасибо за ваш вклад.   -  person K.I    schedule 13.10.2016
comment
Объясняю на примере: три разные версии одного и того же имени, написанные по-разному (по какой-то причине), но один и тот же человек. Имя JADA, JEDA или GEDA. Аналогично тому, сколько букв общего. JADA к JEDA составляет 75% (они похожи более чем на 60%), JEDA и GEDA также 75%, но JADA и GEDA похожи только на 50%. Если вы используете свой запрос и в Distance_similarity вы используете «JEDA», будут выбраны оба других имени. Но если вы используете для сравнения «JADA», «GEDA» не будет выбрано. Таким образом, концепция всех дубликатов определена нечетко.   -  person mathguy    schedule 13.10.2016
comment
По той же логике, если следует применить друг моего друга к моему другу, у вас может быть такая ситуация: ABC похож на DBC, который похож на DEC, который похож на DEF. Означает ли это, что ABC похож на DEF?   -  person mathguy    schedule 13.10.2016
comment
Ха! Теперь понял :) Спасибо! Я думаю, что если бы кто-то реализовал вашу идею, запрос, указанный ниже, стал бы намного более эффективным, но, возможно, на этот раз в этом нет необходимости, поскольку я могу выполнить работу, и мне нужно будет сделать это только один раз, поэтому скорость не так важна. .   -  person K.I    schedule 13.10.2016
comment
Я разделил свой запрос по местоположениям, все хорошо, мне придется запускать его несколько раз, но, как я уже сказал, только один раз.   -  person K.I    schedule 13.10.2016

Ответы (2)


arrow_upward
3
arrow_downward

Что-то вроде этого технически сработает.

select c1.first_name, c2.first_name
  from customers c1
       cross join customers c2
 where utl_match.edit_distance_similarity( c1.first_name, c2.first_name ) > 60
 order by c1.first_name

Однако это было бы очень медленно, если только ваша таблица customers не окажется очень (очень) маленькой, поскольку вы сравниваете каждую строку в таблице customers с каждой другой строкой в ​​таблице (и ваше отсечение подобия расстояния редактирования довольно низкое) . Чтобы сделать это быстрее, вам, вероятно, придется делать предположения о ваших данных или делать что-то еще, что может быть предварительным фильтром. Например, если вы предполагаете, что любые дубликаты начинаются с одного и того же первого символа или с одних и тех же первых нескольких символов, игнорируя знаки препинания, вы можете значительно уменьшить количество пар, которые необходимо сопоставить, рискуя упустить тот факт, что «Кастин» может быть опечаткой, дублирующей «Джастин», где первый символ отличается. Требование этого c2.customer_id > c1.customer_id было бы еще одним разумным фильтром, который следует учитывать при условии, что вам не нужно дублировать каждую пару (т. Е. Строка «Кустин/Джастин» может существовать без эквивалентной строки «Джастин/Кастин»).

person Justin Cave    schedule 13.10.2016
comment
Спасибо за ваш ответ! Я попробовал очень похожий подход, и мне пришлось отменить свой запрос через 5 минут, он все еще выполнялся, я подумал, что с моим кодом что-то не так. Есть ли способ увеличить его скорость? - person K.I; 13.10.2016
comment
@ K.I - Как я уже сказал, было бы очень медленно сравнивать каждую строку в таблице с каждой строкой в ​​таблице. Вам нужно будет придумать какой-то разумный способ ограничить комбинации, которые вам нужно учитывать, на основе некоторого знания ваших данных. Я упомянул несколько возможностей, которые могут быть или не быть разумными для вас. К сожалению, трудно предположить, что может быть разумной эвристикой для ваших данных. Возможно, у ваших клиентов есть адресная информация, и вы можете, например, сравнивать только клиентов с одним и тем же почтовым индексом. Или, может быть, вы можете позволить ему работать в течение нескольких часов на ночь. - person Justin Cave; 13.10.2016

arrow_upward
3
arrow_downward

Вы можете использовать это для join:

select c1.first_name, c2.first_name
from customers c1 join
     customers c2
     on UTL_MATCH.edit_distance(c1.first_name, c2.first_name) <= 3
order by c1.first_name;

Заметки:

  • Я предпочитаю edit_distance() edit_distance_similarity(), потому что понимаю единицы измерения.
  • Объединение будет медленным, медленным, медленным, поэтому, надеюсь, у вас не слишком строк.
  • Вероятно, будет много ложных совпадений, так что будьте осторожны.
person Gordon Linoff    schedule 13.10.2016
comment
Я бы просто добавил and c1.first_name < c2.first_name в предложение соединения, чтобы избежать бесполезных дубликатов (и точных совпадений) - person Raphaël Althaus; 13.10.2016
comment
Спасибо за ваш ответ! Я попробовал очень похожий подход, и мне пришлось отменить свой запрос через 5 минут, он все еще выполнялся, я подумал, что с моим кодом что-то не так. Есть ли способ увеличить его скорость? - person K.I; 13.10.2016
comment
@К.И. . . Начните с небольшого фрагмента таблицы, например, с имен, начинающихся с буквы А, и посмотрите, что получится. - person Gordon Linoff; 14.10.2016