Логотип StingRay

Поделиться
FacebookFacebookRSSTwitterYouTubeВ контактеОдноклассники
FacebookFacebookRSSTwitterYouTubeВ контактеОдноклассники
Силуэт человека

Псевдорелевантный SQL-поиск

Столкнувшись с необходимостью реализовывать для посетителей своего сайта более-менее поиск по базе данных, используемой в проектах «Кино» и «Книги», и решив эту проблему вполне приемлемым способом, я решил поделиться опытом с другими SQL-разработчиками.

Сразу предупрежу, что здесь вы не найдёте серьёзной теории по механизмам поиска и тем более открытия секретов функционирования таких «поисковых монстров», как «Яндекс» или Google. Скорее, я просто решал частную техническую задачу, хотя в порядке повышения общей эрудиции с кое-какой скудной теорией по этому вопросу и познакомился. Некоторые полезные ссылки для особо интересующихся:

Но вернёмся к данной статье. Начну с объяснения её названия. Прежде всего – что такое релевантность? Релевантность (от англ. “relevance” – уместность, соответствие, адекватность) – это мера соответствия результатов поискового запроса сути самого запроса. Я назвал объясняемый здесь механизм «псевдорелевантным» потому, что хоть он и учитывает такую меру соответствия и ранжирует результаты по ней, но на самом деле делается это без серьёзной теоретической проработки вопроса и без особо изощрённого алгоритма расчёта индекса релевантности. Ну а «SQL-поиск» это потому, что в его реализации будет использован только самый обычный SQL (скажем, соответствующий стандарту SQL-92).

Предположим, что у нас есть некоторая таблица MyTable с неким строковым полем MyField, по которому нужно осуществить поиск. И пусть она условно содержит следующие записи:

# MyField
1 fulL fooL
2 Full Fool
3 relevancE iS fulL
4 Relevance Is Full
5 morE fulL relevancE arounD
6 More Full Relevance Around
7 fulL relevancE
8 Full Relevance

Определим уровни релевантности, то есть степени соответствия результатов запросу. Изначально я «придумал» 4 таких уровня, но вспомнив о возможной регистрозавимости поиска, всё-таки насчитал их 8 штук. Ниже они приводятся в порядке уменьшения релевантности.

  1. Значение поля в точности равно искомой строке, с учётом регистра символов.
  2. Значение поля равно искомой строке, но без учёта регистра символов.
  3. Значение поля содержит искомую строку в качестве подстроки, с учётом регистра символов.
  4. Значение поля содержит искомую строку в качестве подстроки, без учёта регистра символов.
  5. Значение поля содержит все слова искомой строки в любом порядке, с учётом регистра символов.
  6. Значение поля содержит все слова искомой строки в любом порядке, без учёта регистра символов.
  7. Значение поля содержит любые слова (одно или несколько) искомой строки, с учётом регистра символов.
  8. Значение поля содержит любые слова (одно или несколько) искомой строки, без учёта регистра символов.

То есть, если мы будем искать строку “Full Relevance” в приведённом выше примере таблицы, то сначала будет выведена запись № 8 (так как значение поля MyField этой записи в точности равно искомой строке с учётом регистра), потом – № 7 (равно без учёта регистра), № 6 (содержит как подстроку с учётом регистра), № 5 (как подстроку без учёта регистра) и т. д. – в общем, в обратном порядке.

Каждый из уровней релевантности выбирается отдельным SQL-запросом (например, для п. 1 это SELECT * FROM MyTable WHERE BINARY MyField = 'Full Relevance'), вместе же они объёдиняются посредством SQL-оператора UNION. И вот что получается (на том же примере):

SELECT * FROM MyTable WHERE
  BINARY MyField = 'Full Relevance'
UNION
SELECT * FROM MyTable WHERE
  MyField = 'Full Relevance'
UNION
SELECT * FROM MyTable WHERE
  BINARY MyField LIKE '%Full Relevance%'
UNION
SELECT * FROM MyTable WHERE
  MyField LIKE '%Full Relevance%'
UNION
SELECT * FROM MyTable WHERE
  (BINARY MyField LIKE '%Full%') AND (BINARY MyField LIKE '%Relevance%')
UNION
SELECT * FROM MyTable WHERE
  (MyField LIKE '%Full%') AND (MyField LIKE '%Relevance%')
UNION
SELECT * FROM MyTable WHERE
  (BINARY MyField LIKE '%Full%') OR (BINARY MyField LIKE '%Relevance%')
UNION
SELECT * FROM MyTable WHERE
  (MyField LIKE '%Full%') OR (MyField LIKE '%Relevance%')

Конечно, тут же возникает вопрос о правильности определения вхождения слова в значение поля как %слово% (ведь при этом будут найдены не только и не столько искомые слова целиком, но и части других слов). Однако практика показывает, что подобный упрощённый SQL-запрос даёт вполне приемлемые результаты. Понятное дело, что запрос должен формироваться приложением динамически, с учётом разбора ввода пользователя по словам.

Если есть необходимость искать не по одному полю, а по нескольким сразу, в том числе по полям другой таблицы, то определённые выше уровни релевантности продолжают действовать, а вот их техническая реализация в виде SQL-запроса дополняется соответствующими сравнениями по другим полям, между собой объединяющимися оператором OR:

SELECT * FROM MyTable WHERE
  (BINARY MyField = 'Full Relevance') OR
  (BINARY MyField2 = 'Full Relevance')
UNION
SELECT * FROM MyTable WHERE
  (MyField = 'Full Relevance') OR
  (MyField2 = 'Full Relevance')
UNION
SELECT * FROM MyTable WHERE
  (BINARY MyField LIKE '%Full Relevance%') OR
  (BINARY MyField2 LIKE '%Full Relevance%')
UNION
SELECT * FROM MyTable WHERE
  (MyField LIKE '%Full Relevance%') OR
  (MyField2 LIKE '%Full Relevance%')
UNION
SELECT * FROM MyTable WHERE
  ((BINARY MyField LIKE '%Full%') AND (BINARY MyField LIKE '%Relevance%')) OR
  ((BINARY MyField2 LIKE '%Full%') AND (BINARY MyField2 LIKE '%Relevance%'))
UNION
SELECT * FROM MyTable WHERE
  ((MyField LIKE '%Full%') AND (MyField LIKE '%Relevance%')) OR
  ((MyField2 LIKE '%Full%') AND (MyField2 LIKE '%Relevance%'))
UNION
SELECT * FROM MyTable WHERE
  ((BINARY MyField LIKE '%Full%') OR (BINARY MyField LIKE '%Relevance%')) OR
  ((BINARY MyField2 LIKE '%Full%') OR (BINARY MyField2 LIKE '%Relevance%'))
UNION
SELECT * FROM MyTable WHERE
  ((MyField LIKE '%Full%') OR (MyField LIKE '%Relevance%')) OR
  ((MyField2 LIKE '%Full%') OR (MyField2 LIKE '%Relevance%'))

Конечно, уровни релевантности при поиске по несколькими полям/таблицам могут быть уточнены таким образом, чтобы отдельные поля и/или таблицы имели приоритет, но это уже частные доработки.

Вот, в общем-то, и всё. Добавлю лишь, что попутно мне пришлось решать ещё одну, довольно стандартную проблему – постраничный вывод результатов поиска. Принять правильное решение по этому вопросу мне помогло одно обсуждение на форумах XPoint.ru.

Благодарность за стимул в подготовке данной статьи выражается Артёму Петрову.

Добавьте свой комментарий, почитайте уже добавленные комментарии или войдите, чтобы подписаться/отписаться.
Имя: OpenId
Результат операции:
Предпросмотр Улыбка Подмигивание Дразнит Оскал Смех Огорчение Сильное огорчение Шок Сумасшествие Равнодушие Молчание Крутизна Злость Бешенство Смущение Сожаление Влюблённость Ангел Вопрос Восклицание Жирный Курсив Подчёркивание Зачёркивание Размер шрифта Гиперссылка Цитата
Загрузка…