Предложения для запроса базы данных для имен

Ответ 1

Я придумал решение, которое работает очень хорошо, следуя рекомендациям в комментариях. В частности, @X-Zero предлагает создать таблицу Soundexes: В моем случае я могу создавать новые таблицы, но изменение существующей схемы не допускается!

Итак, мой процесс выглядит следующим образом:

• Создайте новую таблицу со столбцами: ID, token, sound и position; с первичным ключом (ID, sound, position) и дополнительным индексом (ID, sound).

• Пройдите через каждого человека в биографической таблице:

  • Объединить их имя и фамилию.

  • Измените кодовую страницу на us7ascii, поэтому акцентированные символы нормализуются. Это связано с тем, что алгоритм Soundex не работает с акцентированными символами.

  • Преобразуйте все неалфавитные символы в пробелы и рассмотрите это как границу между токенами.

  • Обозначьте эту строку и вставьте в таблицу токен (в нижнем регистре), Soundex маркера и позицию, в которую помещается токен в исходной строке; связать это с ID.

Так же:

declare
  nameString varchar2(82);
  token varchar2(40);
  posn integer;
  cursor myNames is
    select id,
           forename||' '||surname person_name
    from   mypeople;
begin
  for person in myNames
  loop
    nameString := trim(
                    utl_i18n.escape_reference(
                      regexp_replace(
                        regexp_replace(person.person_name,'[^[:alpha:]]',' '),
                        '\s+',' '),
                      'us7ascii')
                    )||' ';
    posn := 1;
    while nameString is not null
    loop
      token := substr(nameString,1,instr(nameString,' ') - 1);
      insert into personsearch values (person.id,lower(token),soundex(token),posn);
      nameString := substr(nameString,instr(nameString,' ') + 1);
      posn := posn + 1;
    end loop;
  end loop;
end;
/

Итак, например, "Siân O'Conner" получает токенизацию в "sian" (позиция 1), "o" (позиция 2) и "conner" (позиция 3), и эти три записи, с их Soundex, получают вставлен в personsearch вместе с их идентификатором.

• Чтобы выполнить поиск, мы делаем тот же процесс: обозначим критерии поиска, а затем возвращаем результаты, в которых соответствуют звуковые и относительные позиции. Мы заказываем по положению, а затем расстояние Левенштейна (ld) от исходного поиска для каждого токена, в свою очередь.

Этот запрос, например, будет искать два токена (т.е. предварительно маркированную строку поиска):

with     searchcriteria as (
         select 'john'  token1,
                'smith' token2
         from   dual)
select   alpha.id,
         mypeople.forename||' '||mypeople.surname
from     peoplesearch alpha
join     mypeople
on       mypeople.student_id = alpha.student_id
join     peoplesearch beta
on       beta.student_id = alpha.student_id
and      beta.position   > alpha.position
join     searchcriteria
on       1 = 1
where    alpha.sound = soundex(searchcriteria.token1)
and      beta.sound  = soundex(searchcriteria.token2)
order by alpha.position,
         ld(alpha.token,searchcriteria.token1),
         beta.position,
         ld(beta.token,searchcriteria.token2),
         alpha.student_id;

Для поиска по произвольному числу токенов нам нужно будет использовать динамический SQL: объединение таблицы поиска столько раз, сколько есть токенов, где поле position в объединенной таблице должно быть больше, чем position из ранее объединенной таблицы... Я планирую написать функцию для этого, а также токенизацию строки поиска, которая вернет таблицу идентификаторов. Однако я просто разместил это здесь, чтобы вы поняли:)

Как я уже сказал, это работает очень хорошо: он возвращает хорошие результаты довольно быстро. Даже поиск "Джона Смита", когда-то кэшированный сервером, занимает менее 0,2 с; вернувшись более чем на 200 рядов... Я очень доволен этим и буду искать его в производство. Единственные проблемы:

• Предварительное вычисление токенов занимает некоторое время, но это одноразовый процесс, поэтому не слишком большая проблема. Однако связанная с этим проблема заключается в том, что триггер должен быть помещен в таблицу mypeople для вставки/обновления/удаления токенов в таблицу поиска всякий раз, когда соответствующая операция выполняется на mypeople. Это может замедлить работу системы; но поскольку это должно произойти только в течение нескольких периодов в год, возможно, лучшим решением было бы перестроить таблицу поиска на плановой основе.

• Никаких операций не происходит, поэтому алгоритм Soundex соответствует только для всех токенов. Например, поиск "chris" не вернет никаких "кристеров". Возможным решением этого является только сохранение Soundex стебля маркера, но вычисление стебля - не простая проблема! Это будет будущим обновлением, возможно, с использованием механизма переноса, используемого TeX...

В любом случае, надеюсь, что это поможет:) Комментарии приветствуются!

EDIT Мое полное решение (написать и реализовать) теперь здесь, используя Metaphone и Damerau-Levenshtein Расстояние.