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

#概要#
**<font color="LightSeaGreen">
全ての人間は２種類に分けることができる。
　　　　　　　　BigQueryで年齢を出力出来る者と、そうでない者
　　　　　　　　　　　　　　　　　　　　@74kenshiro（1988～2130年）</font>**


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


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

##データ概要
**元データ：とある会社の社員データ「staff_list」**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/340189/89d69b29-1f4f-e7f8-4143-f78800aceaad.png)
**年齢を導き出すbirth_dateはDATE型である。**

##１．年齢を導き出すそもそもの計算式##
![image.png](https://qiita-image-store.s3.amazonaws.com/0/340189/11b8c656-b91b-c511-0013-9c0923c3866e.png)


**例：2019年3月1日時点での1988年3月28日生まれの人の年齢**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/340189/301ff018-e557-9a1f-727c-8b76f27df342.png)


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

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

```SQL
CAST(birth_date AS string) AS birth_date01_str
```
②ハイフンを除外 ※``TRIM``関数では出来ないので``REPLACE``関数でハイフンを無にする。

```sql
REPLACE(CAST(birth_date AS string),'-','') AS birth_date02_str
```
③整数型``INT64``に変換

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

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

```sql
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``に変換して小数点以下切り捨て

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

```sql
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
実際は計算するクエリの最終行だけでいいが、以下のように段階的に処理している！！**
![image.png](https://qiita-image-store.s3.amazonaws.com/0/340189/59878440-793e-0a04-9943-47d5bbb3294b.png)

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

```sql 
  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``なら一発で出来る。
###
```sql
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`
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/340189/fc2cd26f-bd5c-599d-914e-629a6439029c.png)

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

```sql
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`
```
![image.png](https://qiita-image-store.s3.amazonaws.com/0/340189/53847153-cd0c-0d39-dc99-9a7dfed608a5.png)

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

```sql 
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,
```
```sql
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
```

#おわり