sql - 姓名姓氏相似度搜索

Question

我有一列 name，其中包含 name surname(命名空间姓氏)，我想根据

搜索它

• name, surname 但我想匹配人们不小心以不同顺序插入 surname 的情况
• 拼错了 1-2 个字符的 names surnames。

Answer 1

您应该阅读 pg_trgm extension及其函数similarity()。下面是几个例子。

示例数据:

create table my_table(id serial primary key, name text);
insert into my_table (name) values
('John Wilcock'),
('Henry Brown'),
('Jerry Newcombe');

create extension if not exists pg_trgm; -- install the extension

示例 1:

select *, 
    similarity(name, 'john wilcock') as "john wilcock",
    similarity(name, 'wilcock john') as "wilcock john"
from my_table;

 id |      name      | john wilcock | wilcock john 
----+----------------+--------------+--------------
  1 | John Wilcock   |            1 |            1
  2 | Henry Brown    |            0 |            0
  3 | Jerry Newcombe |     0.037037 |     0.037037
(3 rows)

示例 2:

select *, 
    similarity(name, 'henry brwn') as "henry brwn",
    similarity(name, 'brovn henry') as "brovn henry"
from my_table;

 id |      name      | henry brwn | brovn henry 
----+----------------+------------+-------------
  1 | John Wilcock   |          0 |           0
  2 | Henry Brown    |   0.642857 |         0.6
  3 | Jerry Newcombe |       0.04 |   0.0384615
(3 rows)

示例 3:

select *
from my_table
where similarity(name, 'J Newcombe') >= 0.6;

 id |      name      
----+----------------
  3 | Jerry Newcombe
(1 row)