Tips : 再帰クエリ例

グループセレクタの設定例:再帰クエリ #

タグ #

作者は長らくタグの存在を忘れていました(汗
これで少しは tag カラムが日の目を見られる…かもしれません。

タグは tag カラムに ; 区切りで格納されているため、再帰を行い文字列を分割します。

以下が設定例となります。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- tabs
タグ
-- tabs

-- queries
WITH RECURSIVE split(fld, remain) AS (
  SELECT '', tag||';' FROM list_view
  UNION ALL
  SELECT
    substr(remain, 0, instr(remain, ';')) AS fld,
    substr(remain, instr(remain, ';')+1) AS remain
  FROM split WHERE remain != ''
  ) SELECT DISTINCT fld FROM split ORDER BY fld;
-- queries

-- operators
SELECT * FROM list_view WHERE ';' || tag || ';' LIKE '%;@click-string;%';

グループセレクタ抽出クエリ部の説明 #

 6
 7
 8
 9
10
11
12
13
WITH RECURSIVE split(fld, remain) AS (
  SELECT '', tag||';' FROM list_view
  UNION ALL
  SELECT
    substr(remain, 0, instr(remain, ';')) AS fld,
    substr(remain, instr(remain, ';')+1) AS remain
  FROM split WHERE remain != ''
  ) SELECT DISTINCT fld FROM split ORDER BY fld;

詳細を説明できるほど精通していませんが、良く例題として示される再帰SQLです。
(Qiita や Stack Overflow で紹介された例を混ぜて簡素化したものです)

  1. 7行目の結果が splitテーブル抽出テーブル(最終結果用の内部テーブル) へ格納される
    fld には 空文字, remain には tag 末尾に ; を付加したものが入る
  2. 9~12行目の結果が一時テーブルに格納される
    → 1. の remain に入れた文字列を先頭から ; の位置まで切り出して fld に格納
    → 切り出した分 remain を詰める
  3. 一時テーブル抽出テーブルへ結合される
  4. splitテーブル一時テーブルに置き換えらる
  5. 一時テーブルが空になるまで 2.~ 繰り返される

要するに ; 区切りのキーワードを分割し、分割したキーワードが1行毎に抽出されます。
DISTINCT しているので、重複する文字列は纏められます。

ちなみに付属の設定ファイルにある creator 定義も同じ再帰SQLです。
違いは、creator の定義では文字列先頭から [~] を除去しながら再帰しています。

アイテムリスト絞り込みクエリ部の説明 #

17
SELECT * FROM list_view WHERE ';' || tag || ';' LIKE '%;@click-string;%';

正確な絞り込みには正規表現が必要と思いましたが、
これも Stack Overflow に “ハッキーな方法” として紹介されていました。
比較元も比較先も ; で囲う方法です。
単純なセミコロン区切りである tag カラムはこれでイケると思います。

“正確な” とは、例えば あんぱん小倉あんぱん; を区別するケースです。
ちなみに正規表現を用いると以下のような感じになると思います。

REGEX 環境の例:

17
SELECT * FROM list_view WHERE REGEX(tag, '^(.*;)*@click-string(;.*)*$');

REGEXP 環境の例:

17
SELECT * FROM list_view WHERE tag REGEXP '^(.*;)*@click-string(;.*)*$';

オマケ #

creator カラムも ; 区切りですが、[音楽]すぎやまこういち のように [~] が含まれているため、正確に分割してクリエイター名だけ切り出すのは困難です。

付属の設定ファイルにある定義方法は正確ではありません。
v0.91000 以前は組み込み処理により、正規表現DLLのインストール状況に応じて、regex が使える場合は以下のような正規表現を実行していました。正確さを求める場合は、自身が使用している正規表現DLL向けにクエリを書き換えてください。

REGEX 環境の例:

17
18
SELECT * FROM list_view WHERE
 NOT REGEX(creator, '\][^;].+;') AND creator LIKE '%]%') OR creator ISNULL;

REGEXP 環境の例:

17
18
SELECT * FROM list_view WHERE
 (creator NOT REGEXP '.*\][^;].+;' AND creator LIKE '%]%') OR creator ISNULL;
marm.nakamura