Closed5

# 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
)
)
カイト@データ基盤屋

BigQueryについては、↑で実装できた。想定通りの挙動。

from=2022-02-28, to=2023-05-30のような場合に「3ヶ月」にするのか「3ヶ月ちょい」にするのかは、文脈によりそう。（それが分かっているからBQでは実装されていないのかもしれない。）

カイト@データ基盤屋

ふと、 「Redshiftにあるmonths_between関数、こんな複雑な条件分岐ちゃんとできてるのかな…？」 と疑問に思った。

やってみたら直観に反する結果に。利用時は注意が必要かも？

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

→2

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

→1.96~~

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

→1.93~~

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

→2

カイト@データ基盤屋

SnowFlakeはまだやったことがないが、公式docに「非直観的な動作につながります」との記載あり。

↓引用

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 |
+----------------+----------------+----------------+
このスクラップは2023/10/11にクローズされました