Limitar resultados de pesquisa com RANK

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

As funções CONTAINSTABLE e FREETEXTTABLE retornam uma coluna chamada RANK que contém valores ordinais de 0 a 1000 (valores de classificação). Esses valores são usados para classificar as linhas retornadas de acordo com o quão bem elas correspondem aos critérios de seleção. Os valores de classificação indicam apenas uma ordem relativa de relevância das linhas no conjunto de resultados, com um valor menor indicando menor relevância. Os valores reais não são importantes e normalmente diferem cada vez que a consulta é executada.

O número de itens que correspondem a uma condição de pesquisa geralmente é grande. Para evitar que consultas CONTAINSTABLE ou FREETEXTTABLE retornem muitos resultados, utilize o parâmetro opcional top_n_by_rank, que retorna apenas um subconjunto de linhas. top_n_by_rank é um valor inteiro, n, que especifica que apenas as correspondências n mais bem classificadas devem ser retornadas, em ordem decrescente. Se top_n_by_rank for combinado com outros parâmetros, a consulta poderá retornar menos linhas do que o número de linhas que realmente correspondem a todos os predicados.

O SQL Server ordena as correspondências por classificação e retorna somente até o número especificado de linhas. Esta escolha pode resultar num aumento dramático no desempenho. Por exemplo, uma consulta que normalmente retornaria 100.000 linhas de uma tabela de 1.000.000 linhas é processada mais rapidamente se apenas as 100 primeiras linhas forem solicitadas.

Exemplos de utilização do RANK para limitar os resultados da pesquisa

Exemplo A: Procurar apenas as três principais correspondências

O exemplo a seguir usa CONTAINSTABLE para retornar apenas as três melhores opções.

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

Aqui está o conjunto de resultados.

RANK        Address                          City
----------- -------------------------------- ------------------------------
172         9005, rue des Bouchers           Paris
172         5, rue des Bouchers              Orleans
172         5, rue des Bouchers              Metz

Exemplo B: Procurar as cinco melhores correspondências

O exemplo a seguir usa CONTAINSTABLE para retornar a descrição dos cinco principais produtos em que a coluna Description contém a palavra "alumínio" perto da palavra light ou da palavra 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

Como os resultados da consulta de pesquisa são classificados

A pesquisa de texto completo no SQL Server pode gerar uma pontuação opcional (ou valor de classificação) que indica a relevância dos dados retornados por uma consulta de texto completo. Esse valor de classificação é calculado em cada linha e pode ser usado como um critério de ordenação para classificar o conjunto de resultados de uma determinada consulta por relevância. Os valores de classificação indicam apenas uma ordem relativa de relevância das linhas no conjunto de resultados. Os valores reais não são importantes e normalmente diferem cada vez que a consulta é executada. O valor de classificação não tem qualquer significado nas consultas.

Estatísticas para classificação

Quando um índice é construído, as estatísticas são coletadas para uso no ranking. O processo de criação de um catálogo de texto completo não resulta diretamente em uma única estrutura de índice. Em vez disso, o mecanismo de Full-Text para SQL Server cria índices intermediários à medida que os dados são indexados. Em seguida, o mecanismo de Full-Text mescla esses índices em um índice maior, conforme necessário. Este processo pode ser repetido muitas vezes. Em seguida, o mecanismo de Full-Text conduz uma "mesclagem mestre" que combina todos os índices intermediários em um grande índice mestre.

As estatísticas são recolhidas em cada nível intermédio do índice. As estatísticas são mescladas quando os índices são mesclados. Alguns valores estatísticos só podem ser gerados durante o processo de fusão mestre.

Enquanto o SQL Server classifica um conjunto de resultados de consulta, ele usa estatísticas do maior índice intermediário. Isso depende se os índices intermediários são mesclados ou não. Como resultado, as estatísticas de classificação podem variar em precisão se os índices intermediários não forem mesclados. Isso explica por que a mesma consulta pode retornar resultados de classificação diferentes ao longo do tempo à medida que dados indexados de texto completo são adicionados, modificados e excluídos, e à medida que os índices menores são mesclados.

Para minimizar o tamanho do índice e a complexidade computacional, as estatísticas são frequentemente arredondadas.

A lista a seguir inclui alguns termos comumente usados e valores estatísticos que são importantes no cálculo da classificação.

Problemas de cálculo de classificações

O processo de cálculo do ranking depende de muitos fatores. Separadores de palavras em idiomas diferentes tokenizam o texto de forma diferente. Por exemplo, a string "dog-house" pode ser dividida em "dog" e "house" por um divisor de palavras e em "dog-house" por outro. Isto significa que a correspondência e a classificação variam com base no idioma especificado, porque não só as palavras são diferentes, mas também o tamanho do documento. A diferença de comprimento do documento pode afetar a classificação de todas as consultas.

Estatísticas como IndexRowCount podem variar muito. Por exemplo, se um catálogo tiver 2 bilhões de linhas no índice mestre, um novo documento será indexado em um índice intermediário na memória, e as classificações desse documento com base no número de documentos no índice na memória poderão ser distorcidas em comparação com as classificações de documentos do índice mestre. Por esse motivo, recomendamos que, após qualquer população que resulte em um grande número de linhas sendo indexadas ou reindexadas, você mescle os índices em um índice mestre usando a instrução ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL. O Full-Text Engine também mescla automaticamente os índices com base em parâmetros como o número e o tamanho dos índices intermediários.

Os valores de MaxOccurrence de são normalizados em 32 intervalos. Isto significa, por exemplo, que um documento com 50 palavras é tratado da mesma forma que um documento com 100 palavras. A seguir está a tabela usada para normalização. Como os comprimentos dos documentos estão no intervalo entre os valores de tabela adjacentes 32 e 128, eles são efetivamente tratados como tendo o mesmo comprimento, 128 (32 <docLength<= 128).

{ 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 };

Classificação de CONTAINSTABLE

classificação CONTAINSTABLE usa o seguinte algoritmo:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )
Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )

As correspondências de frases são classificadas como chaves individuais, exceto que KeyRowCount (o número de linhas que contêm a frase) é estimado e pode ser impreciso e maior do que o número real.

Classificação de NEAR

CONTAINSTABLE suporta a consulta de dois ou mais termos de pesquisa próximos uns dos outros usando a opção NEAR. O valor de classificação de cada linha retornada é baseado em vários parâmetros. Um fator importante de classificação em rankings é o número total de ocorrências (ou acertos) em relação ao tamanho do documento. Assim, por exemplo, se um documento de 100 palavras e um documento de 900 palavras contiverem correspondências idênticas, o documento de 100 palavras será classificado mais alto.

O comprimento total de cada resultado numa linha também contribui para a classificação dessa linha, com base na distância entre o primeiro e o último termos de pesquisa nesse resultado. Quanto menor a distância, mais o impacto contribui para o valor de classificação da linha. Se uma consulta de texto completo não especificar um inteiro como a distância máxima, um documento que contenha apenas ocorrências cujas distâncias sejam superiores a 100 termos lógicos de distância terá uma classificação de 0.

Classificação de ISABOUT

CONTAINSTABLE oferece suporte à consulta de termos ponderados usando a opção ISABOUT. ISABOUT é uma consulta de espaço vetorial na terminologia tradicional de recuperação de informações. O algoritmo de classificação padrão usado é Jaccard, uma fórmula amplamente conhecida. A classificação é calculada para cada termo na consulta e, em seguida, combinada, conforme descrito no algoritmo a seguir.

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 ) )

Classificação de FREETEXTTABLE

a classificação FREETEXTTABLE é baseada na fórmula de classificação OKAPI BM25. FREETEXTTABLE consultas adicionam palavras à consulta através da geração flexional (formas flexionadas das palavras de consulta originais); Estas palavras são tratadas como palavras separadas, sem nenhuma relação especial com as palavras a partir das quais foram geradas. Os sinónimos gerados a partir da característica do Thesaurus são tratados como termos separados e igualmente ponderados. Cada palavra na consulta contribui para a classificação.

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.

Conteúdo relacionado

• Consulta com Full-Text Pesquisa