SQLiteの FTS（Full-Text Search） では、検索結果に関連度スコア（rank）を付与できます。
これに COUNT(*)（総件数）、MAX(rank)（最大スコア）、MIN(rank)（最小スコア）、AVG(rank)（平均スコア）を組み合わせることで、検索結果の 関連度分布 を分析できます。
これにより「検索結果全体の件数」「最も関連度が高い候補」「最も低い候補」「平均的な関連度」を把握できます。
FTSテーブル作成例
-- FTS5を利用した住所検索用テーブル
CREATE VIRTUAL TABLE PostalFTS USING fts5(
PostalCode,
Prefecture,
City,
Town,
content=''
);
-- 通常のPostalTableからデータをコピー
INSERT INTO PostalFTS(PostalCode, Prefecture, City, Town)
SELECT PostalCode, Prefecture, City, Town FROM PostalTable;
検索SQL例（分布分析）
-- 総件数、最大スコア、最小スコア、平均スコアを取得
SELECT COUNT(*) AS TotalCount,
MAX(rank) AS MaxScore,
MIN(rank) AS MinScore,
AVG(rank) AS AvgScore
FROM PostalFTS
WHERE PostalFTS MATCH '東京都';
-- ページング検索（例：2ページ目、10件ずつ）
SELECT PostalCode, Prefecture, City, Town, rank
FROM PostalFTS
WHERE PostalFTS MATCH '東京都'
ORDER BY rank
LIMIT 10 OFFSET 10;
VBAコード例（分布分析＋ページング）
Sub QueryPostalSQLiteFTSDistribution()
Dim conn As Object, rs As Object
Dim dbPath As String, keywords As String
Dim pageSize As Integer, pageNum As Integer
Dim totalCount As Long, maxScore As Double, minScore As Double, avgScore As Double
dbPath = "C:\data\PostalCache.sqlite"
' SQLite接続（ODBCドライバ利用）
Set conn = CreateObject("ADODB.Connection")
conn.Open "Driver=SQLite3 ODBC Driver;Database=" & dbPath & ";"
' 検索キーワード
keywords = "東京都"
' ページサイズとページ番号
pageSize = 10
pageNum = 2 ' 例：2ページ目
' 分布分析
Set rs = conn.Execute("SELECT COUNT(*), MAX(rank), MIN(rank), AVG(rank) " & _
"FROM PostalFTS WHERE PostalFTS MATCH '" & keywords & "'")
totalCount = rs.Fields(0).Value
maxScore = rs.Fields(1).Value
minScore = rs.Fields(2).Value
avgScore = rs.Fields(3).Value
rs.Close
' OFFSET計算
Dim offsetVal As Integer
offsetVal = (pageNum - 1) * pageSize
' ページング検索
Set rs = conn.Execute("SELECT PostalCode, Prefecture, City, Town, rank " & _
"FROM PostalFTS WHERE PostalFTS MATCH '" & keywords & "' " & _
"ORDER BY rank LIMIT " & pageSize & " OFFSET " & offsetVal)
' 結果をImmediate Windowに表示
Debug.Print "=== 検索結果: " & totalCount & "件中 ページ " & pageNum & " ==="
Debug.Print "最高スコア: " & maxScore & " / 最低スコア: " & minScore & " / 平均スコア: " & avgScore
Do Until rs.EOF
Debug.Print rs.Fields(0).Value & " → " & rs.Fields(1).Value & rs.Fields(2).Value & rs.Fields(3).Value & _
" (score=" & rs.Fields(4).Value & ")"
rs.MoveNext
Loop
rs.Close
conn.Close
End Sub
実行イメージ
検索条件：東京都
- 総件数：250件
- 最大スコア：12.0
- 最小スコア：2.0
- 平均スコア：6.5
- ページ番号：2ページ目（11〜20件目）
出力例：
=== 検索結果: 250件中 ページ 2 ===
最高スコア: 12 / 最低スコア: 2 / 平均スコア: 6.5
1000011 → 東京都千代田区大手町 (score=11)
1000012 → 東京都千代田区丸の内 (score=10)
...
実務での応用
- 検索結果の分布分析 → 関連度の高低を把握して検索精度を評価
- 顧客データ補完 → 曖昧な住所入力から「平均的な関連度」を基準に候補提示
- 検索UI改善 → ユーザーに「関連度の分布」を提示して信頼性を高める
- 分析業務 → 検索結果の統計情報を活用してマーケティングや品質評価に利用
💡 COUNT(*)＋MAX(rank)＋MIN(rank)＋AVG(rank) を組み合わせることで、検索結果の関連度分布を分析でき、検索精度の評価やユーザーへの説明に役立ちます。
