RANK を使用して検索結果を制限する

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance

CONTAINSTABLE 関数と FREETEXTTABLE 関数は、0 から 1000 までの序数値 (順位値) を含む "RANK" という名前の列を返します。 これらの値を使用すれば、選択基準への適合度合いに応じて、返された行に順位を付けることができます。 この順位値が示しているのは、結果セット内の各行の単なる相対順位であり、値が小さいほど関連性は低くなります。 実際の値は重要ではなく、通常はクエリが実行されるたびに変わります。

検索条件と一致するアイテムの数が膨大になることがよくあります。 CONTAINSTABLE クエリまたは FREETEXTTABLE クエリから多数の一致結果が返されないようにするには、オプション top_n_by_rank パラメーターを使用して行のサブセットのみを返します。 top_n_by_rank は整数値です。 nは、降順で上位 n 個の一致結果のみを返すことを示します。 top_n_by_rank を他のパラメーターと組み合わせた場合、クエリから返される行数は、実際にすべての述語に一致する行数より少なくなります。

SQL Server は、一致結果を順位で並べ替え、指定された数の行のみを返します。 このオプションを使用すると、パフォーマンスが大幅に向上します。 たとえば、通常ならば 100 万行のテーブルから 10 万行を返すクエリに対して、上位 100 行だけを返すように要求すれば、クエリの処理が速くなります。

RANK を使用して検索結果を制限する例

例 A: 上位 3 件の一致結果のみを検索する

次の例では、CONTAINSTABLE を使用して上位 3 件の一致結果のみを返します。

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 件の一致結果を検索する

次の例では、CONTAINSTABLE を使用して Description 列内で "light" または "lightweight" という単語の近くに "aluminum" という語句を含んでいる、上位 5 種の製品の説明を返します。

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 のフルテキスト検索では、フルテキスト クエリが返すデータの関連性を示す省略可能なスコア (順位値) を生成できます。 この順位値は 1 行ごとに計算され、クエリの結果セットを関連性に従って並べ替える順序付け基準として使用できます。 順位値は、単に、結果セット内の各行の相対的な関連順位を示します。 実際の値は重要ではなく、通常はクエリが実行されるたびに変わります。 順位値は、他のクエリでは意味を持ちません。

順位付けに使用する統計

順位付けに使用する統計はインデックス作成時に収集されます。 フルテキスト カタログの作成処理で、単一のインデックス構造が直接作成されるわけではありません。 代わりに、データにインデックスが作成されたとき、Full-Text Engine for SQL Server によって中間インデックスが作成されます。 その後、Full-Text Engine は必要に応じてこれらのインデックスをより大きなインデックスにマージします。 この処理は何度も繰り返される場合があります。 次に、Full-Text Engine は "マスター マージ" と呼ばれる処理を行います。この処理では、すべての中間インデックスが 1 つの巨大なインデックスに結合されます。

統計は各中間インデックス レベルで収集されます。 インデックスがマージされると、統計もマージされます。 マスターのマージ処理中にのみ生成される統計もあります。

SQL Server は、クエリ結果セットの順位付けを行う際に、最も大きい中間インデックスの統計を使用します。 これは、中間インデックスがマージされているかどうかに依存します。 したがって、中間インデックスがマージされていない場合、順位付けの統計の精度にばらつきが生じることがあります。 時間が経つにつれて同じクエリが異なる順位結果を返すことがあるのは、フルテキスト インデックス データの追加、変更、および削除が行われ、小さなインデックスがマージされていくためです。

インデックスのサイズを最小化し、計算を容易にするために、統計はしばしば丸められます。

共通に使用される用語と順位の計算に必要な統計値を次の表に示します。

順位計算に関する問題点

順位の計算処理はさまざまな要因に依存します。 ワード ブレーカーの言語が異なると、テキストをトークン化する方法も異なります。 たとえば、あるワード ブレーカーは "dog-house" という文字列を "dog" "house" に分割しますが、別のワード ブレーカーは "dog-house" に分割します。 これは、指定された言語によって一致および順位が異なるということを意味します。単語だけでなく、ドキュメント長も異なるからです。 ドキュメント長の違いは、クエリすべての順位付けに影響します。

IndexRowCount などの統計は大きく異なる場合があります。 たとえば、カタログのマスター インデックスに 20 億の行が格納されている場合、1 つの新規ドキュメントにメモリ内で中間インデックスが作成され、そのメモリ内インデックスのドキュメント数に基づいてドキュメントが順位付けされると、マスター インデックスのドキュメントの順位と整合性のない結果になる可能性があります。 こうした理由から、大量の行がインデックス化または再インデックス化された場合は、ALTER FULLTEXT CATALOG ... REORGANIZE Transact-SQL ステートメントを使用してこれらのインデックスをマスター インデックスにマージすることを推奨します。 また、中間インデックスの数やサイズなどのパラメーターを基に、Full-Text Engine によるインデックスのマージも自動的に実行されます。

MaxOccurrence の値は 1 ～ 32 の範囲のいずれかに正規化されます。 これは、たとえば 50 語のドキュメントが 100 語のドキュメントと同様に扱われるということを意味します。 正規化に使用される表を以下に示します。 これらのドキュメント長は以下の表の 32 と 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 オプションを使用することで、相互に近接する 2 つ以上の検索語句に対するクエリをサポートしています。 返される各行の順位値は、いくつかのパラメーターに基づいています。 順位付けの主な要因の 1 つは、ドキュメントの長さに関連した一致の合計数 ( ヒット) です。 したがって、たとえば、100 語のドキュメントと 900 語のドキュメントに同一の一致が含まれている場合は、100 語のドキュメントの方が順位が高くなります。

行における各ヒットの合計の長さも、そのヒットの最初と最後の検索語句の間の距離に基づいて、その行の順位に影響を与えます。 距離が小さいほど、ヒットは行の順位値に大きく影響します フルテキスト クエリで最大距離の整数が指定されていない場合、論理語の間隔が 100 を超えるヒットのみが含まれるドキュメントの順位は 0 になります。

ISABOUT の順位付け

CONTAINSTABLE は、ISABOUT オプションを指定した重み付け語句のクエリをサポートします。 ISABOUT は、従来の情報検索用語で言うところのベクトル空間クエリです。 既定の順位付けアルゴリズムには、Jaccard という一般的に知られている公式が使用されます。 クエリ内の用語ごとに順位付けが計算され、次のアルゴリズムで示す方法で各順位付けが結合されます。

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.

関連するコンテンツ

• フルテキスト検索でのクエリ