Мне вручили таблицу с 18000 строками. Каждая запись описывает местоположение одного клиента. Проблема заключается в том, что, когда человек создал таблицу, он не добавил поле для «Название компании», только «Имя местоположения», и одна компания может иметь много местоположений.
Например, вот некоторые записи, которые описывают одного и того же клиента:
Таблица расположения
ID Location_Name
1 TownShop#1
2 Town Shop - Loc 2
3 The Town Shop
4 TTS - Someplace
5 Town Shop,the 3
6 Toen Shop4
Моя цель - сделать так, чтобы это выглядело так:
Таблица расположения
ID Company_ID Location_Name
1 1 Town Shop#1
2 1 Town Shop - Loc 2
3 1 The Town Shop
4 1 TTS - Someplace
5 1 Town Shop,the 3
6 1 Toen Shop4
Стол компании
Company_ID Company_Name
1 The Town Shop
Нет таблицы «Компания», мне нужно будет создать список «Название компании» из наиболее описательного или лучшего названия местоположения, которое представляет несколько местоположений.
В настоящее время я думаю, что мне нужно сгенерировать список имен локаций, которые похожи, а затем и пройти через этот список вручную.
Любые предложения о том, как я могу подойти к этому, приветствуется.
@Neall, спасибо за ваше заявление, но, к сожалению, каждое название местоположения отличается, нет повторяющихся названий, только похожие. Так что в результатах вашего утверждения «repcount» стоит 1 в каждой строке.
@yukondude, Твой шаг 4 - это суть моего вопроса.
Пожалуйста, обновите вопрос. У вас есть список доступных имен компаний? Я спрашиваю, потому что вы, возможно, можете использовать алгоритм Левенштейна, чтобы найти связь между вашим списком CompanyNames и LocationNames.
Обновить
Там нет списка названий компаний, мне нужно будет сгенерировать название компании из наиболее описательного или лучшего названия местоположения, которое представляет несколько местоположений.
Хорошо ... попробуйте это:
- Составьте список кандидатов CompanyNames, найдя LocationNames, состоящий в основном из букв или всех буквенных символов. Вы можете использовать регулярные выражения для этого. Храните этот список в отдельной таблице.
- Отсортируйте этот список по алфавиту и (вручную) определите, какие записи должны быть CompanyNames.
- Сравните каждое CompanyName с каждым LocationName и определите результат матча (используйте Левенштейна или другой алгоритм сопоставления строк). Сохраните результат в отдельной таблице.
- Установите пороговый балл так, чтобы любой MatchScore <Threshold не считался совпадением для данного CompanyName.
- Вручную проверить через 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. Там нет общего решения, но вы могли бы сделать что-то вроде этого:
- Создайте таблицу Company со столбцом id, который автоматически увеличивается (зависит от вашей РСУБД).
- Найдите все уникальные названия компаний и вставьте их в Компанию.
- Добавьте столбец company_id в Location, который принимает значения NULL (пока) и который является внешним ключом столбца Company.id.
- Для каждой строки в местоположении определите соответствующую компанию и ОБНОВИТЕ столбец company_id этой строки с идентификатором этой компании. Это, вероятно, самый сложный шаг. Если ваши данные похожи на то, что вы показываете в примере, вам, вероятно, придется выполнить много прогонов с различными подходами сопоставления строк.
- Когда все строки в 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;
Затем просто запустите утверждения, которые он производит. Это может сделать большую часть работы над гранжем для вас.