Логотип StingRay

Социальные сети
FacebookInstagramRSSTwitterYouTubeВ контактеОдноклассники
FacebookInstagramRSSTwitterYouTubeВ контактеОдноклассники
Силуэт человека

Псевдорелевантный 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
Предпросмотр
Улыбка Подмигивание Дразнит Оскал Смех Огорчение Сильное огорчение Шок Сумасшествие Равнодушие Молчание Крутизна Злость Бешенство Смущение Сожаление Влюблённость Ангел Демон Задумчивость Рука-лицо Не могу смотреть Жирный Курсив Подчёркивание Зачёркивание Размер шрифта Гиперссылка Цитата
Загрузка…