結論

months_between は…

• Snowflake・Redshiftにはあり、BigQueryにはない

• RedshiftとSnowflakeの months_between は、直観に反する動作をすることがあるので要注意

• 自前で作ることも可能だが、仕様を細かく決める必要がある

Snowflake

Snowflakeには months_between が実装されています。（公式doc）

ただし、公式ドキュメントに記載の通り、非直観的な動作になる場合があります。

月の日が同じ場合（例：2月28日と3月28日）と、月の日が最後の場合（例：2月28日と3月31日）の 両方 で、関数が月を整数で返すという事実は特に、ペア内で最初の日付を増やしても、必ずしも出力値が増えるわけではないという、非直観的な動作につながります。（公式doc）

やってみました。

select months_between('2023-02-28'::date, '2022-12-28'::date);
select months_between('2023-02-28'::date, '2022-12-29'::date);
select months_between('2023-02-28'::date, '2022-12-30'::date);
select months_between('2023-02-28'::date, '2022-12-31'::date);

上から、

• 2（≒2022-12-28〜2023-02-28の間は2ヶ月という計算）

• 1.967742（≒2022-12-29〜2023-02-28の間は2ヶ月未満という計算）

• 1.935484（≒2022-12-30〜2023-02-28の間は2ヶ月未満という計算）

• 2.000000（≒2022-12-31〜2023-02-28の間は2ヶ月という計算）

となります。

起点の日付が1日ずつ増えていっているので、fromとtoの間隔は短くなっていくのですが、月末同士の比較になったタイミングで、 months_between の結果が再度「2」になります。これは確かに「fromとtoの間隔が狭くなっていくとき、値が大きくなることは無いはずだ」という直観と合わない結果になります。

Redshift

Redshiftにも months_between が実装されています。（公式doc）

こちらは公式ドキュメントに記載が無いのですが、Snowflake同様、非直観的な動作をすることがあります。

同じように下記のクエリで試してみると、

select months_between('2023-02-28'::date, '2022-12-28'::date);
select months_between('2023-02-28'::date, '2022-12-29'::date);
select months_between('2023-02-28'::date, '2022-12-30'::date);
select months_between('2023-02-28'::date, '2022-12-31'::date);

上から、

• 2

• 1.967741935483871

• 1.935483870967742

• 2

となります。

値の桁数が違いますが、Snowflakeとほぼ同じ挙動です。

BigQuery

BigQueryには month_between がありません。

代わりに date_diff を使って第3引数に month を指定すると値が返りますが、これは整数値しか返さず、かつ年・月のみを使った比較になります。

例えば、下記のクエリの結果はすべて「2」になります。

select date_diff(date '2023-02-01', date '2022-12-01', month);
select date_diff(date '2023-02-28', date '2022-12-01', month);
select date_diff(date '2023-02-28', date '2022-12-28', month);
select date_diff(date '2023-02-28', date '2022-12-29', month);
select date_diff(date '2023-02-28', date '2022-12-30', month);
select date_diff(date '2023-02-28', date '2022-12-31', month);

日付も加味して小数点まで考えたい場合、自前の関数を実装する必要があります。

自前での実装例（BigQuery）

実装例を下に示します。データセット名は都度変更ください。（こちらを参考に、私のユースケースに合わせて変更しています。）

create or replace function [dataset].months_between(
  to_date date,
  from_date date
) returns float64
as (
  (
    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 [dataset].months_between(date '2023-02-01', date '2022-12-01');
select [dataset].months_between(date '2023-02-28', date '2022-12-01');

これらはそれぞれ

• 2.0

• 2.8709258064516128

となります。また、

select [dataset].months_between(date '2023-02-28', date '2022-12-28');
select [dataset].months_between(date '2023-02-28', date '2022-12-29');
select [dataset].months_between(date '2023-02-28', date '2022-12-30');
select [dataset].months_between(date '2023-02-28', date '2022-12-31');

これらについてはすべて「2.0」になります。

こちらの実装例では、日数の差が広がっていくときに結果の値が減少することはなく、逆に日数の差が狭くなっていくときに結果の値が増加することもありません。

ただし、例えば「2022-02-28 ~ 2022-05-30」の差を「3ヶ月と少し」としたいのか、「ちょうど3ヶ月」としたいのかは、場合によるかと思いますので、ニーズに合わせた実装が必要です。

（どのようなケースでどのような値になって欲しいかが異なるために、BigQueryではあえて実装されていないのかもしれませんね。）

まとめ

主要なDWHサービスにおける months_between 関数について見ていきました。

Snowflake・Redshiftには months_between 関数が存在しますが、一部直観的でない挙動があります。また、BigQueryには months_between が存在しません。

いずれの場合も、必要な場合は自分で実装することになりますが、細かい部分の仕様はユースケースに応じて異なるはずです。

細かい挙動でハマってしまうことは結構あると思うので、本記事がどなたかのお役に立てることを願っています。

※この記事は、元のスクラップ https://zenn.dev/kit_ok/scraps/46374cc07618cf を清書したものです。