用于通过转换表进行匹配的 SQL 模式

Question

我有来自 2 个不同来源的脏数据。我正在寻找一些关于匹配它们的最佳实践。以下是一些数据示例：

Source1.Name                   Source2.Name
Adda Clevenger Jr Prep School  Adda Clevenger Junior Preparatory School
Alice Fong Yu Alt School       Alice Fong Yu Alternative School
Convent Of Sacred Heart Es     Convent of Sacred Heart Elementary School
Rosa Parks Elementary School   Rosa Parks Elementary School

人类可以看到这 4 个示例应该与理想的模糊匹配相匹配。我有很好的软件，用于传统的模糊匹配，可以捕获拼写错误和其他小的变化。但是在这个数据集中，我有大约十几个规则来管理缩写，如"Preparatory">"Prep"。我想在查询中捕获所有这些规则。(然后我将分别处理更传统的模糊性。

是否有用于处理此要求的已知 SQL 模式？它可以像学习神奇的关键字一样简单，这将解锁我搜索中的示例。这是一种"翻译表"或"缩写表"，但我只是编造了这些术语。我还没有找到被广泛接受的术语。

从

概念上讲，我的目标是从这个天真的查询开始：

/* This succeeds for 1 record and fails for 3 in the sample data set above. */
SELECT * FROM ...
WHERE Source1.Name = Source2.Name

然后将其修改为获取上面显示的所有所需匹配项的内容。我希望我可以用一些嵌套的 REPLACE 函数来暴力破解它：

/* This works for the 4 samples given */
SELECT * FROM ...
WHERE 
  REPLACE( REPLACE( REPLACE( Source1.Name, 'Preparatory', 'Prep' ), 'Alternative', 'Alt' ), 'Elementary School', 'Es' )
  = REPLACE( REPLACE( REPLACE( Source2.Name, 'Preparatory', 'Prep' ), 'Alternative', 'Alt' ), 'Elementary School', 'Es' )

这感觉不优雅。当我解释不一致的缩写时，它越来越丑陋(例如，"国际"有时是"国际"，有时是"国际"(。对于重叠的缩写来说，它不是特别平滑(例如"小学"->"Es"，但在其他情况下"学校"->"Sch"(。

其他人是如何解决这个问题的？

注意：我使用的是甲骨文。我可能会使用 REGEXP_REPLACE 而不是替换。我肯定会使用上限(或下部(来避免案例问题。但这些细节并不是问题的核心。

Answer 1

如果您有一组已知的翻译，则可以创建一个捕获这些翻译的函数。然后，您可以在表上创建一个虚拟列，该列返回其结果。然后，您可以比较虚拟列，从而简化查询：

create or replace function abbr_replace ( str varchar2 )  
  return varchar2 deterministic as
begin
  return replace( 
                   replace( 
                         replace( 
                             replace(
                               replace( lower( str ), 'preparatory', 'prep' ), 
                                 'junior', 'jr'),
                             'elementary school', 'es'), 
                         'alternative', 'alt' ), 
                     'elementary school', 'es' 
                 );
end abbr_replace;
/
create table source1 (
  name         varchar2(100),
  replace_name varchar2(100) as ( 
      cast ( abbr_replace ( name ) as varchar2(100) ) 
    )
);
create table source2 (
  name         varchar2(100),
  replace_name varchar2(100) as ( 
      cast ( abbr_replace ( name ) as varchar2(100) ) 
    )
);
insert into source1 (name) values ('Adda Clevenger Jr Prep School');
insert into source1 (name) values ('Alice Fong Yu Alt School');
insert into source1 (name) values ('Convent Of Sacred Heart Es');
insert into source1 (name) values ('Rosa Parks Elementary School');
insert into source2 (name) values ('Adda Clevenger Junior Preparatory School');
insert into source2 (name) values ('Alice Fong Yu Alternative School');
insert into source2 (name) values ('Convent of Sacred Heart Elementary School');
insert into source2 (name) values ('Rosa Parks Elementary School');
commit;
select s1.name, s2.name
from   source1 s1
join   source2 s2
on     s2.replace_name = s1.replace_name;
NAME                                               NAME                                             
-------------------------------------------------- --------------------------------------------------
Adda Clevenger Jr Prep School                      Adda Clevenger Junior Preparatory School          
Alice Fong Yu Alt School                           Alice Fong Yu Alternative School                  
Convent Of Sacred Heart Es                         Convent of Sacred Heart Elementary School         
Rosa Parks Elementary School                       Rosa Parks Elementary School

需要注意的几点：

• 必须将函数声明为 deterministic
• 您需要使用 11g 或更高才能使用它

如果你正在寻找更通用的模糊匹配，Oracle已经实现了Levenshtein Distance和Jaro-Winkler匹配算法。这些utl_match：

select s1.name, s2.name, utl_match.jaro_winkler(s1.name, s2.name) jw
from   source1 s1
join   source2 s2
on     utl_match.jaro_winkler(s1.name, s2.name) > .9;
NAME                                               NAME                                               JW
-------------------------------------------------- -------------------------------------------------- --
Adda Clevenger Jr Prep School                      Adda Clevenger Junior Preparatory School           0.904
Alice Fong Yu Alt School                           Alice Fong Yu Alternative School                   0.925
Convent Of Sacred Heart Es                         Convent of Sacred Heart Elementary School          0.902
Rosa Parks Elementary School                       Rosa Parks Elementary School                       1.000

脚本也可在 LiveSQL 上使用