使用Levenshtein距离来发现相似或重复的值，清理你的数据，以及更多的东西!
使用Levenshtein距离法
这种方法可以在其他方法中使用（Soundex、LIKE语句、Regexp）来进行字符串相似性或字符串匹配 ，以确定两个元素（文本、字符串、输入）相似但不相同。
这种方法可用于各种应用，包括识别重复的内容，处理拼写错误的用户输入数据，清理客户数据，等等。
Levenshtein方法的理念是计算两个字符串之间的最小编辑距离。
但什么是 "编辑 "距离？它由三个动作组成：插入、 替换、 和 删除。这三个动作被算法赋予相同的权重，基本上是1。
为了帮助理解这些机制，这里有几个例子。
- Bigquer → Bigquery: 字母y被加在最后，所以距离将是1（我们做了1个加法）。
- music → mujic: 字母j代替了music中的字母s，距离也是1（我们做了1次替换）。
- french fries → frij: 字母 french和space被添加，字母j代替e，字母s被删除。(我们做了7次添加，1次替换，1次删除)
这种方法在比较一个完整的字符串和另一个完整的字符串时特别有效（而在比较一个句子中的关键词或比较一个句子和另一个句子时表现得不太理想）。
你可以在维基百科上找到关于该算法的进一步信息。
现在我们已经了解了理论和应用，让我们来看看我们如何在SQL中应用它。
创建一个持久化函数
你可以将UDFs定义为**持久性或临时性。**主要区别在于，持久化函数可以被多个查询重复使用和访问（这就像在你的项目中创建一个数据集），而临时函数只对你当前在编辑器中运行的查询有效。
就我个人而言，我喜欢使用一个我称之为utility的数据集，因为它允许我存储表和函数，我可以在其他数据集和查询中重复使用。
这里是我们将在BigQuery中的Javascript UDF函数。
当执行这个查询时，它将出现在你的项目结构中，如下所示。
函数出现在你指定的数据集的Routine下（图片由作者提供）
请注意，这个函数不是以版本数的形式返回实际的差异（如果我们做了1个加法，则返回1），而是在0到1的范围内返回一个值。其中1表示完全相似，0表示完全不相似。
在我们的第一个理论例子中，Bigquer → Bigquery: 字母y被添加，意味着距离为1。我们的函数将首先计算版本数除以输入的最长的字符串，在这种情况下，Bigquery有8个字符（1个添加/8个长度），这就是距离。
为了得到相似度，我们简单地反过来， 用1-（1/8） =0,875
但首先，让我们看看一个真正的SQL查询!
在SQL查询中使用该函数
现在这个函数已经被保存为一个持久的UDF，你可以通过写下它的位置来简单地调用它。在我们的例子中，它将用以下方式调用。
`datastic.utility.levenshtein`(source,target)
我们准备了几个案例来测试我们的方法所产生的相似度分数。
该查询将输出以下结果。
我们每个测试案例的Levenshtein距离的结果（图片来自作者）
在第一个案例中，增加了一个字母，这导致两个词之间的相似度为0.88!
两个极端的情况也得到了证明：在中等|中等的相同组合下，结果是相似度为1，而在法国|PIzz的相反组合下，结果是相似度为0。
一个有公开数据的真实世界的例子
我们想解决的用例是纠正我们的CRM中的用户国家数据。我们注意到，有很多拼写错误的数据，我们将使用BigQuery的公共数据来帮助我们。
这个表包含了BigQuery提供的数据。
并包含以下格式的所有国家名称。
BigQuery公共国家数据表（图片由作者提供）
现在，我们的CRM数据表包含以下信息，一个电子邮件和一个国家（显然有一些拼写错误的单词）
我们的客户数据表（图片由作者提供）
让我们试着解决我们的真实世界的用例。为此，我们将执行以下查询，这将包括四个步骤。
- 从BigQuery加载国家数据
- 加载我们的CRM数据
- 应用CROSS JOIN语句并计算我们的Levenshtein函数
- 对结果进行排序，找到最相似的国家
然后，tadam 🎉，结果如下。
Levenshtein函数在我们的CRM数据集上的结果（图片由作者提供）。
正如你所看到的，这个方法有一些注意事项，因为我们有时会因为两组字符串非常相似而返回不止一行（spainiain的情况就是如此，它与西班牙、巴林和台湾同样接近）。
我们决定使用CROSS JOINstatement来连接我们的两个数据表。这意味着我们将把CRM表中的每一行与国家表中所有可能存在的国家进行比较。在我们的例子中，我们的CRM数据有7行，我们的国家数据集有250行，这将产生7x250=1750行的结果。
通过使用QUALIFY子句对最相似的国家进行排名，这个结果就会减少，但是根据你的使用情况，CROSS JOINs解决方案将需要大量的计算。
进一步处理更大的数据集
对于我们的例子，我们使用了一个小型的CRM数据集（7行）。相比之下，我在一个800万客户的数据集上重新使用了相同的国家表（250行）， 使用BigQuery花费了大约 33秒，这仍然是相当快的。如果你要使用更大的数据集（也许是10亿行），那么我建议使用ARRAYS而不是使用JOINS，在BigQuery中，这将导致所需资源减少。
要解决的第二个困难是当我们的列文斯坦距离函数返回一个以上的结果时，选择最佳的相似性。我的建议是将Levenshtein方法与其他方法相结合，如正则表达式或Soundex，这将给相似性分数带来额外的权重，并可能允许更好的选择。
使用SQL测量BigQuery中的字符串相似性最初发表在Medium上的Towards Data Science，人们通过强调和回应这个故事继续对话。
