Limiter les résultats de recherche avec RANK

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

Les fonctions CONTAINSTABLE et FREETEXTTABLE retournent une colonne nommée RANK qui contient des valeurs ordinales comprises entre 0 et 1 000 (valeurs de classement). Ces valeurs servent à établir le rang des lignes retournées en fonction de leur correspondance par rapport aux 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 sont généralement différentes d'une exécution de requête à une autre.

Le nombre d’éléments correspondant à une condition de recherche est souvent volumineux. Pour empêcher CONTAINSTABLE ou FREETEXTTABLE les requêtes de retourner trop de correspondances, utilisez le paramètre facultatif top_n_by_rank , qui retourne uniquement un sous-ensemble de lignes. top_n_by_rank est une valeur entière, n, qui spécifie que seules les correspondances classées les plus élevées doivent être retournées, dans l’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.

SQL Server trie les correspondances par rang et retourne uniquement jusqu’au nombre spécifié de lignes. Ce choix peut considérablement améliorer les performances. Par exemple, une requête qui retourne normalement 100 000 lignes d’une table de 1 000 000 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 la recherche

Exemple A : recherche des trois premières correspondances uniquement

L’exemple suivant utilise CONTAINSTABLE pour renvoyer uniquement les trois premières correspondances.

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

Voici le jeu de résultats obtenu.

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 renvoyer la description des cinq premiers produits où la Description colonne contient le mot « aluminium » près du mot light ou du mot 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

Classement des résultats des requêtes de recherche

La recherche en texte intégral dans SQL Server peut générer un score facultatif (ou une valeur de classement) qui indique la pertinence des données retournées par une requête en texte intégral. Cette valeur de classement est calculée sur chaque ligne et peut être utilisée comme critère de tri pour trier le jeu de résultats d'une requête donnée par 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 ne contient aucune importance entre les requêtes.

Statistiques pour le classement

Lors de la création d'un index, des statistiques sont recueillies à des fins de classement. Le processus de création d’un catalogue de texte intégral n’entraîne pas directement une structure d’index unique. Au lieu de cela, le moteur de recherche en texte intégral pour SQL Server crée des index intermédiaires à mesure que les données sont indexées. Il fusionne ensuite ces index dans un index de plus grande taille en fonction de vos besoins. Ce processus peut se répéter à plusieurs reprises. 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. Elles fusionnent en même temps que les index. Certaines valeurs statistiques ne peuvent être générées que durant la fusion principale.

Bien que SQL Server classe un jeu de résultats de requête, il utilise des statistiques à partir du plus grand index intermédiaire. Cela dépend du fait que les index intermédiaires sont fusionnés ou non. Par conséquent, les statistiques de classement peuvent varier en précision si les index intermédiaires ne sont pas fusionnés. C'est la raison pour laquelle la même requête peut retourner différents résultats dans le temps en fonction de l'ajout, de la modification et de la suppression des données indexées en texte intégral, et en fonction de la fusion des index plus petits.

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

La liste suivante comprend des termes couramment utilisés et des valeurs statistiques importantes dans le calcul du classement.

Problèmes de calcul de classement

Le processus de classement informatique dépend de nombreux facteurs. Les analyseurs lexicaux des diverses langues créent des jetons de texte de manière différente. Par exemple, la chaîne « après-midi » peut être décomposée en « après » « midi » par un analyseur lexical et en « après-midi » par un autre. Cela signifie que la correspondance et le classement varient en fonction de la langue spécifiée, car non seulement sont les mots différents, mais il s’agit donc de 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 a 2 milliards de lignes dans l'index principal, un nouveau document est indexé dans un index intermédiaire en mémoire ; par ailleurs, les rangs de ce document qui sont basés sur le nombre de documents dans l'index en mémoire peuvent être incorrects par rapport aux rangs des documents de l'index principal. Pour cette raison, nous vous recommandons de fusionner les index dans un index maître à l’aide de l’instruction ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL après une population qui entraîne un grand nombre de lignes indexées ou réindexées. Le moteur de recherche en texte intégral fusionne également 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. Cela signifie, par exemple, qu’un document de 50 mots longs est traité comme un document long de 100 mots. Voici le tableau utilisé pour la normalisation. Étant donné que les longueurs du document sont comprises entre les valeurs de table adjacentes 32 et 128, elles sont traitées efficacement comme ayant la même longueur, 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 d’expressions sont classées comme des clés individuelles. Toutefois, KeyRowCount (nombre de lignes contenant l’expression) étant estimé, il peut être inexact et supérieur au nombre réel.

Rang de NEAR

CONTAINSTABLE prend en charge l’interrogation de deux termes de recherche ou plus en proximité entre eux à l’aide de l’option NEAR . La valeur de classement de chaque ligne retournée est basée sur plusieurs paramètres. Un facteur majeur du classement est le nombre total de correspondances (ou résultats) 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 correspondance dans une ligne contribue également au classement de cette ligne, en fonction de la distance entre les premiers et les derniers termes de recherche de cet accès. Plus la distance est faible, plus le résultat contribue à la valeur du classement de la ligne. Si une requête de texte intégral ne spécifie pas d’entier comme distance maximale, un document qui contient uniquement des correspondances dont les distances sont supérieures à 100 termes logiques, a un classement de 0.

Classement de ISABOUT

CONTAINSTABLE prend en charge l’interrogation des termes pondérés à l’aide de l’option ISABOUT . ISABOUT est une requête d’espace vectoriel dans la terminologie de récupération d’informations traditionnelle. L'algorithme de classement par défaut utilisé est celui de Jaccard, une formule très connue. Le classement est calculé pour chaque terme de la requête, puis combiné, comme décrit 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 à la requête par le biais d’une génération inflectionnelle (formes inflectées des mots de requête d’origine) ; ces mots sont traités comme des mots distincts, sans relation spéciale avec les mots à partir desquels ils ont été générés. 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