分析でよく使うクエリ〜満年齢〜 - おいしい健康　開発者ブログ

研究開発チームの濱田です。

おいしい健康では、サービス利用データや臨床研究で取得したデータを、BigQuery に集約しています。複雑な可視化や分析を行う際は Jupyter Notebook を活用しますが、単純集計や簡易的な分析は、SQL で直接叩いてしまうことも多くあります。

ユーザデータを集計する際、年代別にグループ化して平均をとるような処理はよく現れます。おいしい健康ではユーザに誕生日を入力していただいてテーブルに保存しているので、このような処理をする際には、基準日をもとに「満年齢」を算出する必要があるのですが、この算出処理はそこそこ面倒です。

よく見るのが、以下のような処理ですが、閏年などを考慮すると正しく算出できないパターンがあります。

CAST(TRUNC((CAST(FORMAT_DATE ('%Y%m%d', target_date) as INT64) - CAST(FORMAT_DATE ('%Y%m%d', birthdate) as INT64)) / 10000) as INT64)

そこで、満年齢を正しく算出するために以下のようなややこしいクエリを採用しています。

DATE_DIFF(as_of_date,date_of_birth, YEAR) - 
IF(EXTRACT(MONTH FROM date_of_birth)*100 + EXTRACT(DAY FROM date_of_birth) > EXTRACT(MONTH FROM as_of_date)*100 + EXTRACT(DAY FROM as_of_date),1,0)

これを毎度毎度書くのはしんどいので、UDF として保存して利用できるようにしてあります。

-- 一度だけ実行
CREATE FUNCTION dataset.calc_age(date_of_birth INT64, as_of_date INT64)
RETURNS INT64 AS (
  DATE_DIFF(as_of_date,date_of_birth, YEAR) - 
  IF(EXTRACT(MONTH FROM date_of_birth)*100 + EXTRACT(DAY FROM date_of_birth) > EXTRACT(MONTH FROM as_of_date)*100 + EXTRACT(DAY FROM as_of_date),1,0)
);

呼び出すときは以下のようにすればOKです。便利。

SELECT dataset.calc_age(birthdate, CURRENT_DATE("+0900")) FROM users;

他にも頻出処理をまとめたり工夫しているところがあるので、折にふれてご紹介します。