この記事は LITALICO Engineers Advent Calendar 2021 の18日目の記事です。

今回は、探しても意外と？情報量が少ない小ネタを投稿してみようと思います。

背景

現在、BigQueryを使った簡単なダッシュボードの開発をしています。

目的としては、ユーザのサービス利用状況を可視化することで、「新規利用ユーザがサービスの活用方法を理解して」「実際に活用し続けられている」かどうかを把握するためです。前者がいわゆるオンボーディングの分析、後者がヘルススコアの分析にあたります。

そうした分析をしていく中で、ユーザを利用開始からの経過期間で分割して、集団ごとの傾向を見ていくことが必要となってきます。
ただ、BigQueryではそこの計算で少し不便な部分があったため、解決策の一つについてご紹介できればと思います。

対象読者は自分と同じように満年齢を計算したい人と、自分が作ったダッシュボードを保守するかもしれない自所属チームの皆さまです。

（※元々はVue.jsの設計の記事を長々書こうと思っていたのですが、年末思ったよりバタバタしているため予定を変更してお届けしております。）

やりたいこと

一言でいうと「利用開始日から基準日（例えば現在の日付）までの経過年数・経過月数をそれぞれ計算したい」です。
ここでいう「経過」というのは、「経過年数が1年」といったときに、「実際に1年以上2年未満の月日が経っている」という意味になります。満年齢の計算と同じ考え方です。

例：基準日（現在の日付）が 2021年12月15日 の場合

利用開始日が 2021年11月16日 なら経過月数は 0ヶ月 、経過年数は 0年
利用開始日が 2021年11月15日 なら経過月数は 1ヶ月 、経過年数は 0年
利用開始日が 2020年12月16日 なら経過月数は 11ヶ月 、経過年数は 0年
利用開始日が 2020年12月15日 なら経過月数は 12ヶ月 、経過年数は 1年

満年齢の定義を読むとイメージしやすいと思うので置いておきます

満年齢(まんねんれい)とは何？ Weblio辞書

満年齢とは、誕生日が訪れるごとに1歳を加えて数える年齢のことである。満年齢の数え方では、生まれてから次の誕生日が来るまでの間を0歳とし、誕生日を迎えると1歳になる。以降も同じように、誕生日を迎えるごとに1歳を加えていく。満年齢は英語では age と表現する

課題

BigQueryには日時を比較できる DATE_DIFF や TIMESTAMP_DIFF という関数がありますが、この関数を使って年単位・月単位の比較を行った場合、「年部分の差」「月部分の差」だけで結果が計算されてしまいます。

例えば 12月1日 と 11月30日 を月部分で比較すると、月部分の 12 と 11 で差を求めて、結果は 1 となります。これでは上記の「やりたいこと」が実現できません。
（これでも分析はできないことはないですが、計算方法が粗すぎて傾向を見誤る可能性があるので、上記の「やりたいこと」の方法で精緻に計算したいところです。）

方針

簡単に検索したところ次のような記事があったため、同じものをBigQuery上で実装してみます。

考え方としては、次の手順で計算します。
なお利用するデータは全て 利用開始日 <= 基準日 となっているものとします。（まだ利用開始していないものは含まれない）

基準日 と 利用開始日 について、 年 部分の単純な引き算をする
- 2021年1月1日 と 2020年12月31日 でも年部分で比較するので 1 となります
- 差を d 年としておきます
本当に d 年経過しているのかどうかをチェックする
- 利用開始日 + d年 <= 基準日 なら実際に d 年経過している（ 5歳の誕生日 = 生年月日 + 5年 <= 現在の日付 なら満年齢で5歳になっている）
- 利用開始日 + d年 > 基準日 ならまだ d 年に満たない（まだ誕生日がきていない）
比較結果に応じて(1)を補正した値を返却する
- 比較の結果実際に d 年経過しているのなら d 年を計算結果とする
- 比較の結果まだ d 年に満たないのなら (d - 1) 年を計算結果とする

経過月数の場合も単位が年から月に変わるだけで考え方は同じです。

実装

基準日を base_date 、比較するデータ（利用開始日）を value_date として式を書いていきます。

「方針」の(1)については、 DATE_DIFF(base_date, value_date, YEAR) で計算できます。これが d でした。
(2) については、 DATE_DIFF(base_date, value_date + d年, DAY) と、 + d年 部分の DATE_ADD(value_date, INTERVAL d YEAR) で計算できます。比較は第一引数が第二引数より後なら 正 です。
- 足し算部分は DATE_ADD(value_date, INTERVAL DATE_DIFF(base_date, value_date, YEAR) YEAR) となります
- 比較部分も入れると DATE_DIFF(base_date, DATE_ADD(value_date, INTERVAL DATE_DIFF(base_date, value_date, YEAR) YEAR), DAY) です
(3) の引き算する年数は IF(d年経過済み, 0, -1) で切り替えます。IF関数はExcelのと同じで IF(条件, 真の場合の値, 偽の場合の値) の文法です。
- ちょっと長ったらしいですが IF(DATE_DIFF(base_date, DATE_ADD(value_date, INTERVAL DATE_DIFF(base_date, value_date, YEAR) YEAR), DAY) >= 0, 0, -1) です

ということで全部合わせると以下のようになります。

DATE_DIFF(base_date, value_date, YEAR) 
  + IF(
    DATE_DIFF(
      base_date, 
      DATE_ADD(
        value_date, 
        INTERVAL DATE_DIFF(base_date, value_date, YEAR) YEAR
      ), 
    　　　　DAY
    ) >= 0, 
    0, 
    -1
  )

月単位の場合はこうなります。

DATE_DIFF(base_date, value_date, MONTH) 
  + IF(
    DATE_DIFF(
      base_date, 
      DATE_ADD(
        value_date, 
        INTERVAL DATE_DIFF(base_date, value_date, MONTH) MONTH
      ), 
    　　　　DAY
    ) >= 0, 
    0, 
    -1
  )

参考

日付関数 | BigQuery | Google Cloud

動作確認

基準日を 2021-12-15 とすることを想定して、次のような動作確認用のテーブルを用意しました。テーブル名は ad_cale_211217 とします。

ということで、上記のクエリを使って計算してみます。経過月数は elapsed_months 、経過年数は elapsed_years とします。
なお、クエリ中に何回も DATE('2021-12-15') と書くのは嫌なので、UDFで base_date() として呼び出せるように定義しておきます。

標準 SQL ユーザー定義関数 | BigQuery | Google Cloud

書いてみたクエリ

CREATE TEMPORARY FUNCTION
  base_date() AS (DATE('2021-12-15'));
SELECT
  id,
  value_date,
  DATE_DIFF(base_date(), value_date, MONTH) +
IF
  ( DATE_DIFF( base_date(), DATE_ADD( value_date, INTERVAL DATE_DIFF(base_date(), value_date, MONTH) MONTH ), DAY ) >= 0,
    0,
    -1 ) AS elapsed_months,
  DATE_DIFF(base_DATE(), value_date, YEAR) +
IF
  ( DATE_DIFF( base_date(), DATE_ADD( value_date, INTERVAL DATE_DIFF(base_date(), value_date, YEAR) YEAR ), DAY ) >= 0,
    0,
    -1 )AS elapsed_years
FROM
  `プロジェクト名.データセット名.ad_cale_211217`
ORDER BY
  id

実行結果

「やりたいこと」で期待したとおりの挙動が確認できました。

リファクタ

これでも完結といえば完結なのですが、せっかくなので今回考えた計算式を関数として定義して、他の場面でも使いやすいようにしたいと思います。
経過月数の計算は CALC_ELAPSED_MONTHS(base_date, value_date) として、経過年数の計算は CALC_ELAPSED_YEARS(base_date, value_date) として定義します。

CREATE OR REPLACE FUNCTION
  `プロジェクト名.データセット名.CALC_ELAPSED_MONTHS`(base_date DATE,
    value_date DATE) AS (DATE_DIFF(base_date, value_date, MONTH) +
  IF
    (DATE_DIFF(base_date, DATE_ADD(value_date, INTERVAL DATE_DIFF(base_date, value_date, MONTH) MONTH), DAY) >= 0,
      0,
      -1));
CREATE OR REPLACE FUNCTION
  `プロジェクト名.データセット名.CALC_ELAPSED_YEARS`(base_date DATE,
    value_date DATE) AS (DATE_DIFF(base_date, value_date, YEAR) +
  IF
    (DATE_DIFF(base_date, DATE_ADD(value_date, INTERVAL DATE_DIFF(base_date, value_date, YEAR) YEAR), DAY) >= 0,
      0,
      -1));

これにより、先ほどクエリが次のように簡略化できます。

CREATE TEMPORARY FUNCTION
  base_date() AS (DATE('2021-12-15'));
SELECT
  id,
  value_date,
  `プロジェクト名.データセット名.CALC_ELAPSED_MONTHS`(base_date(),
    value_date) AS elapsed_months,
  `プロジェクト名.データセット名.CALC_ELAPSED_YEARS`(base_date(),
    value_date) AS elapsed_years
FROM
  `プロジェクト名.データセット名.ad_cale_211217`
ORDER BY
  id

あとは、 elapsed_years は elapsed_months から計算できるので、経過月数計算の関数が定義されているという前提で、 CALC_ELAPSED_YEARS の定義を CALC_ELAPSED_MONTHS(...) / 12 のように変えてしまっても良いかもしれません。

最後に

ググったときに思ったより情報が出てこないのが不思議なのですが、こういうことをしたいニーズが実は少ないのか、もっと手軽にできる記法があるのか謎です。
いずれにせよ今回の計算を前処理段階で入れたことで、ダッシュボード開発がだいぶ捗りそうなので少し期待しています。

明日は @sanpeita さんによる「clusterで乗り物機能が追加されたからホイールコライダーと真剣に向き合ってみた」です。なんだか引き込まれるタイトルですね。（何の話なのかは正直わかっていないが逆にきになる。。）

BigQueryで経過年数(満年齢)や経過月数を計算する

背景

やりたいこと

課題

方針

実装

動作確認

リファクタ

最後に