sql - 从全名获取名字的逻辑和查询

Question

我有一个带有全名字段的大型数据库。全名可以采用任何格式，还可以包括标题。例如，以下所有情况都是可能的:

John Smith
Smith, John
Mr. John Smith
Dr. John Smith
Mrs. Jane Smith
Ms. Jane Smith
Jane Smith, Esq.
Jane Smith, MD

我想保留全名字段，但还要从单独的表(包含姓名、性别)中添加一个预测的名字字段。

我认为正确的逻辑是通过 LIKE 将名字值 + 空格匹配到全名表。空格使得“David Johnson”与“John”不匹配。

我认为完成此任务的方法是在其中包含子查询的更新语句。这是我到目前为止所拥有的:

UPDATE "employees" 
    SET "employees".FirstName = (SELECT  firstname 
                                 FROM genders 
                                 WHERE fullname LIKE '%"employees".FirstName %')

Answer 1

您真正想做的是使用 Postgres 的全文搜索功能。您可以创建一个包含要排除的标题(先生、女士等)的停用词列表。然后，设置搜索配置以使用您的停用词。

一旦您正确设置了搜索配置，您的查询将如下所示(这是 SELECT 变体:更改为 UPDATE 将很简单):

SELECT employees.full_name, genders.first_name 
FROM employees
    LEFT JOIN genders ON
        TO_TSVECTOR('english_titles', employees.full_name) 
            @@ TO_TSQUERY('english_titles', genders.first_name)

这将为您提供以下结果:

full_name           first_name          
"John Smith"        "John"
"Smith, John"       "John"
"Mr. John Smith"    "John"
"Dr. John Smith"    "John"
"Mrs. Jane Smith"   "Jane"
"Ms. Jane Smith"    "Jane"
"Jane Smith, Esq."  "Jane"
"Jane Smith, MD"    "Jane"
"David Johnson"     NULL

为了使其正常工作，您需要执行以下步骤:

1. 创建一个包含职位名称的停用词文件，并将其放入 $SHAREDIR/tsearch_data Postgres 目录中。参见 https://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html#TEXTSEARCH-STOPWORDS .
2. 创建一个使用这个停用词列表的字典(您可以使用 pg_catalog.simple 作为您的模板字典)。参见 https://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html#TEXTSEARCH-SIMPLE-DICTIONARY .
3. 创建职位搜索配置。参见 https://www.postgresql.org/docs/9.1/static/textsearch-configuration.html .
4. 更改您的搜索配置以使用您在第 2 步中创建的词典(参见上面的链接)。

现在，综上所述，您需要仔细考虑几件事:

• 您希望如何处理在您的 Genders 表中姓与名匹配的人？例如，您有一个名为 John Stuart 的人，John 和 Stuart 都在您的 genders 表中。您希望如何处理？
• 您希望如何处理有昵称或只有一个名字的人？我会强烈鼓励您阅读Falsehoods Programmers Believe About Names以确保您没有做出任何没有根据的假设。
• 为什么您的表包含名为genders 的第一个名字？您是否希望按性别将人们与名字相匹配？如果是这样，那将是一条危险的道路——有些名字可以用于任何一种性别。