Полнотекстовые запросы в Microsoft SQL Server

Приветствую Вас на сайте Info-Comp.ru! Сегодня мы приступим к рассмотрению полнотекстовых запросов в СУБД Microsoft SQL Server, с помощью которых осуществляется полнотекстовый поиск.

Напомню, ранее мы с Вами рассмотрели компонент Full-Text Search, на основе которого реализуется полнотекстовый поиск в SQL сервере и даже в качестве примера написали пару простых запросов. В этом материале мы будем более подробно рассматривать полнотекстовые запросы.

Примечание! В качестве исходных данных у нас будет выступать таблица, которую мы создавали в предыдущем материале (TestTable), она содержит список определений технологий (столбец TextData). В качестве СУБД выступает Microsoft SQL Server 2019.

CONTAINS

CONTAINS – это ключевое слово, которое используется в конструкции WHERE для поиска слов или фраз в столбце, который участвует в полнотекстовом поиске.

В следующем примере мы просто ищем строки, которые содержат слово Microsoft

   
   --CONTAINS
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, 'Microsoft');

Логические операторы

При составлении критерия поиска можно использовать логические операторы AND, OR, AND NOT, т.е., например, можно построить запрос так, чтобы искались строки со словами и Microsoft и SQL

   
   --Строки, содержащие и слово Microsoft, и слово SQL (AND)
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, '"Microsoft" AND "SQL"');

   --Строки, содержащие слово Microsoft или слово SQL (OR)
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, '"Microsoft" OR "SQL"');

   --Строки, содержащие слово Microsoft, но которые не содержат слово SQL (AND NOT)
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, '"Microsoft" AND NOT "SQL"');

Поиск по префиксным выражениям

Префиксные выражения означают, что мы можем искать слова, не указывая их полностью, например, указав только начало, это делается с помощью знака *

Допустим, нам необходимо найти строки, где есть упоминания о программах или программировании, для этого мы напишем следующее:

   
   --CONTAINS. Поиск по префиксным выражениям
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, '"програм*"');

Поиск слова по словоформам

Полнотекстовый поиск SQL сервера позволяет искать различные формы глаголов или существительные в единственном и во множественном числе, например, давайте найдем записи, в которых есть слово «запрос» и его производные выражения.

   
   --CONTAINS. Поиск слова по словоформам
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, 'FORMSOF(INFLECTIONAL, "запрос")');

Как видим слова «запрос» у нас нет, но есть слово «запросов» поэтому эта строка и вывелась. Для поиска по словоформам мы использовали функцию FORMSOF().

Поиск слов или фраз с учетом расположения

Если Вам нужно найти слова или фразы, которые располагаются недалеко друг от друга, то можно использовать ключевое слово NEAR. Допустим, мы хотим получить все строки, в которых есть упоминание о любом языке программирования, но при условии, что компания Microsoft должна иметь к нему какое-то отношение, другими словами, фраза «язык программирования» должна располагаться неподалеку от слова «Microsoft».

   
   --CONTAINS. Поиск слов или фраз с учетом расположения
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, '"язык программирования" NEAR "Microsoft"');

Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.

Функция CONTAINSTABLE

Это табличная функция, которая возвращает результирующий набор данных с проставленным рангом. RANK — это значение от 0 до 1000, показывающее степень соответствия каждой строки условию поиска. Другими словами, мы можем написать запрос и получить отсортированный результат по релевантности. При использовании функции CONTAINSTABLE можно использовать такие же условия поиска, как и в CONTAINS. Эта функция помимо RANK возвращает еще столбец KEY — это уникальный ключ базовой таблицы, по которому можно произвести объединение.

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

   
   --CONTAINSTABLE
   SELECT Table1.Id AS [Id],
          RowRank.Rank AS [RANK],
          Table1.TextData AS [TextData]
   FROM TestTable AS Table1
   INNER JOIN CONTAINSTABLE(TestTable, TextData, '"SQL" OR "Microsoft"') AS RowRank
         ON Table1.Id=RowRank.[KEY]
   ORDER BY RowRank.RANK DESC;

В случае необходимости в функцию CONTAINSTABLE четвертым параметром можно передать язык, ресурсы которого будут использоваться для разбиения слов, но он не обязателен. Также пятым или четвертым параметром (если не указан параметр LANGUAGE) можно передать число, которое ограничит результирующий набор. Например, для получения только первых 3 строк мы бы написали вот такой запрос:

   
   --CONTAINSTABLE с ограничением строк
   SELECT Table1.Id AS [Id],
          RowRank.Rank AS [RANK],
          Table1.TextData AS [TextData]
   FROM TestTable AS Table1
   INNER JOIN CONTAINSTABLE(TestTable, TextData, '"SQL" OR "Microsoft"', 3) AS RowRank
         ON Table1.Id=RowRank.[KEY]
   ORDER BY RowRank.RANK DESC;

Поиск с использованием взвешенных значений

В условии поиска можно указать важность того или иного слова или фразы от 0.0 до 1.0, т.е. осуществлять поиск со взвешенными значениями. Значение 0.0 является самым низким, а значение 1.0 самым высоким (в качестве десятичного разделителя всегда используется точка). Чтобы в запросе использовать взвешенные значения, необходимо использовать такие функции, как ISABOUT и WEIGHT. Их также можно использовать и в запросах CONTAINS.

А теперь допустим, что нас результаты ранжирования, из предыдущего примера, не устроили, мы хотим видеть сначала только самые релевантные строки, содержащие слово SQL, а только потом строки со словом Microsoft. Для этого мы назначим более высокий приоритет слову SQL, допустим 0.9, а Microsoft 0.1.

   
   --CONTAINSTABLE. Поиск с использованием взвешенных значений
   SELECT Table1.Id AS [Id],
          RowRank.Rank AS [RANK],
          Table1.TextData AS [TextData]
   FROM TestTable Table1
   INNER JOIN CONTAINSTABLE(TestTable, TextData,
              'ISABOUT("SQL" WEIGHT(.9), "Microsoft" WEIGHT(.1))') AS RowRank
         ON Table1.id=RowRank.[KEY]
   ORDER BY RowRank.RANK DESC;

Как видим, результат у нас изменился и теперь строки, которые содержат слово SQL, имеют более высокий ранг.

FREETEXT

FREETEXT – это ключевое слово, с помощью которого осуществляется поиск по произвольной текстовой строке. Другими словами, мы можем написать любую фразу или предложение, а FREETEXT сам выделит все слова и определит все словоформы этих слов и только потом выполнит запрос. Полнотекстовые запросы с использованием FREETEXT являются менее точными по сравнению с CONTAINS. Как Вы понимаете, использование ключевых слов WEIGHT, FORMSOF, NEAR и прочего синтаксиса запрещено.

Для примера давайте представим, что мы не знаем, что конкретно мы ищем, например, мы хотим узнать, есть ли в нашей базе языки, которые используются для разработки программ, и для этого пишем следующий запрос:

   
   --FREETEXT
   SELECT Id, TextData
   FROM TestTable
   WHERE FREETEXT (TextData, 'Языки для разработки программ');

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

Заметка! Новичкам рекомендую посмотреть мой видеокурс по T-SQL для начинающих, в нем подробно рассмотрены все базовые конструкции языка T-SQL.

Функция FREETEXTTABLE

Для определения, какая строка самая релевантная, т.е. подходящая под строку запроса, можно использовать табличную функцию FREETEXTTABLE, которая так же, как и CONTAINSTABLE, проставляет ранжирующее значение от 0 до 1000 и возвращает два столбца KEY и RANK. Количество возвращаемых строк можно также ограничить, передав дополнительный параметр. В условии запроса использовать ключевые слова WEIGHT, FORMSOF, NEAR и другие, как и во FREETEXT, нельзя.

В примере выше мы получили результирующий набор, но мы не знаем, какие строки действительно релевантные нашему запросу, поэтому имеет смысл использовать функцию FREETEXTTABLE, для того чтобы увидеть ранжирующее значение каждой строки и, допустим, отсортировать по нему.

   
   --FREETEXTTABLE
   SELECT Table1.Id AS [Id],
          RowRank.Rank AS [RANK],
          Table1.TextData AS [TextData]
   FROM TestTable Table1
   INNER JOIN FREETEXTTABLE(TestTable, TextData,
                           'Языки для разработки программ') AS RowRank
         ON Table1.id=RowRank.[KEY]
   ORDER BY RowRank.RANK DESC;

Результат этого запроса уже более понятен, исключение составляет строка с C++, в которой кроме слова «язык» ничего похожего на слова из нашей фразы нет.

Запросы на получение полнотекстовых свойств индексирования

Дополнительно хотелось бы еще отметить пару функций, с помощью которых нельзя выполнить поиск, но можно узнать параметры, которые влияют на результаты поиска.

Функция FULLTEXTCATALOGPROPERTY

С помощью этой функции можно узнать свойства полнотекстового каталога. Она имеет два параметра: первый — это название полнотекстового каталога и второй — это имя свойства, значение которого мы хотим узнать.

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

   
   SELECT CASE
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 0
               THEN 'Бездействие'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 1
               THEN 'Идет полное заполнение'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 2
               THEN 'Пауза'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 3
               THEN 'Ограниченный режим'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 4
               THEN 'Восстановление'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 5
               THEN 'Выключение'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 6
               THEN 'Идет добавочное заполнение'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 7
               THEN 'Построение индекса'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 8
               THEN 'Диск заполнен. Приостановлено'
          WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 9
               THEN 'Отслеживание изменений'
          ELSE ''
   END AS [Статус];

Также доступны и другие свойства, например:

• AccentSensitivity – учет диакритических знаков, 0 без учета, 1 с учетом;
• IndexSize – логический размер полнотекстового каталога в мегабайтах (МБ);
• ItemCount – количество элементов в полнотекстовом каталоге;
• MergeStatus – выполняется ли слияние в единый файл, 0 слияние не выполняется, 1 слияние выполняется;
• PopulateCompletionAge – разница в секундах между последним заполнением полнотекстового индекса и 01/01/1990 00:00:00;
• UniqueKeyCount – количество уникальных ключей в полнотекстовом каталоге;
• ImportStatus – выполняется ли в настоящее время импорт полнотекстового каталога, 0 не выполняется, 1 выполняется.

Функция OBJECTPROPERTYEX

Это функция используется для получения данных об объектах области схемы в текущей базе данных. Другими словами, с помощью нее можно узнать свойства объектов, не только относящихся к полнотекстовому индексированию. В качестве параметров она принимает: первый параметр —  идентификатор объекта и второй — это название свойства, значение которого мы хотим узнать.

В случае с полнотекстовым поиском она нам может помочь, например, тогда, когда мы хотим узнать, есть ли у таблицы полнотекстовый индекс или включено ли полнотекстовое отслеживание изменений.

   
    SELECT OBJECTPROPERTYEX(OBJECT_ID('TestTable'),
          'TableHasActiveFulltextIndex') AS [Активный полнотекстовый индекс(TRUE/FALSE)],
          OBJECTPROPERTYEX(OBJECT_ID('TestTable'),
          'TableFulltextChangeTrackingOn') AS [Полнотекстовое отслеживание изменений(TRUE/FALSE)],
          OBJECTPROPERTYEX(OBJECT_ID('TestTable'),
          'TableFulltextCatalogId') AS [Идентификатор полнотекстового каталога];

Заметка! Еще больше статей по Microsoft SQL Server и языку T—SQL Вы можете найти в специальном сборнике статей – Сборник статей для изучения Microsoft SQL Server.

На этом у меня все, пока!