Нормализация таблицы с низкой целостностью

Пожалуйста, обновите вопрос. У вас есть список доступных имен компаний? Я спрашиваю, потому что вы, возможно, можете использовать алгоритм Левенштейна, чтобы найти связь между вашим списком CompanyNames и LocationNames.

Обновить

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

Хорошо ... попробуйте это:

1. Составьте список кандидатов CompanyNames, найдя LocationNames, состоящий в основном из букв или всех буквенных символов. Вы можете использовать регулярные выражения для этого. Храните этот список в отдельной таблице.
2. Отсортируйте этот список по алфавиту и (вручную) определите, какие записи должны быть CompanyNames.
3. Сравните каждое CompanyName с каждым LocationName и определите результат матча (используйте Левенштейна или другой алгоритм сопоставления строк). Сохраните результат в отдельной таблице.
4. Установите пороговый балл так, чтобы любой MatchScore <Threshold не считался совпадением для данного CompanyName.
5. Вручную проверить через LocationNames по CompanyName | LocationName | MatchScore, и выяснить, какие из них на самом деле соответствуют. Заказ по MatchScore должен сделать процесс менее болезненным.

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

Я должен был сделать это раньше. Единственный реальный способ сделать это - вручную сопоставить различные местоположения. Используйте консольный интерфейс вашей базы данных и группируйте операторы выбора. Сначала добавьте поле «Название компании». Потом:

SELECT count(*) AS repcount, "Location Name" FROM mytable
 WHERE "Company Name" IS NULL
 GROUP BY "Location Name"
 ORDER BY repcount DESC
 LIMIT 5;

Выясните, к какой компании принадлежит местоположение в верхней части списка, а затем обновите поле названия вашей компании с помощью ОБНОВЛЕНИЯ ... ГДЕ "Location Name" = "The Location".

PS - Вы должны разбить названия вашей компании и названия местоположений на отдельные таблицы и ссылаться на них по их первичным ключам.

Обновление: - Ух ты - без дубликатов? Сколько у вас записей?

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

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

В идеале вам, возможно, понадобится отдельная таблица с именем Company, а затем столбец company_id в этой таблице «Location», который является внешним ключом первичного ключа таблицы Company, который, вероятно, называется id. Это позволило бы избежать значительного дублирования текста в этой таблице (более 18 000 строк, целочисленный внешний ключ сэкономил бы довольно много места над столбцом varchar).

Но вы по-прежнему сталкиваетесь с методом загрузки этой таблицы Company и последующей ее правильной привязки к строкам в Location. Там нет общего решения, но вы могли бы сделать что-то вроде этого:

1. Создайте таблицу Company со столбцом id, который автоматически увеличивается (зависит от вашей РСУБД).
2. Найдите все уникальные названия компаний и вставьте их в Компанию.
3. Добавьте столбец company_id в Location, который принимает значения NULL (пока) и который является внешним ключом столбца Company.id.
4. Для каждой строки в местоположении определите соответствующую компанию и ОБНОВИТЕ столбец company_id этой строки с идентификатором этой компании. Это, вероятно, самый сложный шаг. Если ваши данные похожи на то, что вы показываете в примере, вам, вероятно, придется выполнить много прогонов с различными подходами сопоставления строк.
5. Когда все строки в Location имеют значение company_id, вы можете изменить таблицу Company, чтобы добавить ограничение NOT NULL в столбец company_id (при условии, что в каждом местоположении должна быть компания, что представляется разумным).

Если вы можете сделать копию своей таблицы Location, вы можете постепенно создать серию операторов SQL для заполнения внешнего ключа company_id. Если вы допустили ошибку, вы можете просто начать сначала и перезапустить сценарий до точки отказа.

Да, этот шаг 4 из моего предыдущего поста - офигительный.

Несмотря ни на что, вам, вероятно, придется делать это вручную, но вы можете автоматизировать большую часть этого. Для приведенных вами примеров местоположений запрос, подобный следующему, установит соответствующее значение company_id:

UPDATE  Location
SET     Company_ID = 1
WHERE   (LOWER(Location_Name) LIKE '%to_n shop%'
OR      LOWER(Location_Name) LIKE '%tts%')
AND     Company_ID IS NULL;

Я считаю, что это будет соответствовать вашим примерам (я добавил IS NULLчасть, чтобы не перезаписывать ранее установленные значения Company_ID), но, конечно, в 18 000 строк вам придется проявить изобретательность для обработки различных комбинаций.

Еще может помочь использование имен в Компании для генерации запросов, подобных приведенному выше. Вы можете сделать что-то вроде следующего (в MySQL):

SELECT  CONCAT('UPDATE Location SET Company_ID = ',
        Company_ID, ' WHERE LOWER(Location_Name) LIKE ',
        LOWER(REPLACE(Company_Name), ' ', '%'), ' AND Company_ID IS NULL;')
FROM    Company;

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