Ограничение результатов поиска с помощью RANK
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure
Функции CONTAINSTABLE и FREETEXTTABLE возвращают столбец с именем, RANK содержащий порядковые значения от 0 до 1000 (значения ранжирования). Эти значения используются для ранжирования возвращенных строк согласно их соответствию критерию выбора. Ранжирующие значения указывают только относительный порядок релевантности строк в результирующем наборе, при этом чем меньше значение, тем меньше релевантность. Фактические значения несущественны и, как правило, различны для каждого выполнения запроса.
Примечание.
CONTAINS И FREETEXT предикаты не возвращают значения ранжирования.
Количество элементов, соответствующих условию поиска, часто большое. Чтобы предотвратить возврат слишком большого количества совпадений при выполнении запросов CONTAINSTABLE или FREETEXTTABLE, используйте необязательный параметр top_n_by_rank, который возвращает только подмножество строк.
top_n_by_rank представляет собой целочисленное значение, n, указывающее, что возвращаются только совпадения с наибольшим рейтингом n в порядке убывания. Если параметр top_n_by_rank скомбинирован с другими параметрами, то запрос может вернуть меньше строк, чем фактически соответствует всем предикатам.
SQL Server упорядочивает совпадения по рангу и возвращает только указанное количество строк. Это может привести к значительному повышению производительности. Например, запрос, который обычно возвращает 100 000 строк из таблицы 1000 000 строк, обрабатывается быстрее, если запрашиваются только первые 100 строк.
Примеры использования RANK для ограничения результатов поиска
Пример A: Поиск только трех лучших соответствий
В следующем примере используется CONTAINSTABLE для возврата только трёх лучших совпадений.
USE AdventureWorks2022;
GO
SELECT K.RANK,
AddressLine1,
City
FROM Person.Address AS A
INNER JOIN CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("des*",
Rue WEIGHT(0.5),
Bouchers WEIGHT(0.9))', 3) AS K
ON A.AddressID = K.[KEY];
GO
Вот результаты.
RANK Address City
----------- -------------------------------- ------------------------------
172 9005, rue des Bouchers Paris
172 5, rue des Bouchers Orleans
172 5, rue des Bouchers Metz
Пример B. Поиск пяти лучших совпадений
В следующем примере используется CONTAINSTABLE для возврата описания первых пяти продуктов, где столбец Description содержит слово "алюминий" рядом со словом light или словом lightweight.
USE AdventureWorks2022;
GO
SELECT FT_TBL.ProductDescriptionID,
FT_TBL.Description,
KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL
INNER JOIN CONTAINSTABLE(Production.ProductDescription,
Description, '(light NEAR aluminum) OR (lightweight NEAR aluminum)', 5) AS KEY_TBL
ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY];
GO
Как ранжируются результаты поиска
Полнотекстовый поиск в SQL Server может создать необязательную оценку (или значение ранжирования), указывающую релевантность данных, возвращаемых полнотекстовый запросом. Это ранжирующее значение вычисляется для каждой строки и может использоваться как критерий упорядочения для сортировки результирующего набора данного запроса по релевантности. Ранжирующие значения показывают только относительный порядок релевантности строк в результирующем наборе. Фактические значения несущественны и, как правило, различны для каждого выполнения запроса. Значение ранжирования не имеет никакого значения между запросами.
Статистика ранжирования
При построении индекса собираются статистические данные, которые будут использоваться в ранжировании. Процесс создания полнотекстового каталога не приводит непосредственно к одной структуре индекса. Вместо этого подсистема полнотекстового текста для SQL Server создает промежуточные индексы при индексировании данных. Затем средство полнотекстового поиска выполняет слияние этих индексов в больший индекс по мере необходимости. Этот процесс может повторяться много раз. После этого средство полнотекстового поиска выполняет "мастер-слияние", объединяющее все промежуточные индексы в один большой главный индекс.
Статистические данные собираются на каждом уровне промежуточных индексов. Слияние этих данных производится при слиянии индексов. Некоторые статистические значения могут формироваться только в процессе объединения мастер-данных.
Хотя SQL Server ранжирует результирующий набор запросов, он использует статистику из крупнейшего промежуточного индекса. Это зависит от того, объединяются ли промежуточные индексы. В результате статистика ранжирования может отличаться в точности, если промежуточные индексы не объединены. По этой причине один и тот же запрос в разное время может возвращать различные результаты ранжирования по мере добавления, изменения и удаления данных, а также по мере слияния небольших индексов.
Чтобы свести к минимуму размер индекса и сложность вычислений, статистические данные часто округляются.
В следующем списке содержатся некоторые часто используемые термины и статистические значения, важные для вычисления ранга.
| Термин / значение | Описание |
|---|---|
| Свойство | Полнотекстовый индексированный столбец в строке. |
| Документ | Сущность, возвращаемая в запросах. В SQL Server это соответствует строке. Документ может иметь несколько свойств точно так же, как и строка может иметь несколько полнотекстовых индексированных столбцов. |
| Указатель | Один инвертированный индекс одного или нескольких документов. Это может быть полностью в памяти или на диске. Многие статистические данные относятся к конкретному индексу, в котором было обнаружено соответствие. |
| Полнотекстовый каталог | Коллекция промежуточных индексов, которые для запросов считаются одной сущностью. Каталоги — это подразделение организации, видимое администратору SQL Server. |
| Слово, токен или элемент | Единица сопоставления в полнотекстовом движке. Потоки текста из документов разбиваются на слова или токены, используя разделители слов, специфичные для каждого языка. |
| Наличие | Смещение слова в свойстве документа, определенное средством разбиения по словам. Первое слово находится на позиции 1, следующее — на позиции 2 и так далее. Чтобы избежать ложных положительных результатов в запросах по сходству и запросах фраз, в конце предложения и абзаца используется увеличенный промежуток между вхождениями. |
| TermFrequency | Количество раз, когда значение ключа появляется подряд в строке. |
| IndexedRowCount | Общее число индексированных строк. Это вычисляется на основе счетчиков, поддерживаемых в промежуточных индексах. Точность этого числа может быть различной. |
| KeyRowCount | Общее число строк в полнотекстовом каталоге, содержащем конкретный ключ. |
| MaxOccurrence | Наибольшее число вхождений для конкретного свойства в строке, хранящееся в полнотекстовом каталоге. |
| MaxQueryRank | Максимальный ранг (1000), возвращенный средством полнотекстового поиска. |
Проблемы с вычислением ранга
Процесс вычисления ранга зависит от многих факторов. Средства разбиения текста на слова в разных языках работают по-разному. Например, строку «dog-house» один разделитель слов может разбить на «dog» и «house», а другой — на «dog-house». Это означает, что сопоставление и ранжирование зависят от указанного языка, так как не только слова отличаются, но и длина документа. Разница в длине документа может повлиять на ранжирование во всех запросах.
Такие статистические данные, как IndexRowCount , могут различаться в широких пределах. Например, если каталог имеет 2 миллиарда строк в главном индексе, то новый документ индексируется хранящимся в памяти индексом. Поэтому ранги для этого документа, вычисленные на основе количества документов в индексе, хранящемся в памяти, могут отличаться от рангов для документов из главного индекса. По этой причине мы рекомендуем после заполнения большого количества строк данными и их последующей индексации или переиндексации объединить индексы в основной индекс с помощью инструкции Transact-SQL ALTER FULLTEXT CATALOG ... REORGANIZE. Подсистема полнотекстового текста также автоматически объединяет индексы на основе параметров, таких как число и размер промежуточных индексов.
ЗначенияMaxOccurrence нормализуются в один из 32 диапазонов. Это означает, например, что документ длиной 50 слов обрабатывается так же, как и документ 100 слов. Ниже приведена таблица, используемая для нормализации. Поскольку длина документа находится в диапазоне между смежными значениями таблицы 32 и 128, они эффективно обрабатываются как имеющие ту же длину, 128 (32 = 128 <docLength<).
{ 16, 32, 128, 256, 512, 725, 1024, 1450, 2048, 2896, 4096, 5792, 8192, 11585,
16384, 23170, 28000, 32768, 39554, 46340, 55938, 65536, 92681, 131072, 185363,
262144, 370727, 524288, 741455, 1048576, 2097152, 4194304 };
Ранг CONTAINSTABLE
РанжированиеCONTAINSTABLE использует следующий алгоритм:
StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )
Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )
Фразовые соответствия ранжируются как отдельные ключи, за исключением того, что значение KeyRowCount (количество строк, содержащих эту фразу) является приближением — оно может быть неточным и выше фактического значения.
Ранг NEAR
CONTAINSTABLE поддерживает запросы на два или более терминов поиска в близости друг от друга с помощью NEAR параметра. Ранжирующее значение для каждой из возвращаемых строк зависит от нескольких параметров. Одним из основных факторов ранжирования является общее количество совпадений (или попаданий) по отношению к длине документа. Таким образом, например, если у документа из 100 слов и документа из 900 слов будет идентичный набор совпадений, то документ из 100 слов получит более высокий ранг.
Общая длина каждого совпадения в строке также влияет на ранжирование этой строки на основе расстояния между первым и последним поисковыми терминами этого совпадения. Чем меньше расстояние, тем больше попадание увеличивает ранжирующее значение строки. Если полнотекстовый запрос не указывает целое число в качестве максимального расстояния, и в документе содержатся только попадания, расстояние между которыми превышает 100 логических терминов, то его рейтинг равен 0.
Ранг ISABOUT
CONTAINSTABLE поддерживает запросы к взвешанным терминам с помощью ISABOUT параметра.
ISABOUT — это запрос векторного пространства в традиционной терминологии получения информации. В качестве алгоритма ранжирования по умолчанию используется широко известная формула Жаккарда. Ранжирование вычисляется для каждого термина в запросе, а затем объединяется, как описано в следующем алгоритме.
ContainsRank = same formula used for CONTAINSTABLE ranking of a single term (above).
Weight = the weight specified in the query for each term. Default weight is 1.
WeightedSum = Σ[key=1 to n] ContainsRankKey * WeightKey
Rank = ( MaxQueryRank * WeightedSum ) / ( ( Σ[key=1 to n] ContainsRankKey^2 )
+ ( Σ[key=1 to n] WeightKey^2 ) - ( WeightedSum ) )
Ранг FREETEXTTABLE
РанжированиеFREETEXTTABLE основано на формуле ранжирования OKAPI BM25.
FREETEXTTABLE запросы добавляют слова в запрос с помощью инлекционного создания (инфлектированные формы исходных слов запроса); эти слова рассматриваются как отдельные слова, без особых отношений с словами, из которых они были созданы. Синонимы, сформированные с помощью тезауруса, обрабатываются как отдельные, независимые и взвешенные выражения. Каждое слово в запросе вносит свой вклад в ранжирование.
Rank = Σ[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 ) qtf / ( k3 + qtf ) ) )
Where:
w is the Robertson-Sparck Jones weight.
In simplified form, w is defined as:
w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * ( n - r + 0.5 ) )
N is the number of indexed rows for the property being queried.
n is the number of rows containing the word.
K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) ).
dl is the property length, in word occurrences.
avdl is the average length of the property being queried, in word occurrences.
k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
tf is the frequency of the word in the queried property in a specific row.
qtf is the frequency of the term in the query.