我已经交给一张约有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步是我的问题的核心。

解决方案

回答

我以前必须这样做。唯一真正的方法是手动匹配各个位置。使用数据库的控制台界面，并对select语句进行分组。首先，添加"公司名称"字段。然后：

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.我们实际上应该将公司名称和位置名称分成单独的表，并通过其主键引用它们。

更新：哇没有重复吗？我们有几条记录？

回答

请更新问题，我们有可用的公司名称列表吗？我问是因为我们也许可以使用Levenshtein算法在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.

好吧...试试这个：

• 通过查找由大部分或者所有字母字符组成的LocationName来建立候选CompanyName的列表。我们可以为此使用正则表达式。将此列表存储在单独的表中。
• 按字母顺序对列表进行排序，并(手动)确定哪些条目应为公司名称。
• 将每个CompanyName与每个LocationName进行比较，并得出一个匹配分数(使用Levenshtein或者其他一些字符串匹配算法)。将结果存储在单独的表中。
• 设置阈值得分，以使任何MatchScore <阈值都不会被视为给定公司名称的匹配项。
• 手动审核LocationNames by CompanyName | LocationName | MatchScore，并找出实际匹配的内容。通过MatchScore进行订购应该可以减轻过程的痛苦。

上述操作的全部目的是使部件自动化并限制问题的范围。它远非完美，但有望为我们省去手工处理18K记录的麻烦。

回答

我打算推荐一些复杂的令牌匹配算法，但是正确处理起来确实很棘手，如果数据没有很多相关性(打字错误等)，那么它就不会给出很好的结果。

我建议我们将工作提交给Amazon Mechanical Turk，然后由人工进行分类。

回答

理想情况下，我们可能想要一个名为Company的单独表，然后在此" Location"表中需要一个company_id列，该列是Company表的主键的外键，可能称为id。这样可以避免该表中的大量文本重复(超过18,000行，整数外键将在varchar列上节省相当多的空间)。

但是我们仍然面临着加载该Company表，然后将其与Location中的行正确关联的方法。没有通用的解决方案，但是我们可以按照以下方式做一些事情：

• 创建公司表，其id列会自动递增(取决于RDBMS)。
• 查找所有唯一的公司名称，并将其插入"公司"中。
• 将一列company_id添加到Location(该列现在接受NULL)，并且这是Company.id列的外键。
• 对于"位置"中的每一行，确定相应的公司，然后使用该公司的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行中，我们将必须具有相当的创造力才能处理各种组合。

其他可能有用的方法是使用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;

然后只运行它产生的语句。那可以为我们完成很多垃圾工作。