Русский язык - флексивный, четкий поиск затруднен. Oracle дает решение, Oracle APEX помогает реализовать его за 15 минут.
Предположим, у нас в базе данных есть коллекция статей. В статьях - слова. Мы хотим найти статьи, в которых использовано интересующее нас слово. Вариант типа
select...
where lower('%'||text||'%') like lower(:key)
- неэффективен, поскольку вероятность угадать в ключе конкретно использованную в тексте русскую словоформу весьма низка.
Поступим иначе. Сначала создадим список всех слов и идентификаторов статей, в которых они использованы. До этого отфильтруем вон все, кроме букв, цифр и пробелов и переведем это все в нижний регистр, дефисы заменим на пробелы, а многократные пробелы заменим на единичные:
select id
, regexp_replace(
regexp_replace(
lower(replace(text, '-', ' '))
, '[^a-zа-я0-9 ]', '')
, '( ){2,}', ' ') as words
from...
;
Получим множество строк, состоящих из ID записи и ее "вычищенного" текста, который, по сути, является разделенным пробелами списком интересующих нас слов.
Теперь нам необходимо разбить каждый такой список по пробелам, записать в столбец и ликвидировать повторы. Для этого мы применим метод от Тома Кайта сотоварищи, и запишем трудночитаемое SQL выражение:
select distinct regexp_substr(w.words, '[^ ]+', 1, c1.column_value) as word, id
from
(
select id, regexp_replace(regexp_replace(lower(replace(text, '-', ' ')), '[^a-zа-я0-9 ]', ''), '( ){2,}', ' ') as words
from...
where...
) w,
table(cast(multiset(select level from dual connect by level <= length(regexp_replace(w.words, '[^ ]+')) + 1) as sys.OdciNumberList)) c1
;
Суть в том, что мы средствами SQL - но SQL от Oracle! - записали как бы цикл, который последовательно пробегает наши тексты и формирует из их слов... строки базы данных! Это - прямое следствие встроенных в Oracle иерархических SQL запросов.
Теперь, имея список всех использованных слов, вычислим меры близости их от введенного пользователем ключа поиска по методам Левенштейна и Джаро-Винклера (многоточие означает сформированный выше запрос):
select id, word
, utl_match.edit_distance_similarity(lower(:P2_KEY), word) as ds_pct
, utl_match.jaro_winkler_similarity(lower(:P2_KEY), word) as jw_pct
from ( ... )
;
Внимательный читатель заметит, что у нас уже появились характерные для Oracle APEX бинды :P2_KEY. Конечно. Мы же описываем уже реализованную практическую задачу, прямо заимствуя из нее фрагменты кода. Да, это вторая страница некого сайта на Oracle APEX.
Далее нам остается только выбрать, по какому методу из двух мы будем работать, а также установить минимальный порог меры близости. Используем метод Джаро-Винклера, порог установим на значении 90%:
select...
from
(
select id, word
--, utl_match.edit_distance_similarity(lower(:P2_KEY), word) as ds_pct
, utl_match.jaro_winkler_similarity(lower(:P2_KEY), word) as jw_pct
from ( ... )
)
where jw_pct >= 90
order by jw_pct desc
;
Начинаем строить Web интерфейс в Oracle APEX (время пошло, минут 15 нужно). Окно поиска:
Чтоб облегчить пользователю жизнь, сделаем его автозаполняемым, применив внутреннюю часть только что написанного запроса, без мер близости:
Если пользователь выберет одно из предлагаемых значений, то получит точное попадание с оценкой близости 100%, но ему не запрещается нажать кнопку (или ENTER, мы же сделали кнопку [Искать] горячей) при произвольном значении введенного ключа поиска:
Минут за 15 мы с помощью Oracle APEX сделали Web интерфейс для визуализации нечеткого поиска по методу оценки лингвистической близости имеющихся данных и ключа. Корпоративный Google.
Этот алгоритм не является оптимальным и будет вызывать рост задержки выполнения по мере накопления анализируемого материала, поскольку он просматривает весь материал при каждом поиске. Но у него есть два неоспоримых достоинства:
1. Не потребовалось вносить никаких изменений в базу, это "автомат автоматов", он автоматически будет следовать своим автоматизмом за задачей;
2. Его можно сразу показать заказчику - час на запрос и 15 минут на интерфейс.
А как его ускорить - тоже понятно: создать хранимый каталог слов и пополнять его при записи очередной порции статей.