Как создать простой нечеткий поиск только с помощью Postgresql?

У меня есть небольшая проблема с функцией поиска на моем сайте на основе RoR. У меня много Produts с некоторыми CODE. Этот код может быть любой строкой, такой как "AB-123-lHdfj". Теперь я использую оператор ILIKE для поиска продуктов:

Product.where("code ILIKE ?", "%" + params[:search] + "%")

Он отлично работает, но не может найти продукт с такими кодами, как "AB123-lHdfj" или "AB123lHdfj".

Что я должен сделать для этого? Может быть, postgresql имеет некоторую функцию нормировки строки или некоторые другие методы, которые помогут мне?:)

Ответы

Ответ 1

Postgres предоставляет модуль с несколькими функциями сравнения строк, такими как soundex и metaphone. Но вы захотите использовать функцию редактирования levenshtein.

Example:

test=# SELECT levenshtein('GUMBO', 'GAMBOL');
 levenshtein
-------------
           2
(1 row)

2 - расстояние редактирования между двумя словами. Когда вы применяете это к нескольким словам и сортируете по результату редактирования расстояния, у вас будет тип нечетких совпадений, которые вы ищете.

Попробуйте этот образец запроса: (с вашими собственными именами объектов и данными, конечно)

SELECT * 
FROM some_table
WHERE levenshtein(code, 'AB123-lHdfj') <= 3
ORDER BY levenshtein(code, 'AB123-lHdfj')
LIMIT 10

В этом запросе говорится:

Дайте мне 10 лучших результатов из всех данных из some_table, где расстояние редактирования между значением кода и входом "AB123-lHdfj" меньше 3. Вы получите все строки, где значение кода не должно превышать 3 символов разница в "AB123-lHdfj"...

Примечание: если вы получите сообщение об ошибке:

function levenshtein(character varying, unknown) does not exist

Установите расширение fuzzystrmatch, используя:

test=# CREATE EXTENSION fuzzystrmatch;

Ответ 2

Пол рассказал вам о levenshtein(). Это очень полезный инструмент, но он также очень медленный с большими таблицами. Он должен рассчитать левенштейн-расстояние от поискового термина для каждой отдельной строки, что дорого.

Прежде всего, если ваши требования просты, как показывает пример, вы все равно можете использовать LIKE. Просто замените любой - в своем поисковом выражении на %, чтобы создать предложение WHERE

WHERE code LIKE "%AB%123%lHdfj%"

вместо

WHERE code LIKE "%AB-123-lHdfj%"

Если ваша реальная проблема сложнее, и вам нужно что-то быстрее, то в зависимости от ваших требований - есть несколько вариантов.

  • полнотекстовый поиск, конечно. Но это может быть излишним в вашем случае.

  • Более вероятным кандидатом является pg_trgm. Обратите внимание, что вы можете комбинировать это с LIKE в PostgreSQL 9.1. Смотрите сообщение в блоге Depesz.
    Также очень интересно в этом контексте: функция similarity() или % этого модуля. Подробнее:

  • Наконец, вы можете реализовать ручное решение с функцией нормализовать строки для поиска. Например, вы можете преобразовать AB1-23-lHdfj → ab123lhdfj, сохранить его в дополнительном столбце и найти в нем поисковые термины, которые были преобразованы одинаково.

    Или используйте индекс в выражении вместо избыточного столбца. (Принятые функции должны быть IMMUTABLE.) И, возможно, объединить это с pg_tgrm сверху.

Обзор методов сопоставления шаблонов: