Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@LowSE01(ロー)

BigQueryでの年齢算出

Last updated at Posted at 2024-04-08

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?