この記事は「AlphaDrive Advent Calendar 2023」の8日目のエントリーです。

弊社では metabase を利用してます。
BI ツール的にダッシュボードでの可視化での用途や、ビジネスサイドの方たちが運用上必要な SQL クエリを実行して結果を取得するための用途などに使っています。

今回は主にその後者のシーン向けに、使いやすいSQLクエリ実行環境を提供するめの小ネタをつらつら紹介します！

metabase は v0.47.2 で、データソースは Redshift でバージョンは v1.0.60854 の話になります

日付指定のデフォルト値を動的にしたい

特定期間の集計を行いたいクエリの場合、期間日付を変数化して任意の日付を指定できるようにしつつ、デフォルトでは現在時刻を基準に直近のものを取得したいケースはよくあると思います。
ただしmetabaseの日付変数のデフォルト値に設定できるのは固定の日付なので、クエリ上でどうにかする必要があります。

公式ドキュメントには下記のような書き方が紹介されてますね
https://www.metabase.com/docs/latest/questions/native-editor/sql-parameters#setting-complex-default-values-in-the-query

SELECT [[ {{date}} #]]GETDATE()

この日付をタイムゾーン変換関数噛ませたりとかすると改行とか縛れられてごちゃごちゃするので
以下のような謎シンタックスを駆使するとよいと思ってます。カンマの位置がミソです。

SELECT coalesce([[{{date}}::timestamp,]] GETDATE());

-- `{{date}}`に設定されていれば、発行されるSQLは下記
SELECT coalesce('2023-12-01'::timestamp, GETDATE());
-- `{{date}}`に設定されていなければ、実際に発行されるSQLは下記
SELECT coalesce(GETDATE());

そうすると、例えば、デフォルトでは直近の 1 週間、date 変数を指定すれば任意の地点から 1 週間という WHERE 句は以下のように書けます

WHERE time < coalesce([[{{date}}::timestamp,]] GETDATE())
  AND time > DATEADD(week, -1, coalesce([[{{date}}::timestamp,]] GETDATE()))

また、少し複雑なクエリの場合で、変数日付が複数回出現して更にはTZ変換とかが必要な場合、幾度も↑の小技を使うと可読性が落ちるので、最初に中間テーブル上で変換しちゃって以降取り回したりするのも良いと思います。

WITH dates AS (
   SELECT CONVERT_TIMEZONE('JST', 'UTC', coalesce([[{{date}}::timestamp,]] GETDATE()))                AS target_from_utc,
          CONVERT_TIMEZONE('JST', 'UTC', ADD_MONTHS(coalesce([[{{date}}::timestamp,]] GETDATE()), 1)) AS target_to_utc
),
SELECT * FROM records
WHERE time < (SELECT utc_from FROM dates)
  AND time >= (SELECT utc_to FROM dates);

絞り込み条件に使う変数で複数の値を取り扱いたい

例えば、任意の複数のユーザ ID で絞り込みを書けられるようにしたい場合、変数idsを文字列として定義して、ANY 句で変数を利用することができます。

SELECT * FROM WHERE id = ANY (string_to_array({{ids}},  ','));

※ IN 句だと引数に配列を受け付けられないのでANYの書き方じゃないと駄目

フィールドフィルターを利用する

これはデフォルト機能ですが, SQL クエリを簡素にしつつ、ユーザビリティも上がるので便利ですね

名前で絞り込めるようにしたいけどIDしか参照できない時

弊社metabase はビジネスサイドの方含めて社内に広く使われているものなので、個人情報等の秘匿データを参照できないようになっています
例えば社内の特定の人物でユーザーを絞り込みたいケースでは、本来はフィールドフィルターを使いたいところですが、それができません
そのままだと、ID を指定しないといけなかったりしますが、名前と ID の紐づけが分からず困ってしまいます

そんな時且つ絞り込みたい対象の数が数個しかない時に限っては、一回中間テーブルで名前->ID の変換を行い、名前を変数化してドロップダウン形式で選べるようにしておくと、社員の名前を選択して絞り込めるようになったりします
(利用シーンは稀そうではありますね！)

WITH name_to_id AS (
  SELECT
  CASE
    WHEN 'NP太郎' = {{name}} THEN 1
    WHEN 'NP花子' = {{name}} THEN 2
    ELSE -1
  END AS id
)
SELECT * FROM users WHERE id = (SELECT id FROM name_to_id);

ダッシュボードを使う

ダッシュボードの方が表現力が高いので一つの SQL クエリでもダッシュボード化した方が良いと思ってます

ダッシュボードで変数を扱う

複数のSQLクエリの変数に一括で値を渡したりデフォルト値を設定することができます

ダッシュボードで注釈とか書く

テキストブロックを配置できたりします
そのクエリの背景やカラムの補足説明などを記載しておいたりすると便利かなと思ってます

ダッシュボードで注視したいレコードにはハイライトする

あんまり書くことなかった！
標準の機能はフル活用していきたいですね！
SQL上の小技に関しては、やりすぎるとメンテナンスが難しくなったり、SQLを metabase 以外のクライアントからそのままコピペして実行できなくなってしまったりするので、用法用量守って適材適所で使っていきたいですね！