BigQueryの年齢算出でハマった話(2023年)
はじめに
こんにちは、メタサイトのKHです。
弊社では一部のデータ分析の基盤にBigqueryを使用しており、
先日、データの集計を行っていて発見した事象とその原因及び、解決策、ハマった点等を紹介したいと思います。
原因と解決方法だけ知りたい場合はこちら。
今回関連するシステムの概要
ざっくり上記のようなイメージでCloudSQLからBigqueryへデータ投入を行っています。
今回、関連するのはシンプルにCloudSQLとBigqueryの二点のみです。
事象
CloudSQL側とBigquery側で同一内容であるテーブルはずなのに 年齢ごとに集計をかけるクエリーを実施した所、結果に差分があることに気づきました。
双方のテーブルは以下のようなイメージです。
調査
データ自体の問題説
まずは、大きいレベル感で問題切り分けてきました。
- CloudSQLとBigqueryのテーブルで総レコード数に差異がないか。
- あってる
- テーブルのカラムのデータ型に差がないか
- あってる
- 適当なレコードをピックアップし、双方のテーブルでデータの比較
- あってる
- 差分となっているレコードを、双方のテーブルでデータ比較
- あってる
つまり、データには問題はなさそう。
クエリ側の問題説
次に疑ったのはクエリ側で集計時に行っていた年齢算出に使っていた関数でした。
レコードいくつかピックアップし、年齢算出実施しました
2000/01/01生まれのレコード
23歳が期待結果（2023/1/19日実施）
■CloudSQL（mysql5.6）
select TIMESTAMPDIFF(YEAR, '2000-01-01', CURRENT_DATE()) as age ;
→ 23
■BigQuery
select DATE_DIFF(CURRENT_DATE(), '2000-01-01',YEAR) AS age;
→ 23
あっていそう。
2000/01/30生まれのレコード
22歳が期待結果（2023/1/19日実施）
■CloudSQL（mysql5.6）
select TIMESTAMPDIFF(YEAR, '2000-01-30', CURRENT_DATE()) as age ;
→ 22
■BigQuery
select DATE_DIFF(CURRENT_DATE(), '2000-01-30', YEAR) AS age;
→ 23
HIT！
原因
年齢算出の関数が原因でした。 bigqueryの
DATE_DIFF(CURRENT_DATE(), bitrh_date, YEAR) AS age
では満年齢は算出できず、 年齢で集計した時に差が生まれていました。
Bigqueryのリファレンスにも https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#date_diff
DATE_DIFF(date_expression_a, date_expression_b, date_part)
2 つの DATE オブジェクト（date_expression_a～date_expression_b）間に存在する、指定された date_part の間隔を示す整数を返します。
とあるように、単純な年の引き算の結果を返却しているだけでした。
↓
今回ハマったポイント点として、 Bigqueryで設定していた関数はWebで「Bigquery 年齢」等のキーワードで検索し、上位にヒットした手順を簡単な試験を行った後にセットしていました。 mysqlの関数の考え方も似ているのも引っかかりやすいポイントかなと思いました。
mysql
TIMESTAMPDIFF(YEAR, bitrh_date, CURRENT_DATE())
bigquery(誤)
DATE_DIFF(CURRENT_DATE(), bitrh_date, YEAR) AS age
mysqlは日数の差分から、年数を計算していて bigqueryは年の部分だけの差分を出しているようです。
解決方法
Bigqueryには年齢算出用の関数が現状ないため、手動で計算する必要があります。
結果を先に書くと以下のようなクエリで算出できます。
select CAST(TRUNC((CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) as INT64) - CAST(FORMAT_DATE('%Y%m%d', '2000-01-30') AS INT64)) / 10000) as INT64) as age;
解説
年齢の算出の計算式があり
年齢 ＝ (現在日時（西暦）ー 誕生日（西暦）) / 10000
この結果の小数点以下を切り捨てることで算出できます。
計算式については、こちらの記事が詳しいです。 https://qiita.com/aqril_1132/items/46e824541e657190ef3e
■実際に分解し実行。
・現在日時（西暦）
CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) as INT64)
→ 20230119
・誕生日
CAST(FORMAT_DATE('%Y%m%d','2000-01-30') AS INT64)
→ 20000130
・(現在日時（西暦）ー 誕生日（西暦）) / 10000
(CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) as INT64) - CAST(FORMAT_DATE('%Y%m%d','2000-01-30') AS INT64)) / 10000;
→ 22.9989
・小数点以下切捨て
TRUNC((CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) as INT64) - CAST(FORMAT_DATE('%Y%m%d','2000-01-30') AS INT64)) / 10000);
→22.0
また、bigqueryでINTへCASTを行う場合、[最も近い整数値を返します。]のでお気をつけください。 https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions?hl=ja#cast_as_integer
（ネットを調べていると誤ってCASTしているケースが多く見られました。。）
select CAST(22.9989 as INT64);
→23
各DB毎の年齢算出
メジャー所のRDBでの年齢算出方法も確認しました。
- mysql（5.6及び8.0で確認）
select TIMESTAMPDIFF(YEAR, '2000-01-30', CURRENT_DATE()) as age;
- postgresql（14.4で確認）
select DATE_PART('year', AGE(TIMESTAMP '2000-01-30')) as age;
- bigquery（2023年1月時点）
select CAST(TRUNC((CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) as INT64) - CAST(FORMAT_DATE('%Y%m%d', '2000-01-30') AS INT64)) / 10000) as INT64) as age;
postgresqlにはage関数があるので楽ですね。 Bigqueryだけ非常に手間。。
Googleさんage関数を。。！
さいごに
今回ハマったポイントとして、Web検索の上位にヒットした手順を軽く試験しセットしていました。ネットの知恵は便利ですが鵜呑みにせず、自身で公式のリファレンス読んで、本当に正しいのか？と自分の頭で挙動を理解した上で利用することが大事だなという教訓を得ることができました。
また余談ですが、最近話題のchatGPTにも質問してみました。
↓
...現状はだめなようです。（2023/1/19時点） （聞き方次第では引き出せるのかもしれません）
webの知見を利用する場合は web検索するにせよ、chatGPT使うにせよ、どちらにせよ自身の頭での解釈は重要ということですね。