months_between沼

create or replace function [データセット名].months_between(
  to_date date,
  from_date date
) returns float64
as (
  (
    -- 参考 https://dev.to/bornfightcompany/implementing-monthsbetween-function-in-bigquery-2892
    with from_and_to_date as (
      -- 引数を縦結合
      select from_date as calculation_date union all
      select to_date as calculation_date
    ),
    year_month_day_nums as (
      -- 年月日を分離して取得
      select
        calculation_date as calculation_date,
        cast(format_datetime("%Y", datetime(calculation_date)) as int64) as year_num,
        cast(format_datetime("%m", datetime(calculation_date)) as int64) as month_num,
        cast(format_datetime("%d", datetime(calculation_date)) as int64) as day_num
      from from_and_to_date
    ),
    days_and_end_of_month as (
      -- 月内の日数・月初からの日数・月末への日数を取得
      select
        *,
        date_diff(last_day(calculation_date), date_trunc(calculation_date, month), day) + 1 as days_in_month,
        date_diff(last_day(calculation_date), calculation_date, day) as distance_to_eom
      from year_month_day_nums
    ),
    left_ratio as (
      -- 月末への日数を割合化
      select
        calculation_date,
        year_num,
        month_num,
        day_num,
        days_in_month,
        round(distance_to_eom/days_in_month, 4) as month_left_ratio
      from days_and_end_of_month
    ),
    from_and_to_date_processed as (
      -- 日付の前処理が終わったので、fromとtoを取得（maxしているのはpivotのため=レコードを1行にするため）
      select
        max(case when calculation_date = from_date then calculation_date end) as calc_date_from,
        max(case when calculation_date = from_date then year_num end) as year_num_from,
        max(case when calculation_date = from_date then month_num end) as month_num_from,
        max(case when calculation_date = from_date then day_num end) as day_num_from,
        max(case when calculation_date = from_date then days_in_month end) as days_in_month_from,
        max(case when calculation_date = from_date then month_left_ratio end) as month_left_ratio_from,
        max(case when calculation_date = to_date then calculation_date end) as calc_date_to,
        max(case when calculation_date = to_date then year_num end) as year_num_to,
        max(case when calculation_date = to_date then month_num end) as month_num_to,
        max(case when calculation_date = to_date then day_num end) as day_num_to,
        max(case when calculation_date = to_date then days_in_month end) as days_in_month_to,
        max(case when calculation_date = to_date then month_left_ratio end) as month_left_ratio_to
      from left_ratio
    )
    select
      case
        when calc_date_from is null or calc_date_to is null
          then null
        when calc_date_from = calc_date_to -- 同じ日の場合は0
          then 0
        when calc_date_from < calc_date_to -- fromのほうがtoよりも前の日付の場合（通常）
          then 
            case 
              when day_num_to = day_num_from -- 日付が一致している場合は年・月から計算し整数で返す
                then (year_num_to-year_num_from)*12 + month_num_to - month_num_from
              when day_num_to < day_num_from and month_left_ratio_to = 0 -- toの日付がfromより前で、かつ、toが月末の場合（例: from=2022-12-30, to=2023-02-28 → 2ヶ月）も、年・月のみから計算し整数で返す
                then (year_num_to-year_num_from)*12 + month_num_to - month_num_from
              when day_num_to > day_num_from and month_left_ratio_from = 0 -- toの日付がfromより後で、かつ、fromが月末の場合（例: from=2022-02-28, to=2023-05-30 → 3ヶ月）も、年・月のみから計算し整数で返す
                then (year_num_to-year_num_from)*12 + month_num_to - month_num_from
              else 
                -- それ以外の場合は、「fromの月の残り割合 + (fromとtoの月数の差 - 1) + toの日付/fromの月内の日数」
                -- 例1: from=2022-02-14, to=2023-03-15 → 14/28 + (1 - 1) + 15/28 → 1.0357...
                -- 例2: from=2023-01-01, to=2023-02-02 → 30/31 + (1 - 1) +  2/31 → 1.0322...
                -- 例3: from=2022-12-27, to=2023-02-28 →  4/31 + (2 - 1) + 28/31 → 2.0322...
                -- 例4: from=2023-04-30, to=2023-06-01 →  0/30 + (2 - 1) +  1/30 → 1.0333...
                month_left_ratio_from + (date_diff(calc_date_to, calc_date_from, month) - 1) + (day_num_to/days_in_month_from)
            end
        when calc_date_from > calc_date_to -- fromのほうがtoよりも後の日付の場合（逆転している場合）は、fromとtoを逆転させて計算した上で、負の値にする
          then 
            case
              when day_num_from = day_num_to
                then -1*( (year_num_from-year_num_to)*12 - month_num_from - month_num_to )
              when day_num_from < day_num_to and month_left_ratio_from = 0
                then -1*( (year_num_from-year_num_to)*12 - month_num_from - month_num_to )
              when day_num_from > day_num_to and month_left_ratio_to = 0
                then -1*( (year_num_from-year_num_to)*12 - month_num_from - month_num_to )
              else -1*( month_left_ratio_to + (date_diff(calc_date_from, calc_date_to, month) - 1) + (day_num_from/days_in_month_to) )
            end
      end as result
    from from_and_to_date_processed
  )
)

select months_between('2023-02-28', '2022-12-28');

select months_between('2023-02-28', '2022-12-29');

select months_between('2023-02-28', '2022-12-30');

select months_between('2023-02-28', '2022-12-31');

SELECT
    MONTHS_BETWEEN('2019-03-28'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween1,
    MONTHS_BETWEEN('2019-03-30'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween2,
    MONTHS_BETWEEN('2019-03-31'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween3
    ;
+----------------+----------------+----------------+
| MONTHSBETWEEN1 | MONTHSBETWEEN2 | MONTHSBETWEEN3 |
|----------------+----------------+----------------|
|       1.000000 |       1.064516 |       1.000000 |
+----------------+----------------+----------------+