Einschränken von Suchergebnissen mit RANK

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Die Funktionen CONTAINSTABLE und FREETEXTTABLE geben eine Spalte mit dem Namen RANK zurück, die Ordinalwerte zwischen 0 und 1.000 (Rangwerte) enthält. Diese Werte werden verwendet, um die Rangfolge der zurückgegebenen Zeilen gemäß ihrer Übereinstimmung mit den Auswahlkriterien festzulegen. Die Rangwerte geben lediglich eine relative Relevanzreihenfolge der Zeilen im Resultset an, wobei ein niedrigerer Wert eine niedrigere Relevanz anzeigt. Die tatsächlichen Werte sind nicht von Bedeutung und unterscheiden sich i. d. R. bei jeder Ausführung der Abfrage.

Hinweis

Die Prädikate CONTAINS und FREETEXT geben keine Rangwerte zurück.

Die Anzahl der Elemente, die eine Suchbedingung erfüllen, ist oft groß. Damit CONTAINSTABLE oder FREETEXTTABLE-Abfragen nicht zu viele Übereinstimmungen zurückgeben, können Sie den optionalen top_n_by_rank-Parameter verwenden, mit dem nur eine Teilmenge der Zeilen zurückgegeben wird. top_n_by_rank ist ein Integer-Wert ( n) mit dem festgelegt wird, dass nur die n höchsten Übereinstimmungen in absteigender Reihenfolge zurückgegeben werden. Wenn top_n_by_rank mit anderen Parametern kombiniert wird, werden von der Abfrage möglicherweise weniger Zeilen zurückgegeben als die Anzahl von Zeilen, die mit allen Prädikaten übereinstimmen.

SQL Server ordnet die Übereinstimmungen nach Rang und gibt nur die angegebene Anzahl Zeilen zurück. Diese Einschränkung kann zu einer deutlichen Leistungssteigerung führen. So wird z. B. eine Abfrage, die normalerweise 100.000 Zeilen aus einer Tabelle mit 1.000.000 Zeilen zurückgeben würde, bedeutend schneller verarbeitet, wenn nur die obersten 100 Zeilen angefordert werden.

Beispiele zur Verwendung von RANK zum Einschränken der Suchergebnisse

Beispiel A: Suchen nach ausschließlich den obersten drei Übereinstimmungen

Im folgenden Beispiel werden mit CONTAINSTABLE nur die obersten drei Übereinstimmungen zurückgegeben.

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

Hier ist das Resultset.

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

Beispiel B: Suchen nach ausschließlich den obersten fünf Übereinstimmungen

Das folgende Beispiel verwendet CONTAINSTABLE, um die Beschreibung der fünf wichtigsten Produkte zurückzugeben, bei denen die Spalte Description das Wort "Aluminium" in der Nähe des Wortes light oder des Wortes lightweight enthält.

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

Ordnen von Suchabfrageergebnissen nach Rang

Die Volltextsuche in SQL Server kann eine optionale Bewertung (einen Rangwert) generieren, die die Relevanz der von einer Volltextabfrage zurückgegebenen Daten angibt. Dieser Rangwert wird für jede Zeile berechnet und als Sortierkriterium verwendet, um das Resultset einer bestimmten Abfrage nach Relevanz zu sortieren. Die Rangwerte geben lediglich eine relative Relevanzreihenfolge der Zeilen im Resultset an. Die tatsächlichen Werte sind nicht von Bedeutung und unterscheiden sich i. d. R. bei jeder Ausführung der Abfrage. Der Rangwert hat keinerlei abfrageüberschreitende Bedeutung.

Statistiken für die Rangfolge

Beim Erstellen eines Indexes werden Statistiken für die Reihenfolgebestimmung gesammelt. Der Vorgang der Erstellung eines Volltextkatalogs führt nicht direkt zu einer einzelnen Indexstruktur. Stattdessen erstellt die Volltext-Engine für SQL Server beim Indizieren der Daten Zwischenindizes. Anschließend werden diese Indizes von der Volltextsuch-Engine bei Bedarf in einen größeren Index zusammengeführt. Dieser Vorgang kann mehrfach wiederholt werden. Die Volltextsuch-Engine führt einen "Mastermergeprozess" aus, bei dem alle Zwischenindizes zu einem größeren Masterindex kombiniert werden.

Auf jeder Zwischenstufe werden Statistiken erhoben. Die Statistiken werden beim Zusammenführen der Indizes zusammengeführt. Einige statistische Werte können nur während des Mastermergeprozesses generiert werden.

Beim Generieren der Rangfolge für ein Abfrageresultset verwendet SQL Server Statistiken vom größten Zwischenindex. Dies hängt davon ab, ob Zwischenindizes zusammengeführt werden oder nicht. Demzufolge kann die Rangfolgestatistik unterschiedlich genau ausfallen, wenn die Zwischenindizes nicht zusammengeführt werden. Dies erklärt, warum dieselbe Abfrage zu verschiedenen Zeitpunkten unterschiedliche Rangergebnisse zurückgeben kann, wenn volltextindizierte Daten hinzugefügt, geändert und gelöscht werden und wenn die kleineren Indizes zusammengeführt werden.

Häufig werden die Statistiken gerundet, um die Größe des Indexes und die Komplexität der Berechnung zu minimieren.

Die folgende Liste enthält einige häufig verwendete Begriffe und statistische Werte, die beim Berechnen des Rangs wichtig sind:

Ausdruck / Wert	Beschreibung
Eigenschaft	Eine volltextindizierte Spalte der Zeile.
Document	Die Entität, die in Abfragen zurückgegeben wird. In SQL Server entspricht dies einer Zeile. Ein Dokument kann mehrere Eigenschaften aufweisen, ebenso wie eine Zeile mehrere volltextindizierte Spalten aufweisen kann.
Index	Ein einzelner invertierter Index mindestens eines Dokuments. Er kann sich vollständig im Arbeitsspeicher oder auf dem Datenträger befinden. Viele Abfragestatistiken sind relativ zu dem jeweiligen Index, mit dem der Vergleich ausgeführt wurde.
Volltextkatalog	Eine Auflistung von Zwischenindizes, die für Abfragen als eine Entität behandelt wird. Kataloge sind die Organisationseinheit, die für den SQL Server-Administrator sichtbar ist.
Wort, Token oder Element	Die Vergleichseinheit in der Volltext-Engine. Textströme aus Dokumenten werden durch eine sprachspezifische Wörtertrennung in Wörter oder Token zerlegt.
Vorkommen	Der von der Wörtererkennung bestimmte Offset eines Worts in einer Dokumenteigenschaft. Das erste Wort stellt Vorkommen 1 dar, das nächste 2 usw. Um falsche Treffer in Ausdrucks- und NEAR-Abfragen zu vermeiden, bewirken Satzende- und Absatzendezeichen größere Abstände zwischen den Vorkommen.
TermFrequency	Die Anzahl der Vorkommen des Schlüsselwerts in einer Zeile.
IndexedRowCount	Gesamtanzahl der indizierten Zeilen. Diese wird aus den in den Zwischenindizes geführten Zählern berechnet. Die Genauigkeit der Anzahl kann variieren.
KeyRowCount	Gesamtanzahl der Zeilen im Volltextkatalog, die einen bestimmten Schlüssel enthalten.
MaxOccurrence	Das größte in einem Volltextkatalog gespeicherte Vorkommen für eine bestimmte Eigenschaft in einer Zeile.
MaxQueryRank	Der maximale Rang, 1000, der von der Volltextsuch-Engine zurückgegeben wird.

Gesichtspunkte bei der Rangberechnung

Der Vorgang der Rangberechnung hängt von mehreren Faktoren ab. Die Wörtererkennung für unterschiedliche Sprachen zerlegt Text unterschiedlich in Wörter. So könnte z. B. die Zeichenfolge "dog-house" von einer Wörtererkennung in "dog" "house" und von einer anderen in "dog-house" zerlegt werden. Dies bedeutet, dass Vergleiche und Rangfolgenberechnung je nach der angegebenen Sprache unterschiedliche Ergebnisse liefern, da nicht nur die Wörter unterschiedlich sind, sondern auch die Dokumentlänge. Die unterschiedliche Dokumentlänge kann sich auf die Rangfolgenberechnung für alle Abfragen auswirken.

Statistiken wie IndexRowCount können stark variieren. Hat z. B. ein Katalog 2 Milliarden Zeilen im Masterindex, wird ein einzelnes neues Dokument in einen im Arbeitsspeicher befindlichen Zwischenindex indiziert, und die Ränge für das Dokument, die auf der Anzahl der Dokumente im Index im Arbeitsspeicher basieren, können im Vergleich zu den Rängen für Dokumente aus dem Masterindex verfälscht sein. Daher empfehlen wir, dass Sie nach jeder Auffüllung, die zu einer großen Anzahl von indizierten oder neu indizierten Zeilen führt, die Indizes mit der ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL-Anweisung zu einem Master-Index zusammenführen. Entsprechend bestimmten Parametern, wie Anzahl und Größe der Zwischenindizes, werden die Indizes auch automatisch von der Volltextsuch-Engine zusammengeführt.

MaxOccurrence -Werte werden in den Bereich 1 bis 32 normalisiert. Das heißt, dass z. B. ein 50 Wörter langes Dokument genau so behandelt wird wie ein 100 Wörter langes. Die zur Normalisierung verwendete Tabelle ist nachfolgend aufgeführt. Da die Dokumentlängen im Bereich zwischen den benachbarten Tabellenwerten 32 und 128 liegen, werden sie behandelt, als hätten sie dieselbe Länge, nämlich 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 };

Rang von CONTAINSTABLE

Beim Generieren der Rangfolge fürCONTAINSTABLE wird der folgende Algorithmus verwendet:

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

Der Rang von Übereinstimmungen in Ausdrücken wird genauso bestimmt wie einzelne Schlüssel, außer dass KeyRowCount (die Anzahl von Zeilen, die den Ausdruck enthalten) geschätzt wird und somit ungenau und höher als die tatsächliche Anzahl ausfallen kann.

Rang von NEAR

CONTAINSTABLE unterstützt das Abfragen von zwei oder mehr Suchbegriffen mithilfe der NEAR-Option hinsichtlich ihrer Nähe zueinander. Der Rangwert der einzelnen zurückgegebenen Zeilen basiert auf mehreren Parametern. Ein Hauptrangfaktor ist die Gesamtzahl der Übereinstimmungen (oder Treffer) in Bezug auf die Länge des Dokuments. Wenn beispielsweise ein Dokument mit 100 Wörtern und ein Dokument mit 900 Wörtern identische Übereinstimmungen enthalten, wird dem Dokument mit 100 Wörtern ein höherer Rangwert zugewiesen.

Die Gesamtlänge der einzelnen Treffer in einer Zeile trägt ebenfalls zum Rangwert der betreffenden Zeile bei, wobei die Entfernung zwischen dem ersten und dem letzten Suchbegriff des jeweiligen Treffers zugrunde gelegt wird. Je kleiner die Entfernung, desto relevanter ist der Treffer für den Rangwert der Zeile. Wenn eine Volltextabfrage keine ganze Zahl angibt (z. B. die maximale Entfernung), weist ein Dokument, das nur Treffer enthält, deren Entfernungen weiter als 100 logische Begriffe auseinander liegen, einen Rang von 0 (null) auf.

Rang von ISABOUT

CONTAINSTABLE unterstützt das Abfragen von gewichteten Begriffen mit der ISABOUT-Option. ISABOUT ist eine Vektorraumabfrage in traditioneller Information Retrieval-Terminologie. Der verwendete Standardalgorithmus zur Rangfolgenberechnung ist Jaccard, eine bekannte Formel. Die Rangfolge wird für jeden Begriff in der Abfrage berechnet und dann, wie im folgenden Algorithmus beschrieben, kombiniert.

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

Rang von FREETEXTTABLE

Die Rangfolgenberechnung fürFREETEXTTABLE basiert auf der OKAPI BM25-Rangfolgenformel. Bei FREETEXTTABLE-Abfragen werden der Abfrage durch Wortformengenerierung Flexionsformen der ursprünglichen Abfragewörter hinzugefügt; diese Wörter werden als separate Wörter ohne besondere Beziehung zu den Wörtern behandelt, aus denen sie generiert wurden. Aus der Thesaurus-Funktion generierte Synonyme werden als separate, gleich gewichtete Begriffe behandelt. Jedes Wort in der Abfrage wird bei der Rangberechnung einbezogen.

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.

Zugehöriger Inhalt

• Abfragen mit Volltextsuche