Zoekresultaten beperken met RANK

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

De functies CONTAINSTABLE en FREETEXTTABLE retourneren een kolom met de naam RANK die rangtelwaarden van 0 tot en met 1000 (rangschikkingswaarden) bevat. Deze waarden worden gebruikt om de rijen te rangschikken die worden geretourneerd op basis van de mate waarin ze voldoen aan de selectiecriteria. De rangschikkingswaarden geven alleen een relatieve volgorde van relevantie aan van de rijen in de resultatenset, met een lagere waarde die een lagere relevantie aangeeft. De werkelijke waarden zijn onbelangrijk en verschillen doorgaans telkens wanneer de query wordt uitgevoerd.

Notitie

De CONTAINS en FREETEXT predicaten retourneren geen rangwaarden.

Het aantal items dat overeenkomt met een zoekvoorwaarde is vaak groot. Als u wilt voorkomen dat CONTAINSTABLE of FREETEXTTABLE query's te veel overeenkomsten retourneren, gebruikt u de optionele parameter top_n_by_rank, die alleen een subset rijen retourneert. top_n_by_rank is een geheel getal, n, waarmee wordt aangegeven dat alleen de n n hoogste gerangschikte overeenkomsten moeten worden geretourneerd, in aflopende volgorde. Als top_n_by_rank wordt gecombineerd met andere parameters, kan de query minder rijen retourneren dan het aantal rijen dat daadwerkelijk overeenkomt met alle predicaten.

SQL Server rangschikt de overeenkomsten op rang en retourneert alleen het opgegeven aantal rijen. Deze keuze kan leiden tot een aanzienlijke toename van de prestaties. Een query die normaal gesproken 100.000 rijen uit een tabel van 100.000.000 rijen retourneert, worden bijvoorbeeld sneller verwerkt als alleen de bovenste 100 rijen worden aangevraagd.

Voorbeelden van het gebruik van RANK om zoekresultaten te beperken

Voorbeeld A: alleen zoeken naar de drie belangrijkste overeenkomsten

In het volgende voorbeeld wordt CONTAINSTABLE gebruikt om alleen de top drie overeenkomsten te retourneren.

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 zijn de resultaten.

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

Voorbeeld B: Zoeken naar de top vijf resultaten

In het volgende voorbeeld wordt CONTAINSTABLE gebruikt om de beschrijving van de top vijf producten te retourneren waarbij de kolom Description het woord "aluminium" bevat in de buurt van het woord light of het woord 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

Hoe zoekresultaten worden gerangschikt

Zoeken in volledige tekst in SQL Server kan een optionele score (of rangschikkingswaarde) genereren die de relevantie aangeeft van de gegevens die worden geretourneerd door een query in volledige tekst. Deze rangschikkingswaarde wordt berekend op elke rij en kan worden gebruikt als rangschikkingscriteria om de resultatenset van een bepaalde query te sorteren op relevantie. De rangschikkingswaarden geven alleen een relatieve volgorde van relevantie aan van de rijen in de resultatenset. De werkelijke waarden zijn onbelangrijk en verschillen doorgaans telkens wanneer de query wordt uitgevoerd. De rangschikkingswaarde heeft geen betekenis voor query's.

Statistieken voor rangschikking

Wanneer een index wordt gemaakt, worden statistieken verzameld voor gebruik in classificatie. Het proces voor het bouwen van een catalogus met volledige tekst leidt niet rechtstreeks tot één indexstructuur. In plaats daarvan maakt de Full-Text Engine voor SQL Server tussenliggende indexen wanneer gegevens worden geïndexeerd. De Full-Text Engine voegt deze indexen vervolgens indien nodig samen in een grotere index. Dit proces kan vaak worden herhaald. De Full-Text Engine voert vervolgens een 'hoofdsamenvoeging' uit die alle tussenliggende indexen combineert tot één grote hoofdindex.

Statistieken worden verzameld op elk tussenliggend indexniveau. De statistieken worden samengevoegd wanneer de indexen worden samengevoegd. Sommige statistische waarden kunnen alleen worden gegenereerd tijdens het hoofdsamenvoegproces.

Sql Server rangschikt een queryresultatenset, maar maakt gebruik van statistieken uit de grootste tussenliggende index. Dit is afhankelijk van of tussenliggende indexen worden samengevoegd of niet. Als gevolg hiervan kunnen classificatiestatistieken in nauwkeurigheid variëren als de tussenliggende indexen niet worden samengevoegd. Dit verklaart waarom dezelfde query in de loop van de tijd verschillende rangschikkingsresultaten kan retourneren als geïndexeerde gegevens in volledige tekst worden toegevoegd, gewijzigd en verwijderd, en omdat de kleinere indexen worden samengevoegd.

Om de grootte van de index en de complexiteit van de berekening te minimaliseren, worden statistieken vaak afgerond.

De volgende lijst bevat enkele veelgebruikte termen en statistische waarden die belangrijk zijn bij het berekenen van rang.

Term/waarde	Beschrijving
eigenschap	Een kolom van de rij met full-text indexering.
Document	De entiteit die wordt geretourneerd in queries. In SQL Server komt dit overeen met een rij. Een document kan meerdere eigenschappen hebben, net zoals een rij meerdere geïndexeerde kolommen met volledige tekst kan hebben.
Index	Eén omgekeerde index van een of meer documenten. Dit kan volledig in het geheugen of op schijf zijn. Veel querystatistieken zijn gerelateerd aan de afzonderlijke index waar de overeenkomst plaatsvond.
Full-Text Catalogus	Een verzameling tussenliggende indexen die worden behandeld als één entiteit voor query's. Catalogi zijn de organisatie-eenheid die zichtbaar is voor de SQL Server-beheerder.
Word, token of item	De eenheid van overeenkomende tekst in de engine voor volledige tekst. Tekststromen uit documenten worden getokend in woorden of tokens door taalspecifieke woordonderbrekers.
Voorkomen	Het woord wordt verschoven in een documenteigenschap zoals bepaald door de woordonderbreker. Het eerste woord is op positie 1, het volgende op 2, en zo verder. Om fout-positieven in uitdrukkings- en nabijheidsquery's te voorkomen, kunnen er bij het einde van een zin en het einde van een alinea grotere hiaten ontstaan.
Termfrequentie-	Het aantal keren dat de sleutelwaarde in een rij voorkomt.
IndexedRowCount-	Totaal aantal geïndexeerde rijen. Dit wordt berekend op basis van aantallen die worden onderhouden in de tussenliggende indexen. Dit getal kan variëren in nauwkeurigheid.
KeyRowCount	Totaal aantal rijen in de catalogus met volledige tekst die een bepaalde sleutel bevatten.
MaxOccurrence	Het grootste voorkomen dat is opgeslagen in een voltekstcatalogus voor een bepaalde eigenschap in een rij.
MaxQueryRank-	De maximumpositie, 1000, geretourneerd door de Full-Text Engine.

Problemen met rangschikkingsberekeningen

Het proces van het berekenen van de rang is afhankelijk van veel factoren. Verschillende taaltermonderbrekers tokeniseren tekst anders. De tekenreeks 'hond-huis' kan bijvoorbeeld worden opgesplitst in 'hond' 'huis' door één woordonderbreker en in 'hond-huis' door een ander. Dit betekent dat overeenkomende en rangschikking varieert op basis van de opgegeven taal, omdat niet alleen de woorden verschillen, maar ook de lengte van het document. Het verschil in de lengte van het document kan van invloed zijn op de rangorde voor alle query's.

Statistieken zoals IndexRowCount kunnen sterk variëren. Als een catalogus bijvoorbeeld 2 miljard rijen in de hoofdindex bevat, wordt er één nieuw document geïndexeerd in een tussenliggende index in het geheugen, en worden de rangschikkingen voor dat document op basis van het aantal documenten in de geheugenindex scheef vergeleken met die van documenten uit de hoofdindex. Daarom raden we aan dat na elke populatie die resulteert in een groot aantal rijen dat wordt geïndexeerd of opnieuw wordt geïndexeerd, u de indexen samenvoegt in een hoofdindex met behulp van de ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL-instructie. De Full-Text Engine voegt ook automatisch de indexen samen op basis van parameters zoals het aantal en de grootte van tussenliggende indexen.

MaxOccurrence waarden worden genormaliseerd in 1 van 32 reeksen. Dit betekent bijvoorbeeld dat een document 50 woorden lang is, hetzelfde wordt behandeld als een document van 100 woorden lang. Hier volgt de tabel die wordt gebruikt voor normalisatie. Omdat de documentlengten zich in het bereik tussen aangrenzende tabelwaarden 32 en 128 bevinden, worden ze effectief behandeld als dezelfde lengte, 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 van CONTAINSTABLE

CONTAINSTABLE rangschikking maakt gebruik van het volgende algoritme:

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

Fraseovereenkomsten worden op dezelfde wijze gerangschikt als afzonderlijke sleutels, behalve dat KeyRowCount (het aantal rijen met de frase) wordt geschat en onnauwkeurig kan zijn en hoger uitvallen dan het werkelijke getal.

Ranglijst van NEAR

CONTAINSTABLE ondersteunt het uitvoeren van query's op twee of meer zoektermen in nabijheid met elkaar met behulp van de optie NEAR. De rangschikkingswaarde van elke geretourneerde rij is gebaseerd op verschillende parameters. Een belangrijke classificatiefactor is de totale hoeveelheid overeenkomsten (of treffers) ten opzichte van de lengte van het document. Als bijvoorbeeld een 100-word-document en een 900-word-document identieke overeenkomsten bevatten, wordt het 100-word-document hoger gerangschikt.

De totale lengte van elke treffer in een rij draagt ook bij aan de rangorde van die rij, op basis van de afstand tussen de eerste en laatste zoektermen van die hit. Hoe kleiner de afstand, hoe meer de hit bijdraagt aan de rangschikkingswaarde van de rij. Als een voltekstquery geen geheel getal opgeeft als maximale afstand, krijgt een document dat alleen treffers bevat waarvan de afstanden groter zijn dan 100 logische termen, een rangschikking van 0.

Rang van ISABOUT

CONTAINSTABLE ondersteunt het uitvoeren van query's op gewogen termen met behulp van de optie ISABOUT. ISABOUT is een vectorruimtequery in traditionele terminologie voor het ophalen van gegevens. Het standaardalgoritme voor classificatie is Jaccard, een veelgebruikte formule. De classificatie wordt berekend voor elke term in de query en vervolgens gecombineerd, zoals beschreven in het volgende algoritme.

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

Positie van FREETEXTTABLE

FREETEXTTABLE rangschikking is gebaseerd op de Okapi BM25-rangschikkingsformule. FREETEXTTABLE query's woorden toevoegen aan de query via inflectionele generatie (inflectionele vormen van de oorspronkelijke querywoorden); deze woorden worden behandeld als afzonderlijke woorden, zonder speciale relatie met de woorden waaruit ze zijn gegenereerd. Synoniemen die zijn gegenereerd op basis van de synoniemenlijstfunctie, worden beschouwd als afzonderlijke, even gewogen termen. Elk woord in de query draagt bij aan de rangschikking.

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.

Verwante inhoud

• de query uitvoeren met Full-Text