数据对比

Question

我们有一个包含公司名称、地址和联系人姓名（等等）的 SQL Server 表。

我们定期从外部来源接收数据文件，要求我们与此表进行匹配。 不幸的是，数据略有不同，因为它来自完全不同的系统。 例如，我们有“123 E. Main St.” 我们收到“123 East Main Street”。 另一个例子，我们有“Acme, LLC”，文件包含“Acme Inc.”。 另一个是，我们有“Ed Smith”，他们有“Edward Smith”。

我们有一个遗留系统，它利用一些相当复杂和 CPU 密集型的方法来处理这些比赛。 有些涉及纯 SQL，有些则涉及 Access 数据库中的 VBA 代码。 目前的系统不错，但并不完美，而且麻烦且难以维护。

这里的管理层希望扩大其使用范围。 将继承系统支持的开发人员希望用需要更少维护的更敏捷的解决方案来替换它。

是否有一种普遍接受的方法来处理这种数据匹配？

Answer 1

Access 确实没有这方面的工具。 在理想的情况下，我会使用 SSIS 解决方案并使用模糊查找。 但是，如果您当前正在使用 Access，那么您的办公室购买 SQL Server Enterprise 版本的可能性对我来说似乎很低。 如果您被当前的环境困住，您可以尝试暴力方法。

从地址的标准化清理开始。 选择 Street、raod 等的标准缩写，并编写代码以将所有正常变体更改为这些标准地址。 将任何两个空格替换为一个空格，修剪所有数据并删除所有非字母数字字符。 正如您所看到的，这是一项艰巨的任务。

至于公司名称，也许您可​​以尝试将名称的前 5 个字符与地址或电话进行匹配。 您还可以创建一个已知变体表以及它们在数据库中的相关内容，以用于清理未来的文件。 因此，如果您记录的 id 100 是 Acme, Inc.，您可能会得到一个如下表：

idfield Name

100 Acme, Inc.

100 Acme, Inc

100 Acme, Incorporated

100 Acme, LLC

100 Acme

这将开始很小，但会随着时间的推移而建立，如果每次找到并修复重复项（使其成为重复数据删除过程的一部分）时，以及每次能够将名称和地址的第一部分与现有公司相匹配时，您都会进行输入。

我还会查看 Torial 发布的该功能，看看它是否有帮助。

所有这些都将是痛苦且耗时的，但随着时间的推移，当您发现新的变体并将它们添加到代码或列表中时，情况会变得更好。 如果您决定对地址数据进行标准化，请确保首先清理生产数据，然后对工作表进行任何导入并清理它，然后尝试匹配生产数据并插入新记录。

Answer 2

有许多供应商提供进行此类模式匹配的产品。 我会做一些研究并找到一个好的、信誉良好的产品并废弃本土系统。

正如您所说，您的产品只是好的，这是企业的普遍需求，我确信那里有不止一种优秀的产品。 即使许可证花费几千美元，它仍然比付钱给一群开发人员在内部开发东西要便宜。

此外，“复杂”、“CPU 密集型”、“VBA 代码”和“Access 数据库”等短语同时出现在系统描述中，这一事实也是寻找优秀第三方工具的另一个原因。

编辑：.NET 也可能有一个内置组件可以执行此类操作，在这种情况下您无需为此付费。 我仍然偶尔会对 .NET 提供的工具感到惊讶。

Answer 3

这是我为几乎相同的堆栈编写的内容（我们需要标准化硬件的制造商名称，并且存在各种变化）。 不过，这是客户端（确切地说是 VB.Net）——并使用 Levenshtein 距离算法（经过修改以获得更好的结果）：

    Public Shared Function FindMostSimilarString(ByVal toFind As String, ByVal ParamArray stringList() As String) As String
        Dim bestMatch As String = ""
        Dim bestDistance As Integer = 1000 'Almost anything should be better than that!

        For Each matchCandidate As String In stringList
            Dim candidateDistance As Integer = LevenshteinDistance(toFind, matchCandidate)
            If candidateDistance < bestDistance Then
                bestMatch = matchCandidate
                bestDistance = candidateDistance
            End If
        Next

        Return bestMatch
    End Function

    'This will be used to determine how similar strings are.  Modified from the link below...
    'Fxn from: http://ca0v.terapad.com/index.cfm?fa=contentNews.newsDetails&newsID=37030&from=list
    Public Shared Function LevenshteinDistance(ByVal s As String, ByVal t As String) As Integer
        Dim sLength As Integer = s.Length ' length of s
        Dim tLength As Integer = t.Length ' length of t
        Dim lvCost As Integer ' cost
        Dim lvDistance As Integer = 0
        Dim zeroCostCount As Integer = 0

        Try
            ' Step 1
            If tLength = 0 Then
                Return sLength
            ElseIf sLength = 0 Then
                Return tLength
            End If

            Dim lvMatrixSize As Integer = (1 + sLength) * (1 + tLength)
            Dim poBuffer() As Integer = New Integer(0 To lvMatrixSize - 1) {}

            ' fill first row
            For lvIndex As Integer = 0 To sLength
                poBuffer(lvIndex) = lvIndex
            Next

            'fill first column
            For lvIndex As Integer = 1 To tLength
                poBuffer(lvIndex * (sLength + 1)) = lvIndex
            Next

            For lvRowIndex As Integer = 0 To sLength - 1
                Dim s_i As Char = s(lvRowIndex)
                For lvColIndex As Integer = 0 To tLength - 1
                    If s_i = t(lvColIndex) Then
                        lvCost = 0
                        zeroCostCount += 1
                    Else
                        lvCost = 1
                    End If
                    ' Step 6
                    Dim lvTopLeftIndex As Integer = lvColIndex * (sLength + 1) + lvRowIndex
                    Dim lvTopLeft As Integer = poBuffer(lvTopLeftIndex)
                    Dim lvTop As Integer = poBuffer(lvTopLeftIndex + 1)
                    Dim lvLeft As Integer = poBuffer(lvTopLeftIndex + (sLength + 1))
                    lvDistance = Math.Min(lvTopLeft + lvCost, Math.Min(lvLeft, lvTop) + 1)
                    poBuffer(lvTopLeftIndex + sLength + 2) = lvDistance
                Next
            Next
        Catch ex As ThreadAbortException
            Err.Clear()
        Catch ex As Exception
            WriteDebugMessage(Application.StartupPath , [Assembly].GetExecutingAssembly().GetName.Name.ToString, MethodBase.GetCurrentMethod.Name, Err)
        End Try

        Return lvDistance - zeroCostCount
    End Function

Answer 4

SSIS（在Sql 2005+ Enterprise中）具有模糊查找，其设计对于这样的数据清理问题。

除此之外，我只知道特定于域的解决方案 - 例如 地址清理 ，或一般字符串匹配技术。

Answer 5

有很多方法可以解决这个问题，但这些方法可能并不明显。 最好的是找到唯一的标识符，您可以使用它来匹配字段之外的拼写错误等。

一些想法

1. 显而易见，社会安全号码、驾驶执照等
2. 电子邮件地址
3. 清理后的电话号码（删除标点符号等）

就供应商而言我刚刚回答了类似的问题并粘贴在下面。

每个主要提供商都有自己的解决方案。 Oracle、IBM、SAS Dataflux 等公司都声称自己最擅长解决此类问题。

独立验证评估：

澳大利亚科廷大学数据链接中心进行了一项研究，模拟了 440 万条记录的匹配。 确定提供商的准确性（找到的匹配数与可用的匹配数。错误匹配数）

DataMatch Enterprise，< /a> 准确度最高 (>95%)、速度非常快、成本低

IBM Quality Stage，高精度 (>90%)、非常快、高成本 (>100K 美元)

SAS Data Flux、中等精度 (>85%)、快速、高成本 (>100K) ）
这是我们能找到的最好的独立评估，非常彻底。