RANK를 사용하여 검색 결과 제한

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

CONTAINSTABLEFREETEXTTABLE 함수는 0에서 1000(순위 값)의 서수 값이 포함된 명명된 RANK 열을 반환합니다. 이러한 값은 선택 조건과 얼마나 잘 일치하는지에 따라 반환되는 행의 순위를 지정하는 데 사용됩니다. 순위 값은 결과 집합에 포함된 행의 관련성을 나타내는 상대적 순서일 뿐이며 값이 작을수록 관련이 없는 것입니다. 실제 값은 중요하지 않으며 일반적으로 쿼리가 실행될 때마다 달라집니다.

참고 항목

CONTAINS 조건 FREETEXT 자는 순위 값을 반환하지 않습니다.

검색 조건과 일치하는 항목 수가 많은 경우가 많습니다. 쿼리가 너무 많은 일치 항목을 반환하지 않도록 CONTAINSTABLEFREETEXTTABLE 하려면 행의 하위 집합만 반환하는 선택적 top_n_by_rank 매개 변수를 사용합니다. top_n_by_rank 가장 높은 순위 일치 항목만 내림차순으로 반환되도록 지정하는 정수 값인 n입니다. top_n_by_rank 다른 매개 변수와 결합된 경우 쿼리는 실제로 모든 조건자와 일치하는 행 수보다 적은 행을 반환할 수 있습니다.

SQL Server는 순위별로 일치 항목을 정렬하고 지정된 행 수까지만 반환합니다. 이 선택으로 인해 성능이 크게 향상될 수 있습니다. 예를 들어 상위 100개 행만 요청하면 일반적으로 1,000,000개의 행 테이블에서 100,000개의 행을 반환하는 쿼리가 더 빠르게 처리됩니다.

RANK를 사용하여 검색 결과 제한 예제

예제 A: 상위 3개 일치 항목만 검색

다음 예제에서는 상위 3개 일치 항목만 반환하는 데 사용합니다 CONTAINSTABLE .

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

결과 집합은 다음과 같습니다.

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

예제 B: 상위 5개 일치 항목 검색

다음 예제에서는 열에 단어 또는 단어 lightweight근처에 "알루미늄"이라는 단어가 포함된 상위 5개 제품에 Description 대한 설명을 반환하는 데 사용합니다CONTAINSTABLE.light

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

검색 쿼리 결과의 순위를 지정하는 방법

SQL Server의 전체 텍스트 검색은 전체 텍스트 쿼리에서 반환된 데이터의 관련성을 나타내는 선택적 점수(또는 순위 값)를 생성할 수 있습니다. 이 순위 값은 모든 행에서 계산되며 정렬 조건으로 사용하여 지정된 쿼리의 결과 집합을 관련성별로 정렬할 수 있습니다. 순위 값은 결과 집합에 있는 행의 상대적인 관련성 순서만 나타냅니다. 실제 값은 중요하지 않으며 일반적으로 쿼리가 실행될 때마다 달라집니다. 순위 값은 쿼리에서 어떤 의미도 유지하지 않습니다.

순위 통계

인덱스가 작성되면 순위에 사용하기 위해 통계가 수집됩니다. 전체 텍스트 카탈로그를 빌드하는 프로세스에서는 단일 인덱스 구조가 직접 생성되지 않습니다. 대신 SQL Server용 전체 텍스트 엔진은 데이터가 인덱싱될 때 중간 인덱스를 만듭니다. 그런 다음 전체 텍스트 검색 엔진은 필요에 따라 이러한 인덱스를 큰 인덱스로 병합합니다. 이 프로세스는 여러 번 반복될 수 있습니다. 그런 다음 전체 텍스트 엔진은 모든 중간 인덱스를 하나의 큰 마스터 인덱스로 결합하는 "마스터 병합"을 수행합니다.

통계는 각 중간 인덱스 수준에서 수집됩니다. 인덱스를 병합할 때 통계도 병합됩니다. 일부 통계 값은 마스터 병합 프로세스 중에만 생성할 수 있습니다.

SQL Server는 쿼리 결과 집합의 순위를 지정하지만 가장 큰 중간 인덱스의 통계를 사용합니다. 중간 인덱스가 병합되는지 여부에 따라 달라집니다. 따라서 중간 인덱스가 병합되지 않으면 순위 통계의 정확도가 달라질 수 있습니다. 이렇게 하면 전체 텍스트 인덱싱된 데이터가 추가, 수정 및 삭제되고 더 작은 인덱스가 병합될 때 동일한 쿼리가 시간에 따라 다른 순위 결과를 반환할 수 있는 이유를 설명합니다.

인덱스 크기와 복잡한 계산을 최소화하기 위해 통계를 반올림하는 경우가 많습니다.

다음 목록에는 순위를 계산하는 데 중요한 몇 가지 일반적으로 사용되는 용어 및 통계 값이 포함되어 있습니다.

용어/값 설명
속성 행의 전체 텍스트 인덱싱된 열입니다.
Document 쿼리에서 반환되는 엔터티입니다. SQL Server에서 행에 해당합니다. 행에 여러 개의 전체 텍스트 인덱싱된 열이 있을 수 있는 것처럼 문서에는 여러 속성이 있을 수 있습니다.
Index 하나 이상의 문서에 대해 반전된 단일 인덱스입니다. 이는 전적으로 메모리 또는 디스크에 있을 수 있습니다. 많은 쿼리 통계는 일치가 발생한 개별 인덱스를 기준으로 합니다.
전체 텍스트 카탈로그 쿼리에 대해 하나의 엔터티로 처리되는 중간 인덱스 컬렉션입니다. 카탈로그는 SQL Server 관리자가 볼 수 있는 조직의 단위입니다.
Word, 토큰 또는 항목 전체 텍스트 검색 엔진에서 일치하는 단위입니다. 문서의 텍스트 스트림 단어 또는 언어별 단어 분리기를 통해 토큰으로 토큰화됩니다.
발생 단어 분리기에 의해 결정된 문서 속성의 단어 오프셋입니다. 첫 번째 단어는 1번, 다음 단어는 2번 등입니다. 구 및 근접 쿼리에서 가양성을 방지하기 위해 문장 끝과 단락 끝은 더 큰 간격을 발생합니다.
TermFrequency 해당 키 값이 행에서 나오는 횟수입니다.
IndexedRowCount 인덱싱된 총 행 수입니다. 중간 인덱스에 기본 개수에 따라 계산됩니다. 정확도가 달라질 수 있습니다.
KeyRowCount 전체 텍스트 카탈로그에서 지정된 키가 포함된 전체 행 수입니다.
MaxOccurrence 지정된 행 속성에 대해 전체 텍스트 카탈로그에 저장된 최대 발생 횟수입니다.
MaxQueryRank 전체 텍스트 엔진에서 반환하는 최대 순위(1000)입니다.

계산 문제 순위 지정

순위 계산 프로세스는 여러 요인에 따라 달라집니다. 다른 언어 단어 분리기는 텍스트를 다르게 토큰화합니다. 예를 들어 문자열 "dog-house"는 한 단어 분리기에 의해 "개" "집"으로 나뉘고 다른 단어 분리기는 "dog-house"로 나눌 수 있습니다. 즉, 단어는 다를 뿐만 아니라 문서 길이도 다르기 때문에 지정한 언어에 따라 일치 및 순위가 달라집니다. 문서 길이 차이는 모든 쿼리의 순위에 영향을 줄 수 있습니다.

IndexRowCount와 같은 통계는 매우 다양할 수 있습니다. 예를 들어 카탈로그에 마스터 인덱스에 20억 개의 행이 있는 경우 새 문서 하나가 메모리 내 중간 인덱스로 인덱싱되고 메모리 내 인덱스의 문서 수를 기준으로 해당 문서의 순위가 마스터 인덱스의 문서 순위와 비교하여 왜곡될 수 있습니다. 따라서 많은 수의 행이 인덱싱되거나 다시 인덱싱되는 채우기 후에 Transact-SQL 문을 사용하여 ALTER FULLTEXT CATALOG ... REORGANIZE 인덱스를 마스터 인덱스에 병합하는 것이 좋습니다. 또한 전체 텍스트 엔진은 중간 인덱스의 수 및 크기와 같은 매개 변수를 기반으로 인덱스를 자동으로 병합합니다.

MaxOccurrence 값은 32개 범위 중 하나로 정규화됩니다. 예를 들어 문서 길이가 50단어인 문서는 100단어 길이의 문서와 동일하게 처리됩니다. 다음은 정규화에 사용되는 테이블입니다. 문서 길이는 인접한 테이블 값 32와 128 사이의 범위에 있으므로 길이가 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 };

CONTAINSTABLE의 순위

CONTAINSTABLE 순위는 다음 알고리즘을 사용합니다.

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

구 일치는 KeyRowCount (구가 포함된 행 수)가 예상 개수이므로 부정확하고 실제 개수보다 많을 수 있는 점을 제외하고 개별 키와 마찬가지로 순위가 지정됩니다.

NEAR의 순위

CONTAINSTABLE 는 옵션을 사용하여 NEAR 서로 근접한 두 개 이상의 검색어를 쿼리할 수 있도록 지원합니다. 반환된 각 행의 순위 값은 여러 매개 변수를 기반으로 합니다. 한 가지 주요 순위 요소는 문서 길이를 기준으로 하는 총 일치 항목(또는 적중 횟수)입니다. 따라서 예를 들어 100 단어 문서와 900 단어 문서에 동일한 일치 항목이 포함된 경우 100 단어 문서의 순위가 더 높습니다.

각 적중 항목의 총 길이는 해당 적중 항목의 첫 번째 검색어와 마지막 검색어 사이의 거리를 기준으로 해당 행의 순위에도 영향을 줍니다. 거리가 작을수록 적중이 행의 순위 값에 더 많이 영향을 줍니다. 전체 텍스트 쿼리가 정수를 최대 거리로 지정하지 않는 경우 거리가 100개의 논리적 용어를 초과하는 적중 항목만 포함된 문서에는 순위가 0입니다.

ISABOUT 순위

CONTAINSTABLE 는 옵션을 사용하여 가중치가 지정된 용어에 대한 쿼리를 ISABOUT 지원합니다. ISABOUT 는 기존 정보 검색 용어의 벡터 공간 쿼리입니다. 사용되는 기본 순위 알고리즘은 널리 알려진 수식인 Jac카드입니다. 순위는 다음 알고리즘에 설명된 대로 쿼리의 각 용어에 대해 계산된 다음 결합됩니다.

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

FREETEXTTABLE의 순위

FREETEXTTABLE 순위는 OKAPI BM25 순위 수식을 기반으로 합니다. FREETEXTTABLE 쿼리는 변곡 생성(원래 쿼리 단어의 굴절된 형태)을 통해 쿼리에 단어를 추가합니다. 이러한 단어는 생성된 단어와 특별한 관계가 없는 별도의 단어로 처리됩니다. 사전 기능에서 생성된 동의어도 가중치가 같은 별도의 단어로 처리됩니다. 쿼리의 각 단어는 순위에 기여합니다.

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.