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

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

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

Выясните, какой компании принадлежит местоположение в верхней части списка, а затем обновите поле названия вашей компании с помощью оператора UPDATE ... WHERE "Location Name" = "The Location".

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

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

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

Обновлять

There is not a list of Company Names, I will have to generate the company name from the most descriptive or best Location Name that represents the multiple locations.

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

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

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

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

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

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

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

1. Создайте таблицу Company с автоматически увеличивающимся столбцом id (зависит от вашей СУБД).
2. Найдите все уникальные названия компаний и вставьте их в «Компания».
3. Добавьте столбец company_id в Location, который принимает значения NULL (на данный момент) и является внешним ключом столбца Company.id.
4. Для каждой строки в Location определите соответствующую компанию и ОБНОВИТЕ столбец 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), но, конечно, в 18000 строках вам придется проявить изобретательность, чтобы обрабатывать различные комбинации.

Еще кое-что, что может помочь, - это использовать имена в Company для генерации запросов, подобных приведенному выше. Вы можете сделать что-то вроде следующего (в 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;

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