metabaseでのWHERE変数の組み方メモ

metabaseとは

OSSのWEBベースBIツールです。以下のQiitaが詳しい
https://qiita.com/acro5piano/items/0920550d297651b04387

特に気に入っているのがdockerでの利用が超かんたんな所。
適当なRDBと一緒にyamlを書いてcomposeを立ちあげれば、すぐに分析に使えます。

docker-composeのサンプル

なお、よくあるサンプルではmetabase.dbをvolume指定していないので、コンテナを再作成すると作った分析データが消えます。
Volume指定だいじ。

docker-compose.yml

version: '3'
services:
  metabase:
    image: metabase/metabase
    volumes:
      - ./metabase-data:/metabase-data
    environment:
      MB_DB_FILE: /metabase-data/metabase.db
    ports:
      - "3000:3000"
    links:
      - db
  db:
    image: mysql:5.6.26
    environment:
      MYSQL_ROOT_PASSWORD: ****
      MYSQL_DATABASE: mydb
      MYSQL_USER: user
      MYSQL_PASSWORD: ***
      TZ: 'Asia/Tokyo'
    volumes:
      - ./db:/var/lib/mysql
      - ./mysql_conf:/etc/mysql/conf.d
    ports:
      - 3306:3306
volumes:
  db:
  metabase:

便利なSQLでの定義作成

metabaseはSQLでガシガシ条件を書いて、定義（分析）を公開することができるので、土方社内SEにもおすすめです。
ただし、使い込みの情報があまり国内になく、stackoverflowやgithubのissueからノウハウを拾って作らなければいけないケースが、結構あります。

詳しい使い方は以下の公式が詳しい。
https://www.metabase.com/docs/latest/users-guide/writing-sql.html

この文書では、この中に記載があるもの、無いものの中から使いたいケースがあったものを記載します。

変数

SQLで問い合わせを書いた場合に、変数が使える。
公式のヘルプもあるが、『こういう時どうすればいいんだ？』という事が結構記載がなかったりする。海外のフォーラムなどで解が得られたものをメモしていく。随時追記する。

2020-02-18：取り急ぎなんとかしたかったLIKEとINがわかったので、下書きから公開にします。

参考ページ

基本構文

metabaseの変数は基本的に以下のルールで構成される。

1. {} で囲むと変数になる。
   • 変数は半角英数で命名する。
   • フィルターラベルを指定すると入力枠に名称が表示される。
   • 必須の制御は変数設定メニュー
   • デフォルト値は事前に決めた固定しか登録できない
2. [[]]で囲むと、囲まれた範囲内の変数が入力されたときのみ、その中の構文が評価される。

デフォルト値の設定

参考：https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

metabaseの変数設定エリアにも初期値を指定できるが、今日の日付などといった動的な値は設定できない。
metabaseの説明によれば、値を入力した時だけ、初期値がコメントアウトされるように記入すればよいとのこと。

WHERE createdAt = [[ {{dateOfCreation}} -- ]]CURRENT_DATE()

このSQLで記載し、該当の項目に何も入力しないと以下のクエリとして評価される。

WHERE createdAt = CURRENT_DATE()

何か入力するとこの様になる。

WHERE createdAt = '2020-02-01 00:00:00' -- CURRENT_DATE()

ちょっとHackっぽいが、結果が取れればよい。というスタンスのようだ

LIKE の書き方

LIKEで文字列を検索したい場合は以下のように記載するとよい。
ダブルクォーテーションでくくるとうまいことやっくれるようだ。

name LIKE "%"{{name}}"%"

また、比較対象の文字列に%を含めればよいのであれば、以下のような書き方でもOK
もちろんCONCAT以外でも各RDBの文字結合ができる書き方なら通る

name LIKE CONCAT('%',{{name}},'%')

IN の書き方

v0.25 から 数値タイプ のみIN句がNativeSupportされています。

/* 値タイプを数値にする */
id IN ({{id}})

それ以外の型はまだIssueがCloseされておらず、以下のような形での回避が提示されています。
https://github.com/metabase/metabase/issues/4728

/* mysql */
select count(*) from T
where find_in_set(day, {{day}}) <> 0;

/* postgresql */
select count(*) from T
where day = ANY (string_to_array({{day}},  ','));

ON/OFFのような条件指定

いわゆるチェックボックスで対応したい、クエリ上では文字列で指定するが、エンドユーザーには0/1の指定で切り替えをしたい場合の書き方

[[AND NOT type_column= CASE WHEN {{type_reject}} = 1 THEN 'hoge' ELSE '-1' END]]

このように書くと、type_rejectの入力枠ができるので、ここに1を入力してもらうと、type_column = hoge の結果が除外され、それ以外の値をいれるか、値をセットしないと、hogeの結果が表示されます。
これまたHackぽいですが、結果がでればよいのです。