postgresql - PostgreSQL：匹配通用名称变体（昵称）

Question

设想

我有许多企业数据集，我必须在它们之间找到缺失的链接，而我用来查找潜在匹配项的方法之一是加入名字和姓氏。复杂之处在于，我们有大量的人在一个数据集（员工记录）中使用他们的法定姓名，但他们在其他数据集中使用昵称或（更糟糕的是）他们的中间名（即 EAD、培训、PIV 卡、 ETC。）。我正在寻找一种方法来匹配各种数据集中这些可能不同的名称。

简化示例

这是我正在尝试做的一个过于简化的示例，但我认为它传达了我的思维过程。我从员工表开始：

员工表

员工ID	名	姓
052451	罗伯特	阿姆斯登
442896	雅各布	克拉克斯福德
054149	授予	基廷
025747	加布里埃尔	伦顿
071238	玛格丽特	赛芬马赫

并尝试从 PIV 卡数据集中查找匹配数据：

牌桌

card_id	名	姓
1008571527	鲍比	阿姆斯登
1009599982	杰克	克拉克斯福德
1004786477	加比	伦顿
1000628540	玛吉	赛芬马赫

期望的结果

在尝试将这些数据集与名字和姓氏进行匹配后，我想得到以下结果：

Employees_Cards 表

emp_employee_id	emp_first_name	emp_last_name	crd_card_id	crd_first_name	crd_last_name
052451	罗伯特	阿姆斯登	1008571527	鲍比	阿姆斯登
442896	雅各布	克拉克斯福德	1009599982	杰克	克拉克斯福德
054149	授予	基廷	无效的	无效的	无效的
025747	加布里埃尔	伦顿	1004786477	加比	伦顿
071238	玛格丽特	赛芬马赫	1000628540	玛吉	赛芬马赫

如您所见，我想进行以下匹配：

Gabrielle -> Gabi
Jacob -> Jacob
Margaret -> Maggy
Robert -> Bobbie

我最初的想法是找到一个通用名称数据集，如下所示：

Name_Aliases 表

名称1	名称2	名称3	名称4
加布里埃尔	加比	无效的	无效的
雅各布	杰克	无效的	无效的
玛格丽特	玛吉	劣质煤	梅格
迈克尔	麦克风	米奇	米克
罗伯特	鲍比	鲍勃	抢

并为 JOIN 使用类似的东西：

CREATE TABLE employee_cards AS
    SELECT 
        employees.employee_id AS emp_employee_ID,
        employees.first_name AS emp_first_name,
        employees.last_name AS emp_last_name,
        cards.card_id AS crd_card_id,
        cards.first_name AS crd_first_name,
        cards.last_name AS crd_last_name
    FROM employees
    LEFT OUTER JOIN name_aliases
    LEFT OUTER JOIN cards
        ON employees.first_name IN (
            nane_aliases.name1,
            nane_aliases.name2,
            nane_aliases.name3,
            nane_aliases.name4
        )
        AND employees.last_name = cards.last_name;

这就是我卡住的地方，因为我不知道如何将第一个 ON 条件的结果与卡表中的名字联系起来。

一些问题

当我更深入地思考这个问题时，我知道我不是第一个遇到这种需要匹配常见名称变体的人。我最初的搜索将我指向诸如fuzzysearchand之类的东西soundex，但这些并不是我当前场景所需要的（尽管它们可能会在路上派上用场）。鉴于此，我有几个问题要问社区：

可下载的通用名称变体数据集？

name-aliases是否有人按照我上面的表格编译或众包了一个全面的名称变体数据集？我的搜索使我进入了几个似乎具有此类数据的站点，但没有一个站点可下载以导入我的本地数据库。

我确实发现这个 SO 讨论已有十多年了，但它似乎不是最新的：数据库的常用名称别名/人的昵称

另外，我无法为此支付任何费用，所以我希望可能有一个隐藏在 github 上。

构造 Name_Aliases 表的更好方法？

由于 name_aliases 表中的每条记录都可以有两个或更多条目，有没有更好的方法来设置这个表的结构，使其无限灵活？

匹配 Name_Aliases 表中的任何列？

如何设置 JOIN 查询以将employees.first_name 与name_alises 中的任何列匹配，然后最终将其与cards.first_name 匹配？

更好的解决方案？

我是否采取了错误的方法来解决这个问题？有没有人想出一个更灵活和优雅的方法来使用 PostgreSQL？

标签： postgresqlpattern-matching

Answer 1

解决方案

这是一场激烈的战斗，但我能够得到所有问题的答案，我很高兴终于让这个项目的一切顺利进行。详情如下。

昵称数据集

我在 Github 上发现了几个很有前途的昵称数据集，这个看起来是维护最积极的：https ://github.com/carltonnorthern/nickname-and-diminutive-names-lookup 。我将 names.csv 文件下载到我的计算机并使用以下代码将其导入到我的数据库中：

导入脚本

DROP TABLE IF EXISTS names_aliases_temp;
CREATE TABLE names_aliases_temp
(
   names_data text
);

COPY names_aliases_temp
FROM '~/Downloads/names.csv';

DROP TABLE IF EXISTS names_aliases;
CREATE TABLE names_aliases
(
   id serial,
   nicknames text[]
);

INSERT INTO names_aliases (nicknames)
SELECT string_to_array(names_data,',') FROM names_aliases_temp;

DROP TABLE IF EXISTS names_aliases_temp;

CREATE INDEX idx_gin_names ON names_aliases USING GIN(nicknames);

Names_Aliases 数据集

这是导入数据库后的示例：

ID	昵称
1	{亚伦，艾琳，罗尼，罗恩}
2	{阿比盖尔，纳比，艾比，盖尔}
3	{abednego，贝德尼}
4	{abel,ebbie,ab,abe,eb}
5	{abiel,ab}
6	{阿比盖尔，纳比，艾比，盖尔}
7	{abijah,ab,bige}
8	{abner,ab}
9	{亚伯拉罕,ab,abe}
10	{亚伯兰,ab, abe}

使用数组的注意事项

我很高兴进入数组格式是多么容易，但更让我兴奋的是我能够在 JOIN 查询中使用数组格式！由于每行的条目数变化很大，我发现array数据类型使其自然适合此数据，这也使得使用ILIKE ANY()运算符匹配记录变得非常容易。

JOIN 查询脚本

DROP TABLE IF EXISTS employee_cards;

CREATE TABLE employee_cards AS
WITH joined_data AS (
    SELECT
        employees.employee_id AS emp_id,
        employees.first_name  AS emp_first_name,
        employees.last_name   AS emp_last_name,
        cards.first_name      AS crd_first_name,
        cards.last_name       AS crd_last_name,
        cards.card_id         AS crd_id
    FROM employees
        -- Attempt to match first names with nicknames
        LEFT JOIN names_aliases
            ON employees.first_name ILIKE ANY(names_aliases.nicknames)
        LEFT JOIN cards
            -- First match records where name is the same between `employees` and `cards`
            ON (employees.last_name ILIKE cards.last_name
               AND employees.first_name ILIKE cards.first_name)
            -- Then bring in nicknames where no matches are found
            OR (employees.last_name ILIKE cards.last_name
                AND cards.first_name ILIKE ANY(names_aliases.nicknames))
)
-- Put successful matches at the top for each employee and retain only the first row
SELECT DISTINCT ON (emp_id)
    emp_id,
    emp_first_name,
    emp_last_name,
    crd_first_name,
    crd_last_name,
    crd_id
FROM joined_data
ORDER BY
    emp_id, 
    crd_id NULLS LAST;

使用的表

为方便起见，本练习中使用的三个表格如下所示。

员工表

员工ID	名	姓
052451	罗伯特	阿姆斯登
022448	迈克尔	棕色的
442896	雅各布	克拉克斯福德
054149	授予	基廷
025747	加布里埃尔	伦顿
425972	财团	雷亚斯
071238	玛格丽特	赛芬马赫

insert into public.employees (employee_id, first_name, last_name)
values  ('052451', 'Robert', 'Armsden'),
        ('022448', 'Michael', 'Brown'),
        ('442896', 'Jacob', 'Craxford'),
        ('054149', 'Grant', 'Keeting'),
        ('025747', 'Gabrielle', 'Renton'),
        ('425972', 'Consorcia', 'Reyas'),
        ('071238', 'Margaret', 'Seifenmacher');

牌桌

card_id	名	姓
1008571527	鲍勃	阿姆斯登
1000594085	迈克尔	棕色的
1009599982	杰克	克拉克斯福德
1004786477	饶舌	伦顿
1009481574	财团	雷亚斯
1000628540	玛吉	赛芬马赫

insert into public.cards (card_id, first_name, last_name)
values  ('1008571527', 'Bob', 'Armsden'),
        ('1000594085', 'Michael', 'Brown'),
        ('1009599982', 'Jake', 'Craxford'),
        ('1004786477', 'Gabby', 'Renton'),
        ('1009481574', 'Consorcia', 'Reyas'),
        ('1000628540', 'Maggy', 'Seifenmacher');

员工卡（已加入）表

emp_id	emp_first_name	emp_last_name	crd_first_name	crd_last_name	crd_id
052451	罗伯特	阿姆斯登	鲍勃	阿姆斯登	1008571527
022448	迈克尔	棕色的	迈克尔	棕色的	1000594085
442896	雅各布	克拉克斯福德	杰克	克拉克斯福德	1009599982
054149	授予	基廷	无效的	无效的	无效的
025747	加布里埃尔	伦顿	饶舌	伦顿	1004786477
425972	财团	雷亚斯	财团	雷亚斯	1009481574
071238	玛格丽特	赛芬马赫	玛吉	赛芬马赫	1000628540

insert into public.employee_cards (emp_id, emp_first_name, emp_last_name, crd_first_name, crd_last_name, crd_id)
values  ('052451', 'Robert', 'Armsden', 'Bob', 'Armsden', '1008571527'),
        ('022448', 'Michael', 'Brown', 'Michael', 'Brown', '1000594085'),
        ('442896', 'Jacob', 'Craxford', 'Jake', 'Craxford', '1009599982'),
        ('054149', 'Grant', 'Keeting', null, null, null),
        ('025747', 'Gabrielle', 'Renton', 'Gabby', 'Renton', '1004786477'),
        ('425972', 'Consorcia', 'Reyas', 'Consorcia', 'Reyas', '1009481574'),
        ('071238', 'Margaret', 'Seifenmacher', 'Maggy', 'Seifenmacher', '1000628540');