Ogranicz wyniki wyszukiwania przy użyciu funkcji RANK
Dotyczy:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Funkcje CONTAINSTABLE i FREETEXTTABLE zwracają kolumnę o nazwie RANK zawierającą wartości porządkowe od 0 do 1000 (wartości rangi). Te wartości są używane do klasyfikowania wierszy na podstawie stopnia zgodności z kryteriami wyboru. Wartości rangi wskazują tylko względną kolejność istotności wierszy w zestawie wyników z niższą wartością wskazującą niższą istotność. Rzeczywiste wartości są nieistotne i zwykle różnią się za każdym razem, gdy zapytanie jest uruchamiane.
Notatka
Predykaty CONTAINS i FREETEXT nie zwracają żadnych wartości klasyfikacji.
Liczba elementów pasujących do warunku wyszukiwania jest często duża. Aby zapobiec zwracaniu zbyt wielu dopasowań w zapytaniach CONTAINSTABLE lub FREETEXTTABLE, użyj opcjonalnego parametru top_n_by_rank, który zwraca tylko podzbiór wierszy.
top_n_by_rank jest wartością całkowitą, n, która określa, że zwracane są tylko n najwyżej sklasyfikowanych dopasowań, w kolejności malejącej. Jeśli top_n_by_rank jest połączona z innymi parametrami, zapytanie może zwrócić mniej wierszy niż liczba wierszy, które faktycznie pasują do wszystkich predykatów.
Program SQL Server porządkuje dopasowania według rangi i zwraca tylko do określonej liczby wierszy. Ten wybór może spowodować dramatyczny wzrost wydajności. Na przykład zapytanie, które zwykle zwraca 100 000 wierszy z tabeli zawierającej 1000 000 wierszy, jest przetwarzane szybciej, jeśli zażądano tylko 100 pierwszych wierszy.
Przykłady użycia funkcji RANK w celu ograniczenia wyników wyszukiwania
Przykład A: wyszukiwanie tylko trzech najlepszych dopasowań
W poniższym przykładzie użyto CONTAINSTABLE, aby zwrócić tylko trzy pierwsze dopasowania.
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
Oto zestaw wyników.
RANK Address City
----------- -------------------------------- ------------------------------
172 9005, rue des Bouchers Paris
172 5, rue des Bouchers Orleans
172 5, rue des Bouchers Metz
Przykład B: wyszukiwanie pięciu najlepszych dopasowań
W poniższym przykładzie użyto CONTAINSTABLE, aby zwrócić opis pięciu pierwszych produktów, w których kolumna Description zawiera słowo "aluminium" w pobliżu wyrazu light lub słowa 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
Jak są klasyfikowane wyniki zapytania wyszukiwania
Wyszukiwanie pełnotekstowe w programie SQL Server może wygenerować opcjonalny wynik (lub wartość rangi), który wskazuje istotność danych zwracanych przez zapytanie pełnotekstowe. Ta wartość rangi jest obliczana dla każdego wiersza i może służyć jako kryteria porządkowania w celu sortowania zestawu wyników danego zapytania według istotności. Wartości rangi wskazują tylko względną kolejność istotności wierszy w zestawie wyników. Rzeczywiste wartości są nieistotne i zwykle różnią się za każdym razem, gdy zapytanie jest uruchamiane. Wartość rangi nie zawiera żadnego znaczenia w zapytaniach.
Statystyki dotyczące klasyfikacji
Po utworzeniu indeksu statystyki są zbierane do użycia w klasyfikacji. Proces tworzenia wykazu pełnotekstowego nie powoduje bezpośredniego utworzenia pojedynczej struktury indeksu. Zamiast tego aparat Full-Text dla programu SQL Server tworzy indeksy pośrednie w miarę indeksowania danych. Aparat Full-Text następnie scala te indeksy z większym indeksem zgodnie z potrzebami. Ten proces można powtórzyć wiele razy. Następnie aparat Full-Text przeprowadza "scalanie nadrzędne", które łączy wszystkie indeksy pośrednie w jeden duży indeks główny.
Statystyki są zbierane na każdym poziomie indeksu pośredniego. Statystyki są scalane po scaleniu indeksów. Niektóre wartości statystyczne można wygenerować tylko podczas procesu scalania głównego.
Podczas gdy program SQL Server plasuje zestaw wyników zapytania, używa statystyk z największego indeksu pośredniego. Zależy to od tego, czy indeksy pośrednie są scalane, czy nie. W związku z tym statystyki klasyfikacji mogą się różnić w dokładności, jeśli indeksy pośrednie nie są scalane. W tym artykule wyjaśniono, dlaczego to samo zapytanie może zwracać różne wyniki klasyfikacji w miarę dodawania, modyfikowania i usuwania danych indeksowanych pełnotekstowo oraz scalania mniejszych indeksów.
Aby zminimalizować rozmiar indeksu i złożoności obliczeniowej, statystyki są często zaokrąglane.
Poniższa lista zawiera niektóre często używane terminy i wartości statystyczne, które są ważne podczas obliczania rangi.
| Termin/wartość | Opis |
|---|---|
| właściwości | Kolumna indeksowana pełnotekstowo w wierszu. |
| Dokument | Jednostka zwracana w zapytaniach. W programie SQL Server odpowiada to wierszowi. Dokument może mieć wiele właściwości, podobnie jak wiersz może zawierać wiele kolumn indeksowanych pełnotekstowo. |
| Indeks | Pojedynczy odwrócony indeks jednego lub większej liczby dokumentów. Może to być całkowicie w pamięci lub na dysku. Wiele statystyk zapytań odnosi się do pojedynczego indeksu, w którym wystąpiło dopasowanie. |
| Full-Text katalog | Kolekcja indeksów pośrednich traktowana jako jedna jednostka dla zapytań. Wykazy są jednostką organizacji widoczną dla administratora programu SQL Server. |
| Word, token lub element | Jednostka dopasowania w silniku pełnotekstowym. Strumienie tekstu z dokumentów są tokenizowane na wyrazy lub tokeny według podziałów wyrazowych typowych dla danego języka. |
| wystąpienia | Przesunięcie wyrazu we właściwości dokumentu ustalone przez narzędzie dzielące wyrazy. Pierwsze słowo znajduje się przy wystąpieniu 1, kolejne przy 2 i tak dalej. Aby uniknąć fałszywych wyników pozytywnych w zapytaniach frazowych i zbliżeniowych, koniec zdania i koniec akapitu wprowadzają większe luki w wystąpieniach. |
| CzęstotliwośćTerminu | Liczba wystąpień wartości klucza w wierszu. |
| indeksowanaLiczbaWierszy | Łączna liczba indeksowanych wierszy. Jest to obliczane na podstawie liczby utrzymywanych w indeksach pośrednich. Ta liczba może się różnić w dokładności. |
| KeyRowCount | Całkowita liczba wierszy w wykazie pełnotekstowym zawierającym dany klucz. |
| Maksymalna Wystąpienie | Największe wystąpienie przechowywane w wykazie pełnotekstowym dla danej właściwości w wierszu. |
| MaxQueryRank | Maksymalna ranga, 1000, zwrócona przez silnik Full-Text. |
Problemy z obliczaniem rangi
Proces klasyfikacji obliczeniowej zależy od wielu czynników. Różne systemy dzielenia słów tokenizują tekst w zależności od języka. Na przykład ciąg "dog-house" można podzielić na "pies" "dom" przez jeden dzielnik wyrazów, a na "dog-house" przez inny. Oznacza to, że dopasowanie i klasyfikacja różnią się w zależności od określonego języka, ponieważ nie tylko słowa są różne, ale tak jest długość dokumentu. Różnica długości dokumentu może mieć wpływ na klasyfikację wszystkich zapytań.
Statystyki takie jak IndexRowCount mogą się znacznie różnić. Na przykład, jeśli katalog zawiera 2 miliardy wierszy w indeksie głównym, to nowy dokument jest indeksowany w tymczasowym indeksie znajdującym się w pamięci, a jego ranking oparty na liczbie dokumentów w tym indeksie może być zniekształcony w porównaniu do rankingów dokumentów z indeksu głównego. Z tego powodu zalecamy, aby po każdej operacji w wyniku której duża liczba wierszy jest indeksowana lub ponownie indeksowana, scalono indeksy z głównym indeksem przy użyciu instrukcji ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL. Silnik Full-Text również automatycznie scala indeksy na podstawie parametrów, takich jak liczba i rozmiar indeksów pośrednich.
wartości MaxOccurrence są znormalizowane do jednego z 32 zakresów. Oznacza to na przykład, że dokument o długości 50 wyrazów jest traktowany tak samo jak w przypadku dokumentu o długości 100 wyrazów. Poniżej znajduje się tabela używana do normalizacji. Ponieważ długość dokumentu znajduje się w zakresie między sąsiednimi wartościami tabeli 32 i 128, są one skutecznie traktowane jako o tej samej długości, 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 };
Ranga funkcji CONTAINSTABLE
klasyfikacji CONTAINSTABLE używa następującego algorytmu:
StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )
Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )
Dopasowania fraz są klasyfikowane podobnie jak poszczególne klucze, z tą różnicą, że KeyRowCount (liczba wierszy zawierających frazę) jest szacowana i może być niedokładna i większa niż rzeczywista liczba.
Ranga BLISKIEJ
CONTAINSTABLE obsługuje wykonywanie zapytań dotyczących co najmniej dwóch terminów wyszukiwania znajdujących się w pobliżu siebie przy użyciu opcji NEAR. Wartość rangi każdego zwróconego wiersza jest oparta na kilku parametrach. Jednym z głównych czynników klasyfikacji jest całkowita liczba dopasowań (lub trafień) w stosunku do długości dokumentu. W związku z tym, jeśli na przykład dokument o długości 100 słów i dokument o długości 900 słów zawierają identyczne dopasowania, dokument o długości 100 słów jest klasyfikowany wyżej.
Łączna długość każdego trafienia z rzędu przyczynia się również do klasyfikacji tego wiersza na podstawie odległości między pierwszym i ostatnimi terminami wyszukiwania tego trafienia. Im mniejsza odległość, tym bardziej trafienie przyczynia się do wartości rangi wiersza. Jeśli zapytanie pełnotekstowe nie określa liczby całkowitej jako maksymalnej odległości, dokument zawierający tylko trafienia, których odległości są większe niż 100 terminów logicznych, ma klasyfikację 0.
Ranga ISABOUT
CONTAINSTABLE obsługuje wykonywanie zapytań dotyczących terminów ważonych przy użyciu opcji ISABOUT.
ISABOUT to zapytanie dotyczące przestrzeni wektorowej w tradycyjnej terminologii pobierania informacji. Domyślny używany algorytm klasyfikacji to Jaccard, powszechnie znana formuła. Ranking jest obliczany dla każdego terminu w zapytaniu, a następnie są łączone, jak opisano w poniższym algorytmie.
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 ) )
Ranga dla FREETEXTTABLE
FREETEXTTABLE ranking opiera się na formule rankingu OKAPI BM25.
FREETEXTTABLE zapytania dodają wyrazy do zapytania za pośrednictwem generowania form fleksyjnych (odmiany fleksyjne oryginalnych wyrazów zapytania); te słowa traktowane są jako osobne słowa, bez specjalnej relacji ze słów, z których zostały wygenerowane. Synonimy wygenerowane na podstawie funkcji Thesaurus są traktowane jako oddzielne, równie ważone terminy. Każde słowo w zapytaniu przyczynia się do rangi.
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.