文章/答案/技术大牛

发布

社区首页 >问答首页 >SQL中的模糊分组

问SQL中的模糊分组
EN

Stack Overflow用户

提问于 2013-05-09 03:05:11

回答 2查看 6.3K关注 0票数 4

我需要修改一个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

在本例中，我选择了第一个名字作为“标准化名称”，但实际上我并不关心选择哪个名字--最终的“标准化名称”将被散列到一个惟一的person ID中。(我也可以选择直接使用数字ID的替代解决方案。)我也会有生日来匹配，所以在实践中名字匹配的准确性实际上并不需要那么精确。我已经对此进行了一些研究，可能会使用Jaro-Winkler算法(参见例如here)。

如果我知道所有的名字都是成对的，这将是一个相对简单的查询，但是可以有任意多个相同的名字。

我可以很容易地概念化如何在过程化语言中执行此查询，但我不太熟悉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']。

sql

sql-server

回答 2

Stack Overflow用户

回答已采纳

发布于 2013-05-09 11:29:16

假设您从SSC复制并粘贴了jaro-winkler实现(需要注册)，则以下代码将起作用。我试图为它构建一个SQLFiddle，但当我构建模式时，它一直在崩溃。

这个实现有一个骗局-我使用的是游标。通常，游标不利于性能，但在这种情况下，您需要能够将集合与自身进行比较。可能有一种优雅的number/tally table方法来消除声明的游标。

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上，我谈到了我的experience matching people。在本节中，我将列出一些需要注意的事项。

速度

作为配对的一部分，万岁，因为你有一个生日来增加配对过程。实际上，我建议您首先根据出生日期生成匹配。这是一个精确的匹配，如果有适当的索引，SQL Server将能够快速包括/排除行。因为你会需要它的。TSQL实现速度很慢。我已经在一个包含28k个名字的数据集上运行了等价的匹配(这些名字已经被列为会议参与者)。那里应该有一些很好的重叠，虽然我确实用数据填充了@src，但它是一个table variable with all that that implies，但它已经运行了15分钟，仍然没有完成。

它很慢，原因有很多，但我突然想到的是函数中所有的循环和字符串操作。这不是SQL Server的闪光点。如果你需要做很多这样的事情，把它们转换成CLR方法可能是个好主意，这样至少你可以利用.NET库的强大功能来做一些操作。

我们过去使用的匹配之一是Double Metaphone，它会为该名称生成一对可能的语音解释。而不是每次都计算，只计算一次，并将其与名称一起存储。这将有助于加快一些匹配。不幸的是，JW看起来并不愿意这样分解它。

也可以看看迭代。我们首先会尝试我们知道速度快的algs。'John‘= 'John’所以没有必要拿出大炮，所以我们会尝试第一次通过直接的名字检查。如果我们找不到匹配的，我们会更努力。我们的希望是，通过在匹配时进行各种滑动，我们将尽可能快地获得低垂的果实，并担心以后更难的匹配。

名字

在我的SU答案和代码注释中，我提到了昵称。比尔和比利将配对。比利，利亚姆和威廉肯定不会匹配，即使他们可能是同一个人。您可能希望查看类似这样的列表，以提供nickname and full name之间的转换。在对提供的名称运行了一组匹配之后，我们可能会尝试根据可能的根名称来查找匹配。

显然，这种方法有其不足之处。例如，我的公公是麦克斯。只有麦克斯。不是马西米利安，马西穆斯或者其他任何你想要的东西。

您提供的名称看起来像是第一个和最后一个连接在一起。未来的读者，如果你有机会捕捉一个名字的个别部分，请这样做。有一些产品会将名字拆分，并尝试将它们与目录进行匹配，以尝试猜测某个东西是名字/中间名还是姓氏，但你会有像"Robar Mike“这样的人。如果你在那里看到这个名字，你会认为Robar是一个姓氏，你也会把它读成“robber”。相反，Robar (用法国口音说)是他的名字，Mike是他的姓。无论如何，我认为如果您可以将第一个和最后一个拆分到单独的字段中，并将各个部分匹配在一起，您将获得更好的匹配体验。一个精确的姓氏匹配加上一个部分的名字匹配可能就足够了，特别是在法律上他们是"Franklin Roosevelt“的情况下，你有一个候选人"F. Roosevelt”也许你有一个规则，首字母可以匹配。或者你不知道。

噪音-在JW帖子和我的答案中提到，为了匹配的目的，去掉垃圾(标点符号，停用的单词等)。还要注意敬语(phd，jd等)和代称(II，III，JR，SR)。我们的规则是，具有/不具有代际的候选人可以匹配相反州的候选人(Bob Jones Jr == Bob Jones)，也可以完全匹配代际(Bob Jones Sr = Bob Jones Sr)，但是如果两个记录都提供了它们，并且它们是冲突的(Bob Jones Sr = Bob Jones Jr)，那么您永远不会想要匹配。

要区分大小写，请始终检查您的数据库和tempdb，以确保您没有进行区分大小写的匹配。如果是这样的话，为了匹配的目的，把所有东西都转换成大写或小写，但不要扔掉附带的大写字母。祝你好运，尝试确定latessa应该是Latessa，LaTessa还是其他什么。

我的查询将进行一个小时的处理，没有返回任何行，所以我将终止它并返回。祝你好运，结婚快乐。

票数 6

Stack Overflow用户

发布于 2013-05-09 03:12:40

这只是一个想法，但是您也许能够使用SOUNDEX()函数。这将为names创建一个类似的值。

如果你是这样开始的：

select name, soundex(name) snd,
  row_number() over(partition by soundex(name)
                    order by soundex(name)) rn
from yt;

参见SQL Fiddle with Demo。它将给出每个相似行的结果以及一个row_number()，这样您就可以只返回每个组的第一个值。例如，上面的查询将返回：

|    NAME |  SND | RN |
-----------------------
|   Jon Q | J500 |  1 |
|  John Q | J500 |  2 |
|  Jonn Q | J500 |  3 |
|  Matt H | M300 |  1 |
|  Mary W | M600 |  1 |
| Marie W | M600 |  2 |

然后，您可以从该结果中选择row_number()等于1的所有行，然后根据soundex(name)值连接回您的主表：

select t1.name,
  t2.Stdname
from yt t1
inner join
(
  select name as stdName, snd, rn
  from
  (
    select name, soundex(name) snd,
      row_number() over(partition by soundex(name)
                        order by soundex(name)) rn
    from yt
  ) d
  where rn = 1
) t2
  on soundex(t1.name) = t2.snd;

参见SQL Fiddle with Demo。这会给出一个结果：

|    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 |

票数 6

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/16448297

复制

相似问题

问SQL中的模糊分组
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL中的模糊分组EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL中的模糊分组
EN