BigQueryでの年齢算出

動機

BigQueryでの誕生日からの年齢算出は下記の式で求められる、という記事を見かけたが、結果が正しくない
```
date_diff(today, birthday, year)
```

前提

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

事象再現

下記の式では正しく年齢を算出できていないことを確認
```
date_diff(today, birthday, year)
```

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

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

結論

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

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

検証①

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

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

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

検証②

下記の式で閏年にも対応できることを確認する

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

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が与えられた場合の挙動を確認する

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

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
- →算出されない（想定どおり）