Limiter les résultats de la recherche avec RANK

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Les fonctions CONTAINSTABLE et FREETEXTTABLE renvoient une colonne nommée RANK contenant des valeurs ordinales de 0 à 1 000 (valeurs de rang). Ces valeurs classent les lignes selon leur adéquation avec les critères de sélection. Les valeurs de classement indiquent uniquement un ordre relatif de pertinence pour les lignes du jeu de résultats. Une valeur inférieure indique une pertinence plus faible. Les valeurs réelles sont sans importance et diffèrent généralement à chaque exécution de la requête.

Le nombre d'éléments répondant à une condition de recherche est souvent élevé. Pour éviter que les requêtes CONTAINSTABLE ou FREETEXTTABLE ne retournent trop de correspondances, utilisez le paramètre optionnel top_n_by_rank. Il ne retourne qu’un sous-ensemble de lignes. top_n_by_rank est une valeur entière, n, qui spécifie que seules les n correspondances de classement le plus élevé doivent être retournées, par ordre décroissant. Si top_n_by_rank est associé à d’autres paramètres, la requête peut retourner moins de lignes que le nombre de lignes correspondant effectivement à tous les prédicats.

Le Moteur de base de données SQL Server ordonne les correspondances par rang et ne retourne que jusqu’au nombre de lignes spécifié. Par exemple, une requête qui retourne normalement 100 000 lignes d’un tableau de 1 000 000 de lignes est traitée plus rapidement si seules les 100 premières lignes sont demandées.

Exemples d’utilisation de RANK pour limiter les résultats de recherche

Exemple A : recherche des trois premières correspondances uniquement

L'exemple suivant utilise CONTAINSTABLE pour retourner uniquement les trois premières correspondances.

USE AdventureWorks2025;
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

Voici l’ensemble de résultats.

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

Exemple B : recherche des cinq premières correspondances

L'exemple suivant utilise CONTAINSTABLE pour retourner la description des 5 premiers produits dont la colonne Description contient les mots « aluminum » à proximité du mot light ou lightweight.

USE AdventureWorks2025;
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

Classement des résultats d'une requête de recherche

La recherche en texte intégral peut générer un score optionnel (ou une valeur de rang) qui indique la pertinence des données retournées par une requête en texte intégral. Cette valeur de rang est calculée sur chaque ligne et peut être utilisée comme critère d’ordre pour trier l’ensemble de résultats d’une requête donnée selon la pertinence. Les valeurs de classement indiquent uniquement un ordre relatif de pertinence pour les lignes contenues dans le jeu de résultats. Les valeurs réelles sont sans importance et sont généralement différentes d'une exécution de requête à une autre. La valeur de classement n'a pas de signification entre les requêtes.

Statistiques de classement

Lorsque vous construisez un index, le système collecte des statistiques à utiliser dans le classement. Construire un catalogue en texte intégral ne crée pas directement une structure d’index unique. À la place, le Full-Text Engine crée des indices intermédiaires en indexant les données. Le moteur de recherche en texte intégral fusionne ensuite ces index dans un index plus volumineux, si nécessaire. Ce processus peut se produire plusieurs fois. Le Moteur d'indexation et de recherche en texte intégral mène ensuite une « fusion principale » qui associe tous les index intermédiaires dans un index principal de plus grande taille.

Des statistiques sont recueillies à chaque niveau d'index intermédiaire. Les statistiques sont fusionnées lorsque les indices sont fusionnés. Certaines valeurs statistiques ne peuvent être générées que durant la fusion principale.

Bien que le Moteur de base de données classe un ensemble de résultats de requêtes, il utilise des statistiques provenant du plus grand index intermédiaire. Cette utilisation dépend de la fusion des indices intermédiaires. Par conséquent, les statistiques de classement peuvent varier en précision si les index intermédiaires ne sont pas fusionnés. Cette différence de précision explique pourquoi la même requête peut restituer des résultats de rang différents au fil du temps, à mesure que des données indexées en texte intégral sont ajoutées, modifiées et supprimées, et que les petits index sont fusionnés.

Pour réduire la taille des index et la complexité des calculs, les statistiques sont souvent arrondies.

La liste ci-dessous comprend des termes et des valeurs statistiques fréquemment utilisés, qui sont d'une grande importance dans le calcul du classement.

Problèmes de calcul de rang

De nombreux facteurs influencent le processus de calcul du classement. Les analyseurs lexicaux des diverses langues créent des jetons de texte de manière différente. Par exemple, un séparateur de mots découpe la chaîne « dog-house » en « dog » et « house », mais un autre tient compte de « dog-house » comme un seul mot. L’appariement et le classement varient selon la langue spécifiée, car non seulement les mots sont différents, mais aussi la longueur du document. La différence de longueur d'un document peut affecter le classement pour toutes les requêtes.

Les statistiques telles que IndexRowCount peuvent fortement varier. Par exemple, si un catalogue compte 2 milliards de lignes dans l’index maître, un nouveau document est indexé dans un index intermédiaire en mémoire, et les rangs de ce document en fonction du nombre de documents dans l’index en mémoire peuvent être déséquilibrés par rapport aux rangs des documents issus de l’index maître. Pour cette raison, après toute population qui entraîne un grand nombre de lignes indexées ou réindexées, fusionnez les indices en un index maître en utilisant l’instruction ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL. Le moteur d'indexation et de recherche en texte intégral fusionne automatiquement les index en fonction de paramètres tels que le nombre et la taille des index intermédiaires.

Les valeursMaxOccurrence sont normalisées sous forme de 32 plages individuelles. Cette normalisation signifie, par exemple, qu’un document de 50 mots est traité de la même manière qu’un document de 100 mots. Le tableau suivant montre la normalisation. Dans la mesure où les documents ont une longueur comprise dans la plage située entre les valeurs adjacentes 32 et 128 du tableau, ils sont effectivement traités comme s'ils avaient le même nombre de mots, c'est-à-dire 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 };

Classement de CONTAINSTABLE

Le classement deCONTAINSTABLE utilise l’algorithme suivant :

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

Les correspondances de phrases sont classées comme les clés individuelles, sauf que KeyRowCount (le nombre de lignes contenant la phrase) est une valeur estimée qui peut être inexacte et supérieure au nombre réel.

Classement de NEAR

CONTAINSTABLE prend en charge l'interrogation de plusieurs termes de recherche à proximité l'un de l'autre à l'aide de l'option NEAR. La valeur de classement de chaque ligne retournée est basée sur plusieurs paramètres. L’un des principaux facteurs de classement est le nombre total de correspondances (ou occurrences) par rapport à la longueur du document. Par exemple, si un document de 100 mots et un document de 900 mots contiennent des correspondances identiques, le document de 100 mots a un classement supérieur.

La longueur totale de chaque résultat dans une ligne contribue également au classement de cette ligne en fonction de la distance entre le premier et le dernier terme de recherche de ce résultat. Plus la distance est faible, plus le résultat contribue à la valeur du classement de la ligne. Si une requête en texte intégral ne spécifie pas un entier comme distance maximale, un document contenant uniquement des résultats dont les distances sont supérieures à 100 termes logiques a un classement de 0.

Classement de ISABOUT

CONTAINSTABLE prend en charge les requêtes de recherche de termes pondérés à l'aide de l'option ISABOUT. ISABOUT est une requête d'espace vectoriel dans la terminologie traditionnelle de récupération d'informations. L'algorithme de classement par défaut utilisé est celui de Jaccard, une formule très connue. Le classement est calculé pour chaque nouveau terme de la requête et est ensuite combiné comme indiqué dans l’algorithme suivant.

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

Classement de FREETEXTTABLE

Le classement deFREETEXTTABLE est basé sur la formule de classement OKAPI BM25. FREETEXTTABLE Les requêtes ajoutent des mots dans la requête par génération de formes fléchies (formes fléchies des mots d’origine de la requête). Ces mots sont considérés comme des mots séparés, sans relation particulière avec les mots dont ils sont issus. Les synonymes générés à partir de la fonctionnalité du dictionnaire des synonymes sont traités comme des termes distincts, de même pondération. Chaque mot de la requête contribue au rang.

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.

Contenu connexe

• Exécuter une requête avec une recherche en texte intégral