PostgreSQLで文字列のヒットスコアを返す関数をPL/pgSQLで作る

こんなとき

フリーワードで検索できるシステムを開発していると、文字列のヒット数の高いものから表示したいという要望が出る場合があります。 そんな時に便利そうなSCORE()関数を作ってみました。PostgereSQL標準のPL/pgSQLで作りましたので、恐らく配列をサポートしているPostgreSQLバージョンであれば機能すると思います。

こんな感じで使えます

まずは使い方から。

例えば書籍テーブルがあったとして、入力したフリーワードが多くマッチしたものから表示したい場合、こんな風に書けます。

SELECT * FROM books WHERE body LIKE '%猫%' ORDER BY SCORE(body, '猫') DESC;

引数はこんな感じ。

SCORE(検索対象となるカラム, 入力されたワード)

SCORE()関数だけピックアップして、いくつか実行サンプルを見てみましょう。

SELECT SCORE('ABC123_ABC', 'ABC'); -- → 2を返す
SELECT SCORE('ABC123_abc123_ab', 'ab'); -- → 3を返す(大文字小文字は区別されない)
SELECT SCORE('いぬ ねこ こねこ ひつじ', 'ねこ いぬ'); -- → 3を返す(複数のワードもカウントする)

アルファベットの大文字小文字は判別しません。(全角半角は区別します)
さらに、検索するワードは空白が入ったまま渡してOKです。それぞれのワードのヒット数を合計したものが返ります。 ワードの区切りには1つ以上の半角または全角の空白が使用できます。

実際の関数定義

psqlコマンドライン上などで、以下のSQLをそのままコピペして実行すればSCORE()関数が出来上がります。

CREATE OR REPLACE FUNCTION SCORE(target TEXT, keyword_string TEXT) RETURNS INTEGER AS $$
DECLARE
  words text[] := (SELECT STRING_TO_ARRAY(REGEXP_REPLACE(LOWER(keyword_string), '[  ]+', ' ', 'g'), ' '));
  word text;
  score INTEGER := 0;
BEGIN
  FOREACH word IN ARRAY words
  LOOP
    score = score + (ARRAY_LENGTH(STRING_TO_ARRAY(LOWER(target), word), 1)-1);
  END LOOP;
  RETURN score;
END;
$$ LANGUAGE plpgsql;

※定義中に全角スペースが一部含まれますので文字コードにはご注意を。

関数内部の動き

実際にこの関数が何をやっているかというと、

  1. 入力されたワードを空白で切り分けて配列を作成→[A]
  2. [A]のひとつのワードを区切り文字として、検索対象のデータを切り分けて配列を作成→[B]
  3. [B]の配列個数から1を引けばほら、そのワードのヒット数と同義なので、総計に加算します。
  4. これを残りの[A]のワードも全て実行すれば、全ての入力ワードのヒットスコア数総計になります。

簡単なワード検索などで活躍しそうですね!
PostgreSQLでワード検索付きのシステムを開発することがあれば是非お試しください。

・・え? MySQLしか使ったことがない?
PostgreSQLも素敵なデータベースサーバですので、是非お試しあれ!