SQL для дедупликации нечетких совпадений
В таблице A есть записи с повторяющимися объектами с небольшими вариациями строк. Не существует уникального ключа, который бы однозначно идентифицировал сущность. Поле «ID» идентифицирует запись внутри таблицы, но не сам объект.
TABLE A
--------------
ID;SomeString
1;something1
2;something2
3;something3
При использовании программного обеспечения нечеткого сопоставления таблица A нечетко сопоставляется с самой собой, чтобы обнаружить повторяющиеся записи. Так создается справочная таблица B, в которой есть два столбца: ID1 и ID2, представляющие идентификаторы совпадающих записей из таблицы A.
TABLE B
---------
ID1;ID2
1;2
1;3
2;1
2;3
3;1
3;2
Результатом дедупликации будет удаление записей 2 и 3 из таблицы A, так что останется только первая запись.
TABLE A
--------------
ID;SomeString
1;something1
Есть ли способ выполнить такую дедупликацию с нечетким соответствием таблицы A через SQL, используя таблицу B в качестве таблицы поиска нечеткого соответствия идентифицированных повторяющихся записей? Чтобы уточнить, я не прошу способ выполнить нечеткое сопоставление или идентифицировать дубликаты, это уже сделано, и результаты находятся в таблице B. Я спрашиваю, как выполнить удаление дубликатов (и сохранение одной записи для каждой идентифицированной повторяющейся записи group) в соответствии с уже идентифицированными парами повторяющихся записей (несколько пар повторяющихся записей на одну и ту же сущность).
Если аналогичные значения соответствуют некоторому последовательному шаблону, вы можете использовать оператор LIKE с некоторыми значениями подстановочных знаков (%), чтобы найти похожие значения. Одна из проблем с таблицей B заключается в том, что у вас есть зеркальные значения, такие как 1; 2 и 2; 1. Если вы очистили его, чтобы удалить такие дубликаты, вы могли бы затем удалить строки в таблице A на основе значений ID2 в таблице B.
Какую СУБД вы используете? Вам необходимо определить, что именно составляет нечеткое совпадение. Совместно ли 90% символов, одинаковые последовательные символы превышают 90% длины строки, совпадают нечисловые символы и т.д. Если вы собираетесь делать это в SQL, ваша цель должна быть недвусмысленной.
Я не спрашиваю способ выполнить нечеткое совпадение, это уже сделано, и результаты находятся в таблице B. Я прошу способ выполнить удаление дубликатов в таблице A в соответствии с идентифицированными дубликатами (результаты в таблице B) . То, что используется СУБД, действительно не имеет значения, мой вопрос был общим по SQL.
@zlatko Какие СУБД могут иметь значение, потому что каждая из них реализует несколько разные функции, поэтому может быть функция, которая бы хорошо справлялась с ней в одной СУБД, но не реализована в другой. В этом случае я считаю, что его можно решить с помощью довольно простого оператора CASE, но это может быть не всегда так, даже если это кажется общим вопросом SQL.
Ответы 1
Основная проблема, которую я вижу, заключается в том, что ваша таблица нечетких совпадений содержит повторяющиеся пары с обратным порядком идентификаторов. Это означает, что у вас есть строки, чтобы сказать, что обе 2 являются дубликатом 1, а 1 - дубликатом 2. Если вы удалите все строки на основе столбца ID2 таблицы B, вы просто удалите все строки в таблице. А.
Вы можете решить эту проблему с помощью оператора select, который переупорядочивает столбцы так, чтобы меньший идентификатор всегда был первым. Таким образом, предыдущий пример «2 - это дубликат 1, а 1 - дубликат 2» становится просто повторением «2 - это дубликат 1». На этом этапе вы можете выбрать отдельные значения, чтобы получить список идентификаторов для удаления из таблицы A.
На основе ваших данных выборки этот запрос удалил правильные значения:
WITH Duplicates (ID) AS
(
SELECT DISTINCT
CASE
WHEN ID1 > ID2 THEN ID1
WHEN ID2 > ID1 THEN ID2
END AS Duplicate
FROM Table_B
)
DELETE
FROM Table_A
WHERE ID IN (SELECT * FROM Duplicates)
Вы спрашиваете, есть ли способ на самом деле выполнить нечеткое совпадение в SQL (возможно, но для этого есть лучшие инструменты), или есть способ использовать Результаты вашего нечеткого совпадения (в таблице B) для обработки удалений ( да легко)?