SQL Serverでフリーテキスト検索の結果をランク順に並び替えるSQL文を紹介します。
こちらの記事では、SQL Serverのフリーテキスト検索のSQL文を紹介しました。
検索結果は表示できましたが、一致度が高いもの先に表示したい場合があります。
この記事では、ランクを利用して一致度の高い順に検索結果を並び替えるSQLを紹介します。
FREETEXTTABLE CONTAINSTABLE で参照できます。
関数として定義されていますが、戻り値でテーブルを返す関数のため、テーブル名のように記述して利用できます。
FREETEXTTABLE, CONTAINSTABLEのRANK列の値が大きいものほどより一致する列になります。
書式は以下の通りになります。
select (列名) from (テーブル名) INNER JOIN FREETEXTTABLE( (列名), (テーブル名), '(検索語句)') TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]
select (列名) from (テーブル名) INNER JOIN CONTAINSTABLE( (列名), (テーブル名), '(検索語句)') TEMP_TABLE_NAME ON Contents.Id = TEMP_TABLE_NAME.[Key]
SELECT id,title,FT.[KEY], FT.RANK FROM Contents
INNER JOIN FREETEXTTABLE(Contents,(title,document),'スクロール Web') FT ON Contents.Id = FT.[Key]
select id,title,FT.[KEY], FT.RANK from Contents
INNER JOIN CONTAINSTABLE(Contents, (title,document),'フライト NEAR ビジネス', Language 0) FT ON Contents.Id = FT.[Key]
以下のデータのテーブルを準備します。
| 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 | ひっそりと生きるトカゲです |
テーブルを作成後、フルテキストインデックスを作成します。フルテキストインデックス作成手順は次の記事を参照してください。
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列の値で逆順にソートします。書式は以下の通りになります。
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
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順に表示されています。
