BigQueryでの年齢算出

# BigQueryでの年齢算出

## 動機
- BigQueryでの誕生日からの年齢算出は下記の式で求められる、という記事を見かけたが、結果が正しくない

    ```sql
    date_diff(today, birthday, year)
    ```

### 前提
- 誕生日はdate型で保持されているものとする
- 以降の再現、検証の実施日は`2024-04-08`
	- `select current_date('Asia/Tokyo')` => `2024-04-08`

### 事象再現

- 下記の式では正しく年齢を算出できていないことを確認

    ```sql
    date_diff(today, birthday, year)
    ```

- SQL

    ```sql
    with
        test_cases as (
            -- 昨日が誕生日
            select date_add(date_add(current_date('Asia/Tokyo'), interval -1 day), interval -13 year) as birthday
            union all
            -- 今日が誕生日
            select date_add(current_date('Asia/Tokyo'), interval -13 year) as birthday
            union all
            -- 明日が誕生日
            select date_add(date_add(current_date('Asia/Tokyo'), interval 1 day), interval -13 year) as birthday
        ),
        age_ats as (
            select date_add(current_date('Asia/Tokyo'), interval -1 day) as age_at, 'yesterday' as age_at_label
            union all
            select current_date('Asia/Tokyo') as age_at, 'today' as age_at_label
            union all
            select date_add(current_date('Asia/Tokyo'), interval 1 day) as age_at, 'tomorro' as age_at_label
        ),
        calc_ages as (
            select
                test_cases.birthday,
                age_ats.age_at,
                date_diff(age_ats.age_at, test_cases.birthday, year) as age
            from
                test_cases
            cross join
                age_ats
        ),
        final as (
            select * from calc_ages order by birthday, age_at
        )
    select * from final
    ```

- 結果

    | birthday   | age_at     | age |
    | ---------- | ---------- | --- |
    | 2011-04-07 | 2024-04-07 | 13  |
    | 2011-04-07 | 2024-04-08 | 13  |
    | 2011-04-07 | 2024-04-09 | 13  |
    | 2011-04-08 | 2024-04-07 | 13  |
    | 2011-04-08 | 2024-04-08 | 13  |
    | 2011-04-08 | 2024-04-09 | 13  |
    | 2011-04-09 | 2024-04-07 | 13  |
    | 2011-04-09 | 2024-04-08 | 13  |
    | 2011-04-09 | 2024-04-09 | 13  |

    - →その年の誕生日を迎えていなくても年齢が加算されてしまっている

## 結論

- 下記の式で年齢を算出することができる

    ```sql
    date_diff(today, birthday, year)
    -
    cast(date_add(birthday, interval date_diff(today,birthday,year) year) > today as integer)
    ```


### 検証①

- 下記の式で正しく年齢を算出できることを確認する

    ```sql
    date_diff(today, birthday, year)
    -
    cast(date_add(birthday, interval date_diff(today,birthday,year) year) > today as integer)
    ```

- SQL

    ```sql
    with
        test_cases as (
            -- 昨日が誕生日
            select date_add(date_add(current_date('Asia/Tokyo'), interval -1 day), interval -13 year) as birthday
            union all
            -- 今日が誕生日
            select date_add(current_date('Asia/Tokyo'), interval -13 year) as birthday
            union all
            -- 明日が誕生日
            select date_add(date_add(current_date('Asia/Tokyo'), interval 1 day), interval -13 year) as birthday
        ),
        age_ats as (
            -- 昨日
            select date_add(current_date('Asia/Tokyo'), interval -1 day) as age_at
            union all
            -- 今日
            select current_date('Asia/Tokyo') as age_at
            union all
            -- 明日
            select date_add(current_date('Asia/Tokyo'), interval 1 day) as age_at
        ),
        calc_ages as (
            select
                test_cases.birthday,
                age_ats.age_at,
                date_diff(age_ats.age_at, test_cases.birthday, year)
                -
                cast(date_add(test_cases.birthday,interval date_diff(age_ats.age_at, test_cases.birthday, year) year) > age_ats.age_at as integer) as age,
            from
                test_cases
            cross join
                age_ats
        ),
        final as (
            select * from calc_ages order by birthday, age_at
        )
    select * from final
    ```

- 結果

    | birthday   | age_at     | age |
    | ---------- | ---------- | --- |
    | 2011-04-07 | 2024-04-07 | 13  |
    | 2011-04-07 | 2024-04-08 | 13  |
    | 2011-04-07 | 2024-04-09 | 13  |
    | 2011-04-08 | 2024-04-07 | 12  |
    | 2011-04-08 | 2024-04-08 | 13  |
    | 2011-04-08 | 2024-04-09 | 13  |
    | 2011-04-09 | 2024-04-07 | 12  |
    | 2011-04-09 | 2024-04-08 | 12  |
    | 2011-04-09 | 2024-04-09 | 13  |

    - →OK







### 検証②
- 下記の式で閏年にも対応できることを確認する

    ```sql
    date_diff(today, birthday, year)
    -
    cast(date_add(birthday, interval date_diff(today,birthday,year) year) > today as integer)
    ```

- SQL

    ```sql
    with
        test_cases as (
            select parse_date('%Y-%m-%d', '2000-02-28') as birthday
            union all
            select parse_date('%Y-%m-%d', '2000-02-29') as birthday
            union all
            select parse_date('%Y-%m-%d', '2000-03-01') as birthday
        ),
        age_ats as (
            select parse_date('%Y-%m-%d', '2024-02-27') as age_at
            union all
            select parse_date('%Y-%m-%d', '2024-02-28') as age_at
            union all
            select parse_date('%Y-%m-%d', '2024-02-29') as age_at
            union all
            select parse_date('%Y-%m-%d', '2024-03-01') as age_at
            union all
            select parse_date('%Y-%m-%d', '2024-03-02') as age_at
        ),
        calc_ages as (
            select
                test_cases.birthday,
                age_ats.age_at,
                date_diff(age_ats.age_at, test_cases.birthday, year)
                -
                cast(date_add(test_cases.birthday,interval date_diff(age_ats.age_at, test_cases.birthday, year) year) > age_ats.age_at as integer) as age,
            from
                test_cases
            cross join
                age_ats
        ),
        final as (
            select * from calc_ages order by birthday, age_at
        )
    select * from final
    ```

- 結果

    | birthday   | age_at     | age |
    | ---------- | ---------- | --- |
    | 2000-02-28 | 2024-02-27 | 23  |
    | 2000-02-28 | 2024-02-28 | 24  |
    | 2000-02-28 | 2024-02-29 | 24  |
    | 2000-02-28 | 2024-03-01 | 24  |
    | 2000-02-28 | 2024-03-02 | 24  |
    | 2000-02-29 | 2024-02-27 | 23  |
    | 2000-02-29 | 2024-02-28 | 23  |
    | 2000-02-29 | 2024-02-29 | 24  |
    | 2000-02-29 | 2024-03-01 | 24  |
    | 2000-02-29 | 2024-03-02 | 24  |
    | 2000-03-01 | 2024-02-27 | 23  |
    | 2000-03-01 | 2024-02-28 | 23  |
    | 2000-03-01 | 2024-02-29 | 23  |
    | 2000-03-01 | 2024-03-01 | 24  |
    | 2000-03-01 | 2024-03-02 | 24  |

    - → OK


### 検証③
- 下記の式で生年月日にnullが与えられた場合の挙動を確認する

    ```sql
    date_diff(today, birthday, year)
    -
    cast(date_add(birthday, interval date_diff(today,birthday,year) year) > today as integer)
    ```

- SQL

    ```sql
    with
        test_cases as (
            select date(null) as birthday
        ),
        age_ats as (
            select current_date('Asia/Tokyo') as age_at
        ),
        calc_ages as (
            select
                test_cases.birthday,
                age_ats.age_at,
                date_diff(age_ats.age_at, test_cases.birthday, year)
                -
                cast(date_add(test_cases.birthday,interval date_diff(age_ats.age_at, test_cases.birthday, year) year) > age_ats.age_at as integer) as age,
            from
                test_cases
            cross join
                age_ats
        ),
        final as (
            select * from calc_ages order by birthday, age_at
        )
    select * from final
    ```

- 結果

    | birthday | age_at     | age |
    | -------- | ---------- | --- |
    |          | 2024-04-08 |     |

    - →算出されない（想定どおり）