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

Давайте приступим к рассмотрению полнотекстовых запросов в СУБД Microsoft SQL Server, с помощью которых осуществляется полнотекстовый поиск.

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

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

CONTAINS

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

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

 select id as ID, textdata as TextData from TestTable 
                        where CONTAINS (textdata, 'Microsoft') 

Скриншот 1

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

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

--Строки, содержащие и слово Microsoft и слово SQL (AND)
select id as ID, textdata as TextData from TestTable 
                        where CONTAINS (textdata, '"Microsoft" AND "SQL"')
                        
--Строки, содержащие слово Microsoft или слово SQL (OR)
select id as ID, textdata as TextData from TestTable 
                        where CONTAINS (textdata, '"Microsoft" OR "SQL"')
                        
--Строки, содержащие слово Microsoft и при этом не содержат слово SQL (AND NOT)
select id as ID, textdata as TextData from TestTable 
                        where CONTAINS (textdata, '"Microsoft" AND NOT "SQL"')  

Скриншот 2

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

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

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

 select id as ID, textdata as TextData from TestTable 
                        where CONTAINS (textdata, '"програм*"')

Скриншот 3

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

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

 select id as ID, textdata as TextData from TestTable 
                        where CONTAINS (textdata, 'FORMSOF(INFLECTIONAL, "запрос")')

Скриншот 4

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

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

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

 select id as ID, textdata as TextData from TestTable 
                WHERE CONTAINS (textdata, '"язык программирования" NEAR "Microsoft"')

Скриншот 5

Функция CONTAINSTABLE

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

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

 SELECT  Table1.id AS ID, 
                RowRank.Rank as [RANK], Table1.textdata as [TEXTDATA] 
 FROM TestTable Table1
 INNER JOIN CONTAINSTABLE(TestTable, textdata, '"SQL" OR "Microsoft"') as RowRank 
                                                on Table1.id=RowRank.[KEY]
 ORDER BY RowRank.RANK DESC

Скриншот 6

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

 SELECT  Table1.id AS ID, 
                RowRank.Rank as [RANK], Table1.textdata as [TEXTDATA] 
 FROM TestTable Table1
 INNER JOIN CONTAINSTABLE(TestTable,textdata,'"SQL" OR "Microsoft"',3) as RowRank 
                                                on Table1.id=RowRank.[KEY]
 ORDER BY RowRank.RANK DESC 

Скриншот 7

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

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

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

 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

Скриншот 8

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

FREETEXT

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

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

 select id as ID, textdata as TextData from TestTable 
                        WHERE FREETEXT (textdata, 'Языки для разработки программ')

Скриншот 9

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

Функция FREETEXTTABLE

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

В примере выше мы получили результирующий набор, но мы не знаем какие строки действительно релевантные нашему запросу, поэтому имеет смысл использовать функцию 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

Скриншот 10

Результат этого запроса уже более понятен, исключение составляет строка с 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 [Статус]

Скриншот 11

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

  • 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)] 
 select OBJECTPROPERTYEX(
                                                object_id('TestTable'), 
                                                'TableFulltextChangeTrackingOn'
                                                )as[Полнотекстовое отслеживание изменений(TRUE/FALSE)]

 select OBJECTPROPERTYEX(
                                                object_id('TestTable'), 
                                                'TableFulltextCatalogId'
                                                )as[Идентификатор полнотекстового каталога]

Скриншот 12

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

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.