BigQueryやAthenaでの日付指定

解決したいこと

定期的に集計したいクエリを実行する際、集計対象日となる日付を下記などに渡して利用することが多い

そのときに下記のような問題がある

• クエリ内で一箇所で日付関連の定義を管理して、適切な名前をつけクエリを読みやすくしたい
  • クエリを呼び出す側の修正ではなく、クエリそのものの修正で完結させたい
• そのため集計対象日だけを渡して、他の日付はその日付を計算して算出したい
• 集計対象日として置換する前の文字列があちこちに書かれないようにしたい

集計対象日以外を考慮する集計

下記のように日付を考慮する集計が存在する

• 集計対象日を含む過去30日の集計
• 前月登録会員の30日以内行動の集計

クエリ例

• udf (ユーザー定義関数) による設定の一元管理と関数名前付けによるわかりやすさを意識する
• udfを使いにくい環境では、with句での一元管理とカラム名前付けによるわかりやすさを意識する

bigquery

udfを気軽に使えるため

create temporary function target_date()
returns date as (
  -- date(%(target_date)s)
  date('2021-10-18')
)
-- target_date() を用いて 30日計算した日付を返す関数を作成する
;
with t as (
  -- values を使えないので union allで代用
  select date('2021-10-18') as d, 1 as x union all
  select date('2021-10-19'), 2
)
select
  d
  , x
  , target_date() as target_date
from t
where d = target_date()

athena

udfを使うためにJavaのコードを書くのはつらいので、with句でconfiguration用のテーブルを用意して使いたい日付をscalarとして利用する

with t as (
  select *
  from (
    values (date('2021-10-18'), 1), (date('2021-10-19'), 2)
  ) as t(d, x)
)
, config as (
  -- select date(%(target_date)s) as target date
  select date('2021-10-18') as target date
)
-- config tableを利用してさらにtableを作るか、config内で date_add等を使って算出する
select
  d
  , x
  , target_date() as target_date
from t
where d = (select target_date from config)