フルテキストインデックスのテキスト検索で検索ランクで並び替える - SQL Server

SQL Serverでフリーテキスト検索の結果をランク順に並び替えるSQL文を紹介します。

概要

こちらの記事では、SQL Serverのフリーテキスト検索のSQL文を紹介しました。検索結果は表示できましたが、一致度が高いもの先に表示したい場合があります。この記事では、ランクを利用して一致度の高い順に検索結果を並び替えるSQLを紹介します。

ランクの表示

フリーテキスト検索の結果のランクはFREETEXTTABLE CONTAINSTABLE で参照できます。関数として定義されていますが、戻り値でテーブルを返す関数のため、テーブル名のように記述して利用できます。
FREETEXTTABLE, CONTAINSTABLEのRANK列の値が大きいものほどより一致する列になります。

書式

書式は以下の通りになります。

FREETEXTTABLE

select (列名) from (テーブル名) INNER JOIN FREETEXTTABLE( (列名), (テーブル名), '(検索語句)') TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]

CONTAINSTABLE

select (列名) from (テーブル名) INNER JOIN CONTAINSTABLE( (列名), (テーブル名), '(検索語句)') TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]

記述例

FREETEXTTABLE

SELECT id,title,FT.[KEY], FT.RANK FROM Contents
 INNER JOIN FREETEXTTABLE(Contents,(title,document),'スクロール Web') FT ON Contents.Id = FT.[Key]

CONTAINSTABLE

select id,title,FT.[KEY],　FT.RANK from Contents
 INNER JOIN CONTAINSTABLE(Contents, (title,document),'フライト NEAR ビジネス', Language 0) FT ON Contents.Id = FT.[Key]

例

以下のデータのテーブルを準備します。

Working テーブル
id	name	value	category	memo
1	Penguin	300	B	南国にすむペンギンです
2	Whale	420	M	北極海のクジラです
3	Moffu	880	NULL	よくわからない生き物です
4	Camel	220	M	砂漠にすむラクダです
5	Owl	90	B	関東のフクロウです
6	Duck	120	B	そこらへんのアヒルです
7	Lizard	60	R	ひっそりと生きるトカゲです

テーブルを作成後、フルテキストインデックスを作成します。フルテキストインデックス作成手順は次の記事を参照してください。

フルテキストインデックス作成後、下記のSQLを実行します。

FREETEXTTABLEを実行します。

select * from Working INNER JOIN FREETEXTTABLE(Working, (name,memo), 'すむ トカゲ') FT ON Working.Id = FT.[Key];

下図の結果が表示されます。RANK列が表示され、結果のランクの値が表示されています。

CONTAINSTABLEを実行します。

select * from Working INNER JOIN CONTAINSTABLE(Working, (name,memo), '海 NEAR クジラ') FT ON Working.Id = FT.[Key];

下図の結果が表示されます。RANK列が表示され、結果のランクの値が表示されています。

ランク順表示

ランク順に表示する場合は、FREETEXTTABLE CONTAINSTABLE の RANK列の値で逆順にソートします。

書式

書式は以下の通りになります。

FREETEXTTABLE

select (列名) from (テーブル名)
 INNER JOIN FREETEXTTABLE( (列名), (テーブル名), '(検索語句)') TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]
 ORDER BY TEMP_TABLE_NAME.[RANK] desc

上位の検索結果のみを取得する場合は、FREETEXTTABLEの第5引数に結果を取得する個数を指定して検索速度を向上させることができます。

select (列名) from (テーブル名)
 INNER JOIN FREETEXTTABLE( (列名), (テーブル名), '(検索語句)', LANGUAGE 0, (ランクの大きいものを取得する個数)) TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]
 ORDER BY TEMP_TABLE_NAME.[RANK] desc

CONTAINSTABLE

select (列名) from (テーブル名)
 INNER JOIN CONTAINSTABLE( (列名), (テーブル名), '(検索語句)') TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]
 ORDER BY TEMP_TABLE_NAME.[RANK] desc

上位の検索結果のみを取得する場合は、CONTAINSTABLEの第5引数に結果を取得する個数を指定して検索速度を向上させることができます。

select (列名) from (テーブル名)
 INNER JOIN CONTAINSTABLE( (列名), (テーブル名), '(検索語句)', LANGUAGE 0, (ランクの大きいものを取得する個数)) TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]
 ORDER BY TEMP_TABLE_NAME.[RANK] desc

記述例

select id,title,FT.[KEY],　FT.RANK from Contents 
  INNER JOIN FREETEXTTABLE(Contents,(title,document),'CPU コード', Language 0, 32) FT ON Contents.Id = FT.[Key]
  order by FT.RANK desc

例

先の例のテーブルで、次のSQL文を実行します。

select * from Working
INNER JOIN FREETEXTTABLE(Working, (name,memo), 'すむ ラクダ') FT ON Working.Id = FT.[Key]
ORDER BY FT.RANK DESC;

以下の結果が表示されます。RANK順に表示されています。

著者

neg

iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。

目次

フルテキストインデックスのテキスト検索で検索ランクで並び替える - SQL Server

概要

ランクの表示

書式

FREETEXTTABLE

CONTAINSTABLE

記述例

FREETEXTTABLE

CONTAINSTABLE

例

ランク順表示

書式

FREETEXTTABLE

CONTAINSTABLE

記述例

例

関連するページ