やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

📌 ？
トップ > SQLite3 > SQLite3学習　FTS5の補助関数

SQLite3学習　FTS5の補助関数

SQLite3

　SQL関数に似ている。だがFTSクエリ内でしか使えない。

成果物

情報源

組込の補助関数

補助関数 概要
bm25() 一致の精度を示す数値を返す 低いほど良い一致
snippet() 一致した行からテキスト断片を抜き出す
highlight() 一致したフレーズの前後に任意のテキストを追加する

bm25()

　以下のように書く。

SELECT * FROM fts WHERE fts MATCH ? ORDER BY bm25(fts);

　コード例。以前ビルドしたMeCab用トークナイザを使っている。

.load ./fts5_mecab
create virtual table posts using fts5(title, content, tokenize='mecab');
insert into posts values('タイトルです', '本文です。');
insert into posts values('タイトなタイトルです', '本文章は本文です。');

　bm25()で一致率が高い順にする。

select * from posts where posts match 'タイト*' order by bm25(posts);

タイトなタイトルです|本文章は本文です。
タイトルです|本文です。

　ソートしなければ以下のように挿入順になった。

select * from posts where posts match 'タイト*';

タイトルです|本文です。
タイトなタイトルです|本文章は本文です。

　フレーズの一致数が多いほど優先順位が高くなると思われる。

　列ごとに重み付けできる。デフォルトは10.0。引数の位置は列の定義位置に対応する。

insert into posts values('表題です', 'タイトな本文です。');

select * from posts where posts match 'タイト*' order by bm25(posts, 2.0, 9.0);

表題です|タイトな本文です。
タイトなタイトルです|本文章は本文です。
タイトルです|本文です。

　なんと、タイト*に一致するのが1つしかない行が先頭になった。これは重み付けによるもの。

列 重み
title 2.0
content 9.0
title content 重み
表題です タイトな本文です。 9
タイトなタイトルです 本文章は本文です。 4
タイトルです 本文です。 2

　タイトとタイトルでは重み付けが違う計算方法を使ってみるかもしれない。だが、それは誤差として概算する。詳しく知りたければ英語のwikipedia参照。

　おなじ重み付けでソートすると以下。

select * from posts where posts match 'タイト*' order by bm25(posts);

タイトなタイトルです|本文章は本文です。
タイトルです|本文です。
表題です|タイトな本文です。

　タイト*に一致した数が多い行が最上位。

ちなみに、ソートしなければ挿入順。

select * from posts where posts match 'タイト*';

タイトルです|本文です。
タイトなタイトルです|本文章は本文です。
表題です|タイトな本文です。

highlight()

　マッチ箇所の前後にテキストを挿入して返す。

　以下のように書く。

SELECT highlight(fts, 0, '<b>', '</b>') FROM fts WHERE fts MATCH ?

　4つの引数はそれぞれ以下。

  1. 対象テーブル名
  2. テキストを読み取るFTSテーブル列のインデックスを示す整数(0〜)
  3. 各フレーズが一致する前に挿入するテキスト
  4. 各フレーズが一致した後に挿入するテキスト
CREATE VIRTUAL TABLE ft USING fts5(a);
INSERT INTO ft VALUES('a b c x c d e');
INSERT INTO ft VALUES('a b c c d e');
INSERT INTO ft VALUES('a b c d e');

SELECT highlight(ft, 0, '[', ']') FROM ft WHERE ft MATCH 'a+b+c AND c+d+e';

[a b c] x [c d e]
[a b c] [c d e]
[a b c d e]

　日本語で使ってみる。

.load ./fts5_mecab
create virtual table posts using fts5(title, content, tokenize='mecab');
insert into posts values('タイトルです', '本文です。');
insert into posts values('タイトなタイトルです', '本文章は本文です。');

SELECT highlight(posts, 0, '<b>', '</b>') FROM posts WHERE posts MATCH 'タイトル'

<b>タイトル</b>です
タイトな<b>タイトル</b>です

　title, contentの列を取得してみる。

SELECT highlight(posts, 0, '<b>', '</b>'), highlight(posts, 1, '<b>', '</b>') FROM posts('タイト*');

<b>タイトル</b>です|本文です。
<b>タイト</b>な<b>タイトル</b>です|本文章は本文です。
表題です|<b>タイト</b>な本文です。

　ところで、検索結果の一部を目立たせたいときHTML5では<mark>のほうが正しいと思われる。

SELECT highlight(posts, 0, '<mark>', '</mark>'), highlight(posts, 1, '<mark>', '</mark>') FROM posts('タイト*');

<mark>タイトル</mark>です|本文です。
<mark>タイト</mark>な<mark>タイトル</mark>です|本文章は本文です。
表題です|<mark>タイト</mark>な本文です。

snippet()

　列の値を1行まるごとでなく一部を返す。

  1. テーブル名
  2. 返されたテキストを選択するFTSテーブル列のインデックスを示す整数。(0〜。負数: 列の自動選択)
  3. 返されるテキスト内で各フレーズが一致する前に挿入するテキスト。
  4. 返されたテキスト内で各フレーズが一致した後に挿入するテキスト。
  5. 選択されたテキストの先頭または末尾に追加するテキスト。それぞれ、返されたテキストがその列の先頭または末尾にないことを示す。
  6. 返されるテキスト内のトークンの最大数。(0<n && n<=64)
.load ./fts5_mecab
create virtual table posts using fts5(title, content, tokenize='mecab');
insert into posts values('タイトルです', '本文です。');
insert into posts values('タイトなタイトルです', '本文章は本文です。');

　最後の引数が1のとき。

SELECT snippet(posts, 0, '<mark>', '</mark>', '', 1) FROM posts WHERE posts MATCH 'タイト*';

<mark>タイトル</mark>
<mark>タイト</mark>な<mark>タイトル</mark>

　最後の引数が2のとき。

SELECT snippet(posts, 0, '<mark>', '</mark>', '', 2) FROM posts WHERE posts MATCH 'タイト*';

<mark>タイトル</mark>です
<mark>タイト</mark>な
表題です

　ん？　2のほうが1より短くなってる行があるのだが。なにこれ？　わからん。

補助関数の結果をソート

  • すべてのFTS5テーブルにはrankという名の特別な非表示列がある
  • 現在のクエリがフルテキストクエリでない（MATCH演算子がない）場合、rank列の値は常にNULL
    • それ以外の場合、フルテキストクエリではrank列にデフォルトで後続の引数なしでbm25()補助関数の結果が含まれる
-- 次のクエリは論理的に同等。2番目の方法は特に高速
-- 特に呼び出し元が以前にクエリを放棄した場合、すべての行が返された（またはクエリが変更された場合）LIMIT句を含めることができる。
SELECT * FROM fts WHERE fts MATCH ? ORDER BY bm25(fts);
SELECT * FROM fts WHERE fts MATCH ? ORDER BY rank;

　rank列のデフォルトを任意に設定できる。

rank MATCH 'auxiliary-function-name(arg1, arg2, ...)'
rank = 'auxiliary-function-name(arg1, arg2, ...)'

　match, =の右側は補助関数であるべき。その引数はSQLリテラルでなければならない。

-- 次のクエリは論理的に同等。2番目の方法は高速
SELECT * FROM fts WHERE fts MATCH ? ORDER BY bm25(fts, 10.0, 5.0);
SELECT * FROM fts WHERE fts MATCH ? AND rank MATCH 'bm25(10.0, 5.0)' ORDER BY rank;

　以下3つは同等。

SELECT * FROM fts WHERE fts MATCH ? AND rank MATCH 'bm25(10.0, 5.0)' ORDER BY rank;
SELECT * FROM fts WHERE fts = ? AND rank = 'bm25(10.0, 5.0)' ORDER BY rank;
SELECT * FROM fts WHERE fts(?, 'bm25(10.0, 5.0)') ORDER BY rank;

　以上、説明おわり。以下、試してみる。

.load ./fts5_mecab
create virtual table posts using fts5(title, content, tokenize='mecab');
insert into posts values('タイトルです', '本文です。');
insert into posts values('タイトなタイトルです', '本文章は本文です。');
insert into posts values('表題です', 'タイトな本文です。');

　ソートはorder by rankのほうが早い。order by bm25(fts)よりも。

select * from posts('タイト*') order by rank;

タイトなタイトルです|本文章は本文です。
タイトルです|本文です。
表題です|タイトな本文です。

　重み付けを変えるときはrankとbm25()を以下のいずれかのように書くと高速。（rankを使わずbm25()単独だと低速）

SELECT * FROM posts WHERE posts MATCH 'タイト*' AND rank = 'bm25(1.0, 5.0)' ORDER BY rank;
SELECT * FROM posts WHERE posts = 'タイト*' AND rank = 'bm25(1.0, 5.0)' ORDER BY rank;
SELECT * FROM posts('タイト*', 'bm25(1.0, 5.0)') ORDER BY rank;

表題です|タイトな本文です。
タイトなタイトルです|本文章は本文です。
タイトルです|本文です。

　列を指定したいなら以下。

SELECT * FROM posts WHERE posts MATCH 'content:タイト*' AND rank = 'bm25(1.0, 5.0)' ORDER BY rank;
SELECT * FROM posts WHERE posts = 'content:タイト*' AND rank = 'bm25(1.0, 5.0)' ORDER BY rank;
SELECT * FROM posts('content:タイト*', 'bm25(1.0, 5.0)') ORDER BY rank;

表題です|タイトな本文です。

所感

　ソートするならrankを使う。

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで