模糊匹配SQL中的字符串

模糊匹配SQL中的字符串

本文介绍了模糊匹配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_namebut 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中的字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-27 05:58