В первой части этой серии мы рассмотрели методологию включения базы данных Sql Server для полнотекстовой индексации и включения полнотекстовых индексов для определенных таблиц. А во второй части этой серии мы изучили методологию доступа к этим полнотекстовым индексам с использованием функций CONTAINS и FREETEXT . В этой заключительной части серии мы рассмотрим использование функций CONTAINSTABLE и FREETEXTTABLE для предоставления ранжированных результатов поиска вызывающему приложению.
Как упоминалось ранее, функции CONTAINSTABLE и FREETEXTTABLE запрашивают выбранный полнотекстовый индекс для предоставленных условий поиска. Разница между этими функциями и их двоюродными братьями, не входящими в TABLE, то есть CONTAINS и FREETEXT, заключается в том, что эти функции принимают несколько разные параметры и, что наиболее важно, они возвращают таблицу значений, а не простой ответ истина / ложь.
Эти табличные функции принимают несколько иной набор параметров, чем их двоюродные братья. Эти параметры:
- Индексированная таблица произвольного текста для запроса.
- Столбцы в таблице для запроса.
- Искать термин. Для FREETEXTTABLE это, как правило, просто те термины, которые вы хотите найти. Для CONTAINSTABLE этот термин может быть чем-то похожим на те, что описаны в части 2.
- Top_N_Results. Необязательное целое число, чтобы установить ограничение на количество результатов, которые нужно получить. Это поле очень важно — ограничение количества результатов, с которыми вы действительно хотите работать, значительно снижает нагрузку на сервер базы данных, особенно при работе с большими наборами результатов.
Таблица, которую возвращают эти функции, содержит два столбца:
- KEY: содержит первичный ключ строки в индексированной таблице. Очевидно, но важно то, что его тип данных совпадает с первичным ключом в этой таблице.
- RANK: это положительное целое число, представляющее относительную силу соответствия вашему поисковому запросу. Это число не является абсолютным значением — оно не начинается с некоторого фиксированного значения и работает почти до нуля. Конкретное значение является только относительным по сравнению с другими результатами этого запроса в свободном тексте. Сравнение значения RANK для разных полнотекстовых запросов не обязательно дает значимые результаты.
Теперь эта таблица с двумя полями может показаться очень простой, но с ее помощью вы можете сделать много очень хитрых трюков, используя очень простую технику — просто ВНУТРЕННЕ СОЕДИНИТЕ таблицу, возвращенную из функции произвольного текста в поле KEY, и получите желаемые результаты. , затем сортируйте по столбцу RANK для сортировки эффектов. Или даже получить эти точные процентные совпадения, которые вы видите в некоторых поисковых системах сайта.
В следующих примерах мы будем использовать базу данных Microsoft Pubs, как мы установили во второй части этой серии.
Сначала давайте рассмотрим получение ранжированных результатов поиска с помощью FREETEXTTABLE:
SELECT ftt.RANK, titles.title_id, titles.title FROM Titles INNER JOIN FREETEXTTABLE(titles, notes, 'recipe cuisine') as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESC Как видите, ничего сложного — просто ВНУТРЕННЕЕ СОЕДИНЕНИЕ к FREETEXTTABLE и ЗАКАЗ по рангу FREETEXTTABLE.
Теперь, чтобы получить проценты, нужно немного поумнеть. Для совпадения в процентах вам нужно разделить RANK на верхнее значение RANK. К сожалению, вы не можете сделать это с помощью одного запроса в произвольном тексте; это требует выбора значения TOP, затем выбора желаемых результатов и создания вычисляемого поля для процентов. Смущены еще? Что ж, давайте посмотрим на это в SQL.
DECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FREETEXTTABLE(titles, notes, 'recipe cuisine', 1)) SELECT ftt.RANK, (CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent, titles.title_id, titles.title FROM Titles INNER JOIN FREETEXTTABLE(titles, notes, 'recipe cuisine') as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESCDECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FREETEXTTABLE(titles, notes, 'recipe cuisine', 1)) SELECT ftt.RANK, (CAST(ftt.RANK as DECIMAL)/@topRank) as matchpercent, titles.title_id, titles.title FROM Titles INNER JOIN FREETEXTTABLE(titles, notes, 'recipe cuisine') as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESC
Одно замечание — я намеренно использовал необязательный параметр Top_N_Results при первом вызове FREETEXTTABLE, чтобы ограничить количество возвращаемых результатов. Причина этого заключается в том, чтобы сделать запрос намного дешевле на сервере базы данных.
Последний ключевой трюк: как я уже упоминал в части 2, можно очень увлекаться содержанием и поисковыми терминами. Это действительно, действительно относится к CONTAINSTABLE. Например, использование взвешенных условий поиска и столбца RANK позволяет получить правильные результаты для конечных пользователей. Например, скажем, мы хотели найти книги о получателях или кухнях, но особенно о получателях:
SELECT ftt.RANK, titles.title_id, titles.title, notes FROM Titles INNER JOIN CONTAINSTABLE ( titles, notes, 'ISABOUT("recipe*" weight (.7), "cuisine*" weight (.2))' ) as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESCSELECT ftt.RANK, titles.title_id, titles.title, notes FROM Titles INNER JOIN CONTAINSTABLE ( titles, notes, 'ISABOUT("recipe*" weight (.7), "cuisine*" weight (.2))' ) as ftt ON ftt.[KEY]=titles.title_id ORDER BY ftt.RANK DESC
В вышеприведенном примере следует отметить одну вещь: поскольку мы используем запрос типа CONTAINS, поисковый термин (ISABOUT …) должен включать другие формы для получения множественного числа и тому подобное. В этих случаях я использовал конструкцию «SearchTerm *», чтобы заставить полнотекстовый движок найти все термины, начинающиеся с «SearchTerm».
Наконец, для обзора:
- В первой части мы рассмотрели, как создавать полнотекстовые индексы, и как их поддерживать.
- Во второй части мы рассмотрели, как запрашивать ваши полнотекстовые индексы с использованием предикатов CONTAINS и FREETEXT, а также разницу между этими функциями.
- В этой части мы рассмотрели использование функций CONTAINSTABLE и FREETEXTTABLE для получения ранжированных результатов поиска по вашим индексам произвольного текста. Кроме того, мы рассмотрели некоторые ограничения и обходные пути для использования запросов с произвольным текстом.
Я надеюсь, что вы все получили удовольствие, и, пожалуйста, оставляйте комментарии или вопросы. И наконец, не стесняйтесь пнуть его, если вам это нравится .