LookerのLookML上で日付データ間の期間を計算・出力する

大阪オフィスの玉井です。

データ分析をしている時、日付型のカラムが2つ以上あって、それらを引き算して、2つの日付間の時間（期間）を算出したい時があります。

LookerのLookMLでは、上記のような計算も簡単に行うことができます。

「duration」というタイプを指定する

下記のような日付型のデータがあるとします。

orderが注文日、shipが出荷日と考えてください。つまり、この2つの日付データを使用すると、「注文してから出荷されるまでの期間」を取得することができます。それでは、実際にやってみましょう。

dimension_groupというパラメータを記述します。

dimension_group: ship_duration {
  type: duration
  intervals: [
    hour,
    day,
    week
  ]
  sql_start: ${order_raw} ;;
  sql_end: ${ship_raw} ;;
}

type

2つの日付間の期間を出したいのでdurationを指定します。

intervals

Exploreで選択できる期間の種類を指定します。上記だと「○○時間」「○○日」「○○週」の3つが選べるようになります。

sql_start

期間の計算を開始する日付データを指定します（日付の古い方）。SQLパラメータなので、他のdimension等と同じようにカラムを指定すれば問題ありません。今回の例でいうと、orderはtimeframesとしてrawがあるので、それを指定しています。

ちなみに、ここはあくまでSQLなので、接続しているDBの関数を使うこともできます。例えばAmazon Redshiftだったら、getdate()と記述することで、クエリ実行時（ダッシュボード閲覧時など）の時間を使うこともできます。

sql_end

期間の計算先の日付データを指定します（日付の新しい方）。記述できる内容はsql_startと同じ。

Exploreで見てみる

Exploreでは、「Duration～」という名称でフィールドが表示されます。下記はDaysを選んでデータを表示したものです（わかりやすいように計算元の日付も使用しています）。

ちゃんと計算されていますね。

計算後の値をLookML上で再利用する

計算後の値をExploreで使用してみましたが、場合によっては、さらにこの期間データを使って、新しいフィールドを作成したい時もあると思います。それも置換構文で可能です。期間を計算しているdimension_groupの頭にintervalsで指定した時間種別のいずれかを付与することで、値を引用することが可能です。

下記は「注文から出荷まで4日以上かかっていたらYES、そうじゃなかったらNO」というフィールドです。

dimension: duration_7days {
  type: yesno
  sql: ${days_ship_duration} > 3 ;;
}

上記のSQL部分で日単位の期間を再利用しています。これを利用すれば出荷までに日数を要している注文がどれくらいあるのかをひと目でわかるLookを作ることもできます。

おわりに

シンプルな計算は、LookML上で大体済ませることができます。

参考資料