SQL中的模糊分组

Question

我需要修改一个SQL表以将名称稍有不匹配的分组，并为该分组中的所有元素分配一个标准化名称。

例如，如果初始表如下所示：

Name
--------
Jon Q
John Q
Jonn Q
Mary W
Marie W
Matt H

我想创建一个新表或向现有表中添加一个字段，如下所示：

Name     | StdName
--------------------
Jon Q    | Jon Q
John Q   | Jon Q
Jonn Q   | Jon Q
Mary W   | Mary W
Marie W  | Mary W
Matt H   | Matt H

在这种情况下，我选择了要分配的第一个名称作为“标准名称”，但是我实际上并不在乎选择哪个名称-
最终，最终的“标准名称”将被散列为唯一的人员ID。（我也欢迎直接使用数字ID的替代解决方案。）我也要匹配出生日期，因此实际上，名称匹配的准确性并不需要那么精确。我对此进行了研究，可能会使用Jaro-
Winkler算法（请参见例如此处）。

如果我知道这些名称都是成对的，那么这将是一个相对容易的查询，但是可以有任意数量的相同名称。

我可以轻松地概念化如何使用过程语言进行此查询，但是我对SQL不太熟悉。不幸的是，我无法直接访问数据-
它是敏感数据，因此其他人（一个官僚）必须为我运行实际查询。具体的实现将是SQL Server，但我更喜欢与实现无关的解决方案。

编辑：

在回应评论时，我想到了以下程序方法。它是在Python中编写的，为了简单起见，我用简单匹配名称的第一个字母替换了Jaro-Winkler。

nameList = ['Jon Q', 'John Q', 'Jonn Q', 'Mary W', 'Marie W', 'Larry H']
stdList = nameList[:]

# loop over all names
for i1, name1 in enumerate(stdList):

  # loop over later names in list to find matches
  for i2, name2 in enumerate(stdList[i1+1:]):

    # If there's a match, replace latter with former.
    if (name1[0] == name2[0]):
      stdList[i1+1+i2] = name1

print stdList

结果是['Jon Q', 'Jon Q', 'Jon Q', 'Mary W', 'Mary W', 'Larry H']。

Answer 1

假设您从SSC复制并粘贴jaro-
winkler
实现（需要注册），则以下代码将起作用。我尝试为其构建一个SQLFiddle，但是在构建架构时，它一直在不断发展。

这个实现有一个作弊行为-
我正在使用游标。通常，游标不利于性能，但是在这种情况下，您需要能够将集合与自身进行比较。可能有一种优美的数字/提示表方法可以消除声明的游标。

DECLARE @SRC TABLE
(
    source_string varchar(50) NOT NULL
,   ref_id int identity(1,1) NOT NULL
);

-- Identify matches
DECLARE @WORK TABLE
(
    source_ref_id int NOT NULL
,   match_ref_id int NOT NULL
);

INSERT INTO
    @src
SELECT 'Jon Q'
UNION ALL SELECT 'John Q'
UNION ALL SELECT 'JOHN Q'
UNION ALL SELECT 'Jonn Q'
-- Oops on matching joan to jon
UNION ALL SELECT 'Joan Q'
UNION ALL SELECT 'june'
UNION ALL SELECT 'Mary W'
UNION ALL SELECT 'Marie W'
UNION ALL SELECT 'Matt H';

-- 2 problems to address
-- duplicates in our inbound set
-- duplicates against a reference set
--
-- Better matching will occur if names are split into ordinal entities
-- Splitting on whitespace is always questionable
--
-- Mat, Matt, Matthew

DECLARE CSR CURSOR
READ_ONLY
FOR 
SELECT DISTINCT
    S1.source_string
,   S1.ref_id
FROM
    @SRC AS S1
ORDER BY
    S1.ref_id;

DECLARE @source_string varchar(50), @ref_id int
OPEN CSR

FETCH NEXT FROM CSR INTO @source_string, @ref_id
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        IF NOT EXISTS
        (
            SELECT * FROM @WORK W WHERE W.match_ref_id = @ref_id
        )
        BEGIN
            INSERT INTO
                @WORK
            SELECT
                @ref_id
            ,   S.ref_id
            FROM
                @src S
                -- If we have already matched the value, skip it
                LEFT OUTER JOIN
                    @WORK W
                    ON W.match_ref_id = S.ref_id
            WHERE
                -- Don't match yourself
                S.ref_id <> @ref_id
                -- arbitrary threshold, will need to examine this for sanity
                AND dbo.fn_calculateJaroWinkler(@source_string, S.source_string) > .95
        END
    END
    FETCH NEXT FROM CSR INTO @source_string, @ref_id
END

CLOSE CSR

DEALLOCATE CSR

-- Show me the list of all the unmatched rows 
-- plus the retained

;WITH MATCHES AS
(
    SELECT 
        S1.source_string
    ,   S1.ref_id
    ,   S2.source_string AS match_source_string
    ,   S2.ref_id AS match_ref_id
    FROM 
        @SRC S1
        INNER JOIN
            @WORK W
            ON W.source_ref_id = S1.ref_id
        INNER JOIN
            @SRC S2
            ON S2.ref_id = W.match_ref_id
)
, UNMATCHES AS
(
    SELECT 
        S1.source_string
    ,   S1.ref_id
    ,   NULL AS match_source_string
    ,   NULL AS match_ref_id
    FROM 
        @SRC S1
        LEFT OUTER JOIN
            @WORK W
            ON W.source_ref_id = S1.ref_id
        LEFT OUTER JOIN
            @WORK S2
            ON S2.match_ref_id = S1.ref_id
    WHERE
        W.source_ref_id IS NULL
        and s2.match_ref_id IS NULL
)
SELECT
    M.source_string
,   M.ref_id
,   M.match_source_string
,   M.match_ref_id
FROM
    MATCHES M
UNION ALL
SELECT
    M.source_string
,   M.ref_id
,   M.match_source_string
,   M.match_ref_id
FROM
    UNMATCHES M;

-- To specifically solve your request

SELECT
    S.source_string AS Name
,   COALESCE(S2.source_string, S.source_string) As StdName
FROM
    @SRC S
    LEFT OUTER JOIN
        @WORK W
        ON W.match_ref_id = S.ref_id
    LEFT OUTER JOIN
        @SRC S2
        ON S2.ref_id = W.source_ref_id

查询输出1

source_string   ref_id  match_source_string match_ref_id
Jon Q   1   John Q  2
Jon Q   1   JOHN Q  3
Jon Q   1   Jonn Q  4
Jon Q   1   Joan Q  5
june    6   NULL    NULL
Mary W  7   NULL    NULL
Marie W 8   NULL    NULL
Matt H  9   NULL    NULL

查询输出2

Name    StdName
Jon Q   Jon Q
John Q  Jon Q
JOHN Q  Jon Q
Jonn Q  Jon Q
Joan Q  Jon Q
june    june
Mary W  Mary W
Marie W Marie W
Matt H  Matt H

有龙

在SuperUser上，我谈到了与人打交道的经验。在本节中，我将列出一些注意事项。

作为匹配的一部分，万岁，您必须过生日才能扩大匹配过程。实际上，我建议您首先完全根据出生日期生成一个匹配项。这是一个完全匹配，并且具有正确的索引，SQL
Server将能够快速包含/排除行。因为您将需要它。TSQL的实现是缓慢的。我一直在对28k个名字（被列为会议参加者的名字）的数据集进行等效匹配。那里应该有一些良好的重叠，尽管我确实用数据填充@src，但它是一个表变量，包含所有隐含的含义，但是它已经运行了15分钟，但尚未完成。

由于许多原因，它很慢，但是让我惊讶的是函数中的所有循环和字符串操作。那不是SQL
Server的亮点。如果您需要做很多事情，将它们转换为CLR方法可能是个好主意，因此至少您可以利用.NET库的优势来进行某些操作。

我们以前使用的匹配项之一是Double
Metaphone，它将生成该名称的一对可能的语音解释。与其每次都进行计算，不如一次计算并将其存储在名称旁边。这将有助于加快某些匹配速度。不幸的是，看起来JW并不能像这样分解它。

看看迭代。我们首先尝试一下我们所知道的快速Algs。’John’=’John’，因此无需掏出大把枪，因此我们将尝试直通姓名的第一遍检查。如果找不到匹配项，我们会更加努力。希望通过在配对过程中进行各种滑动，我们将尽快获得低挂果，并在以后担心较难的匹配。

名字

在我的SU答案和代码注释中，我提到了昵称。比尔和比利将要比赛。比利，利亚姆和威廉绝对 不会
配对，即使他们可能是同一个人。您可能想要查看这样的列表，以提供昵称和全名之间的转换。在提供的名称上运行了一组匹配之后，也许我们会尝试根据可能的根名称查找匹配。

显然，这种方法存在一些缺点。例如，我的外公是马克斯。只是最大。不是马克西米利安（Maximilian），马克西姆斯（Maximus）或您可能会想到的其他任何东西。

您提供的名称看起来像是第一个和最后一个串联在一起的。将来的读者，如果您有机会捕获姓名的各个部分，请这样做。有一些产品可以拆分名称，并尝试将其与目录匹配，以尝试猜测是姓氏/中间名还是姓氏，但随后您会遇到“
Robar
Mike”之类的人。如果您在该处看到该名字，您会认为Robar是姓氏，并且还会将其发音为“强盗”。取而代之的是Robar（用法国口音称呼）是他的名字，Mike是他的姓氏。无论如何，我认为如果您可以将第一个和最后一个划分为不同的字段并将各个部分匹配在一起，那么您将获得更好的匹配体验。完全相同的姓氏匹配加上部分姓氏匹配就足够了，特别是在法律上他们是“富兰克林·罗斯福”并且您有“
F.罗斯福”候选人的情况下。也许您有一个首字母可以匹配的规则。否则你不会。

噪声-
正如JW帖子和我的答案中提到的那样，出于匹配目的，请删除废话（标点，停用词等）。还请注意尊敬的什叶派（phd，jd等）和几代人（II，III，JR，SR）。我们的规则是有/无世代的候选人可以在相反的状态下匹配一个人（鲍勃·琼斯==鲍勃·琼斯），也可以与世代完全匹配（鲍勃·琼斯=鲍勃·琼斯），但是如果两条记录都提供了它们，并且它们发生了冲突（Bob
Jones Sr！= Bob Jones Jr）。

区分大小写，请始终检查数据库和tempdb，以确保未进行区分大小写的匹配。如果你是，转换一切上部或下部进行匹配的目的，但并不 永远
扔提供外壳的路程。尝试确定Latessa应该是Latessa，LaTessa还是其他东西，祝您好运。

我的查询耗费了一个小时的处理时间，没有返回任何行，因此我将杀死它并上交。祝您好运，祝您匹配愉快。