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

Мне вручили таблицу с 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 - это суть моего вопроса.

8.08.2008 16:19:20
5 ОТВЕТОВ
РЕШЕНИЕ

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


Обновить

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

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

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

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

1
8.08.2008 17:14:55

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

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 - Вы должны разбить названия вашей компании и названия местоположений на отдельные таблицы и ссылаться на них по их первичным ключам.

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

1
8.08.2008 17:33:30

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

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

0
8.08.2008 16:44:48

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

0
8.08.2008 16:47:35

Да, этот шаг 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;

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

0
8.08.2008 22:07:02