Metabase + BigQuery で、日付型の変数のデフォルトを相対的にする

最近Metabaseを使い始めました。BigQueryに入っているデータをもとに、ダッシュボードを作りたい。

やりたいこと

例えば「年月」「事業分類」「売上高」のカラムを持つデータセットがあるとして、これをMetabaseでグラフにしたい。(「年月」と名前が付いてますが実際は「日付」が入ってる)

このときの期間指定のデフォルトを「前月」にしたい。例えば今日が 2023-11-10 である場合 2023-10-01 から 2023-10-31 の範囲のデータを表示させたい。

「まさにこれだろ！」と思った記事が下記。

なのですが、こちらの記事は元データをPostgreSQLに入っているようで、BigQueryとは記法が異なっているようでうまくいきませんでした。。。

やったこと

上記の記事を参考にしながら、ChatGPTに教えてもらいながら進めました。ChatGPTすげぇ。これ無しでは仕事したくない。

結論

テーブル名は適宜読み替えてください。補足として、カラム名が scores.年月 になってますが、ここには YYYY-MM-DD の日付データが入ってます。

SELECT
  `scores`.`事業分類`,
  FORMAT_DATE('%Y-%m', `scores`.`年月`) as `年月`,
  SUM(`scores`.`売上`)
FROM
  `scores`
WHERE
  `年月` BETWEEN 
    [[ CAST({{from_date}} AS DATE) --]] DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)
  AND
    [[ CAST({{to_date}} AS DATE) --]] LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH))
GROUP BY `事業分類`, FORMAT_DATE('%Y-%m', `scores`.`年月`)

下記のようにMetabaseの画面で「変数」の設定をして実行しました。(値タイプを「日付」に)
本日が 2023-11-10 なので、2023年10月分だけが集計されています。

コード解説

こうなってくると通常のSQLでは機能しないです。特別な記法が必要なのですが、標準のSQLでいいのか、BigQueryの記法なのか、Metabaseの記法なのか、もはや良くわからないので、ChatGPTに聞きます。

ChatGPTに教えてもらった

WHERE 句の中で使用されている構文は、Metabaseで動的な日付フィルタリングを実現するためのものです。具体的な動作は以下の通りです。

WHERE 句の解説

• 年月 BETWEEN ... AND ...:
  この部分は 年月 カラムの値が指定された2つの日付の間にある行をフィルタリングするために使用されます。

• [[ CAST({{from_date}} AS DATE) --]]:
  ここではMetabaseの変数機能を利用しています。{{from_date}} はユーザーがフィルタとして指定する日付です。

  • CAST({{from_date}} AS DATE): ユーザーが入力した値を日付型に変換します。この変数が入力された場合、その値が WHERE 句の開始日として使用されます。
  • --: このダッシュは、Metabaseの変数が提供されていないときに、その後ろのコードがコメントとして扱われることを意味します。つまり、変数 {{from_date}} が空の場合、この部分は無視されます。

• DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH):
  これは {{from_date}} が提供されていない場合に使用されるデフォルトの開始日です。

  • CURRENT_DATE(): 現在の日付を取得します。
  • DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH): 現在の日付から1ヶ月前の日付を計算します。
  • DATE_TRUNC(..., MONTH): その月の最初の日（月初）に日付を切り捨てます。つまり、1ヶ月前の月の最初の日になります。

• [[ CAST({{to_date}} AS DATE) --]]:
  これもMetabaseの変数を利用していますが、今回は終了日に関するものです。

  • もし {{to_date}} がユーザーによって提供されれば、その値を終了日として使用します。

• LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)):
  これは {{to_date}} が提供されていない場合に使用されるデフォルトの終了日です。

  • LAST_DAY(...) 関数は、指定された日付の月の最後の日を返します。つまり、この場合は1ヶ月前の月の最後の日になります。

結果として、この WHERE 句は以下のように機能します：

• もしユーザーが {{from_date}} と {{to_date}} の両方を提供すれば、その範囲がフィルタとして使用されます。
• もしユーザーがどちらかの日付のみを提供すれば、提供された日付とデフォルトの日付の組み合わせがフィルタとして使用されます。
• もしユーザーがどちらの日付も提供しなければ、デフォルトの期間（前月の最初の日から最後の日まで）がフィルタとして使用されます。

次の課題

ひとまずこの「質問」(Metabaseでは一つのクエリ？を「質問」と呼ぶ) についてのデフォルト期間は設定できたのですが、一つのダッシュボードに複数のグラフ(質問をビジュアライズしたもの)があるときに、全てのグラフについてこのWHERE句を書く必要がある、、、のかな。

Metabaseに「SQLスニペット」という機能があるのですがフィルタまではスニペット化できないみたいなんですよね。。。

どなたか知見ございましたら教えてほしいです！