SQL Example :Group Selector 2
2020/08/07

グループセレクタの設定例 2


タグ

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

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

-- 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;%';
-- operators

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

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

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

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


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

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

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

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

REGEX 環境の例:

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

REGEXP 環境の例:

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

オマケ

creator カラムは [職種]鈴木一郎 みたいな [~] があるので難しいです。
付属の設定ファイルにある定義方法は正確ではありません。
v0.91000 以前は組み込み処理により、正規表現DLLのインストール状況に応じて分岐し、 使える場合は以下のような正規表現を実行していました。 正確さを求める場合は使用している正規表現DLL向けにクエリを書き換えてください。

REGEX 環境の例:

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

REGEXP 環境の例:

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