模糊匹配SQL中的字符串 [英] Fuzzy matching a string in SQL
问题描述
我有一个 User 表，该表具有 id ， first_name ，姓氏，街道地址，城市，状态，邮政编码，公司， user_identifier ， created_at ， update_at 。
I have a User table, that has id, first_name, last_name, street_address, city, state, zip-code, firm, user_identifier, created_at, update_at.
此表有很多重复项，例如同一用户已作为新用户多次输入，因此示例
This table has a lot of duplication like the same users have been entered multiple times as a new user, so example
id first_name last_name street_address user_identifier
---------------------------------------------------------
11 Mary Doe 123 Main Ave M2111111
---------------------------------------------------------
21 Mary Doe 123 Main Ave M2344455
---------------------------------------------------------
13 Mary Esq Doe 123 Main Ave M1233444
我想知道是否有一种方法可以对此表进行模糊匹配
I would like to know if there is a way of doing fuzzy matching on this table.
基本上，我想找到所有具有相同名称，相同地址但可能会有细微差别的用户，也许地址相同但具有不同
Basically I would like to find all the users that have the same name, same address but can be with a slight difference, maybe the address is the same but has different apartment number, or has a middle name and the other duplicates don't.
我正在考虑创建一个新列，该列将名字，姓氏，街道地址串联在一起，然后对该列进行模糊匹配。
I was thinking to create a new column that has concatenated first_name, last_name, street_address and do a fuzzy match on that column.
我尝试了将first_name和last_name串联为 full_name
，但似乎没有赶上具有中间名的名称
I tried levenshtein distance on concatenated first_name and last_name as full_name
but doesn't seem to catch up the name that has a middle name
select * from users
where levenshtein('Mary Doe', full_name) <=1;
我正在使用Databricks和PostgreSQL。
I am using a Databricks and PostgreSQL.
谢谢！
推荐答案
在postgres中，您可以使用 fuzzystrmatch 软件包。它提供了 levenshtein 函数，该函数返回两个文本之间的距离，然后可以使用以下示例性谓词执行模糊匹配：
In postgres you can use fuzzystrmatch package. It provies a levenshtein function, that returns distance between two texts, you can then perform fuzzy matching with the following exemplary predicate:
where levenshtein(street_address, '123 Main Avex') <= 1
这将匹配所有记录，因为'123 Main Ave'和'123 Main Avex'之间的距离是1（插入1个）。
This will match all records, because the distance between '123 Main Ave' and '123 Main Avex' is 1 (1 insertion).
Of当然，这里的值 1 只是一个例子，将非常严格地执行匹配（相差一个字符）。您应该使用更大的数字，或者使用@IVO GELOV表示的数字-使用相对距离（距离除以长度）。
Of course, value 1 here is just an example and will perform matching quite strictly (difference by only one character). You should either use larger number or, what @IVO GELOV sugests - use relative distance (distance divided by the length).
这篇关于模糊匹配SQL中的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
