【SQL】AGE関数の無いBigQueryで年齢を出力する二つの方法

#概要#

全ての人間は２種類に分けることができる。
　　　　　　　　BigQueryで年齢を出力出来る者と、そうでない者
　　　　　　　　　　　　　　　　　　　　@74kenshiro（1988～2130年）

名もなき初心者エンジニアがBigQueryでただ年齢を出力するだけの記事です。

目次
１．年齢を導き出すそもそもの計算式
２．CASTとREPLACEで変換し、年齢を出力する方法
３．FORMAT_DATE関数でオシャレに変換し、出力する方法
４．まとめ
##この記事で分かること##
BigQueryでのDATE型から年齢を出力する方法

##データ概要
元データ：とある会社の社員データ「staff_list」

年齢を導き出すbirth_dateはDATE型である。

##１．年齢を導き出すそもそもの計算式##

例：2019年3月1日時点での1988年3月28日生まれの人の年齢

##２．CASTとREPLACEで変換し、年齢を出力する方法

①birth_dateをDATE型から文字型STRINGに変換

CAST(birth_date AS string) AS birth_date01_str

②ハイフンを除外 ※TRIM関数では出来ないのでREPLACE関数でハイフンを無にする。

REPLACE(CAST(birth_date AS string),'-','') AS birth_date02_str

③整数型INT64に変換

CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64) AS birth_date03_int64,

④本日の日付けと差し引き、10,000で割る計算（本日の日付けCURRENT_DATEも同じ処理をしておく）

CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) AS current_date_int64,
(CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS age_float,

⑤整数型INT64に変換して小数点以下切り捨て

 CAST((CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS int64) AS age

SELECT
  id,
  birth_date,
  CAST(birth_date AS string) AS birth_date01_str,
  REPLACE(CAST(birth_date AS string),'-','') AS birth_date02_str,
  CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64) AS birth_date03_int64,
  CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) AS current_date_int64,
  (CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS age_float,
  CAST((CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS int64) AS age
FROM
  `Dataset.staff_list`

OUTPUT
実際は計算するクエリの最終行だけでいいが、以下のように段階的に処理している！！

###ゴリ押し感はぬぐえないが無事出力出来た。
DATE型から年齢を出力するクエリ

  CAST((CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS int64) AS age

##３．FORMAT_DATE関数でオシャレに変換し、出力する方法
CASTとREPLACEを使ってした文字型化とハイフン除去が、FORMAT_DATEなら一発で出来る。

SELECT
  id,
  birth_date,
  --２のやり方で、DATE型➡文字型➡ハイフン除去
  REPLACE(CAST(birth_date AS string),'-','') AS birth_date_str_CR,
  --FORMAT_DATEを使ったクエリ
  FORMAT_DATE('%Y%m%d',birth_date)AS birth_date_str_F
FROM
  `Dataset.staff_list`

これに計算を追加するとこうなる。
３行目：２のやり方
４行目：FORMAT_DATEでのやり方

SELECT
  id,
  birth_date,
  CAST((CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS int64) AS age_CR,
  CAST((CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) AS INT64) - CAST(FORMAT_DATE('%Y%m%d', birth_date ) AS INT64)) / 10000 AS int64) AS age_F
FROM
  `Dataset.staff_list`

##４．まとめ
以上が年齢を出力する二つの方法でした。
どっちでやるかは君次第。

CAST((CAST(REPLACE(CAST(current_date AS string),'-','') AS int64) - CAST(REPLACE(CAST(birth_date AS string),'-','') AS int64)) / 10000 AS int64) AS age_CR,

CAST((CAST(FORMAT_DATE('%Y%m%d', CURRENT_DATE()) AS INT64) - CAST(FORMAT_DATE('%Y%m%d', birth_date ) AS INT64)) / 10000 AS int64) AS age_F

#おわり