Шаблон 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 примера должны соответствовать идеальному нечеткому совпадению. Я имею в своем распоряжении отличное программное обеспечение для традиционного нечеткого сопоставления, которое будет ловить опечатки и другие небольшие изменения. Но в этом наборе данных у меня есть около десятка правил, регулирующих такие сокращения, как "Подготовительный" -> "Подготовительный". Я хотел бы захватить все эти правила в запросе. (Тогда я буду иметь дело с более традиционной нечеткостью отдельно.)

Существует ли хорошо известный шаблон 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").

Как другие решили это?

Примечание: я использую Oracle. Я бы скорее использовал REGEXP_REPLACE, а не REPLACE. Я бы, конечно, использовал UPPER (или LOWER), чтобы избежать проблем с делом. Но эти детали не являются ключевыми в этой проблеме.

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 внедрила алгоритмы согласования расстояния Левенштейна и Яро-Винклера. Это в 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