#sql #oracle #plsql #oracle11gr2
#sql #Oracle #plsql #oracle11gr2
Вопрос:
За эти годы в наши таблицы данных было добавлено несколько дубликатов в результате орфографических ошибок. Так, например, кто-то неправильно написал имя и вместо O’leary написал Oleary. Наша система считает, что это совершенно другое имя, и не жалуется на это, однако в большинстве случаев один и тот же контакт вводится дважды (я не создавал эту систему).
Теперь я хочу удалить все эти дубликаты, но мне сложно создать запрос для их отображения. Я экспериментировал с UTL_MATCH и написал запрос, который вернет все похожие имена, если я укажу имя.
select first_name from customers
where UTL_MATCH.edit_distance_similarity(first_name,'Oleary') > 60
order by first_name;
Однако я хотел бы создать запрос, который автоматически возвращал бы все возможные дубликаты без необходимости указывать имя. Может ли кто-нибудь указать мне правильное направление, пожалуйста?
Комментарии:
1. В постановке задачи есть логическое несоответствие. Можно иметь три first_names, называть их fn1, fn2, fn3, при этом fn1 и fn3 оба «достаточно похожи» на fn2, но не «достаточно похожи» друг на друга. В этом случае вы могли бы либо сохранить fn2 и удалить два других, ЛИБО вы могли бы сохранить как fn1, так и fn3, но удалить средний. И тогда fn3 может быть похож на fn4, но fn4 не «достаточно» похож на fn1 и т.д. Вам нужна более четко определенная постановка проблемы, прежде чем вы сможете подумать о КАКОМ-ЛИБО решении.
2. Извините, mathguy, возможно, помешала разница между нашими уровнями IQ (я не понял, что вы только что сказали), однако я сделал все возможное, чтобы объяснить свою проблему как можно проще, я вижу некоторые ответы ниже, которые почти отвечают на мой запрос, так что, похоже, я не потерпел неудачу полностью. Но спасибо вам за ваш вклад.
3. Объяснение в качестве примера: три разные версии одного и того же имени, написанные по-разному (по какой-либо причине), но одного и того же человека. Первое имя — ДЖАДА, ДЖЕДА или ГЕДА. «Аналогично» — это количество общих букв. JADA для JEDA составляет 75% (они похожи более чем на 60%), JEDA и GEDA также 75%, но JADA и GEDA похожи только на 50%. Если вы используете свой запрос и в distance_similarity используете ‘JEDA’, будут выбраны оба других имени. Но если вы используете ‘JADA’ для сравнения, ‘GEDA’ не будет выбран. Таким образом, понятие «все дубликаты» четко не определено.
4. По той же логике, если следует применить «друг моего друга — мой друг», у вас может возникнуть такая ситуация: ABC похож на DBC, который похож на DEC, который похож на DEF. Означает ли это, что ABC похож на DEF?
5. Ha! Теперь я понимаю 🙂 Спасибо! Я думаю, что если бы кто-то реализовал вашу идею, приведенный ниже запрос стал бы намного более эффективным, но, возможно, на этот раз в этом нет необходимости, поскольку я могу выполнить работу, и мне нужно будет сделать это только один раз, поэтому скорость не так важна.
Ответ №1:
Что-то подобное технически сработало бы.
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 таблице с любой другой строкой в таблице (и ваш предел подобия расстояния редактирования довольно низок).). Чтобы ускорить это, вам, вероятно, придется сделать предположения о ваших данных или сделать что-то еще, что может быть предварительным фильтром. Например, если вы предполагаете, что любые дубликаты начинаются с одного и того же первого символа или с одних и тех же первых нескольких символов, игнорируя знаки препинания, то вы можете значительно уменьшить количество пар, которые необходимо сопоставить, рискуя упустить тот факт, что «Kustin» может быть дубликатом опечатки «Justin», гдепервый символ отличается. Требование этого c2.customer_id > c1.customer_id было бы еще одним разумным фильтром, который следует учитывать, предполагая, что вам не нужно дублировать каждую пару (т.Е. Строка «Kustin / Justin» может существовать без эквивалентной строки «Justin / Kustin»).
Комментарии:
1. Спасибо за ваш ответ! Я попробовал очень похожий подход, и мне пришлось отменить свой запрос через 5 минут, он все еще выполнялся, я подумал, что с моим кодом что-то не так. Любой способ увеличить его скорость?
2. @K.I — Как я уже сказал, было бы очень медленно сравнивать каждую строку в таблице с каждой строкой в таблице. Вам нужно было бы придумать какой-то разумный способ ограничить комбинации, которые вам необходимо учитывать, основываясь на некоторых знаниях ваших данных. Я упомянул несколько возможностей, которые могут быть или не быть разумными для вас. К сожалению, трудно догадаться, какая эвристика может быть разумной для ваших данных. Возможно, у ваших клиентов есть адресная информация, и вы можете сравнивать клиентов только с одним и тем же почтовым индексом, например. Или, может быть, вы можете позволить ему работать часами на ночь.
Ответ №2:
Вы можете использовать это для 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(), потому что я понимаю единицы измерения. - Объединение будет медленным, медленным, медленным, так что, надеюсь, у вас не слишком много строк.
- Вероятно, будет много ложных совпадений, поэтому будьте осторожны.
Комментарии:
1. Я бы просто добавил a
and c1.first_name < c2.first_nameв предложение join, чтобы избежать бесполезных дубликатов (и точных совпадений)2.Спасибо за ваш ответ! Я попробовал очень похожий подход, и мне пришлось отменить свой запрос через 5 минут, он все еще выполнялся, я подумал, что с моим кодом что-то не так. Любой способ увеличить его скорость?
3. @K.I . . . Начните с небольшого фрагмента таблицы, например, имен, начинающихся с «A», и посмотрите, как это происходит.