Получите Impala, эквивалент MONTHS_BETWEEN () в Snowflake

У меня возникла проблема при переносе запросов из Impala в Snowflake:

Импала

SELECT period
    , now() as dt_today
    , MONTHS_BETWEEN(now(), period) as mb
FROM my_table

дает

period                  dt_today                        mb
--------------------------------------------------------------------------
2018-10-30T21:43:57Z    2020-02-21 10:21:12.827383000   15.709677419354838

Снежинка

SELECT period
    , CURRENT_TIMESTAMP() as dt_today
    , DATEDIFF('month', CURRENT_TIMESTAMP(), period) as mb
FROM my_table

дает

period                  dt_today                        mb
--------------------------------------------------------------------------
2018-10-30T21:43:57Z    2020-02-21 10:21:12.827383000   16

Теперь из документации Snowflake я понимаю, что при указании month в DATEDIFF, Snowflake будет использовать только «месяц и год от даты», это означает, что разница не такая точная, как у Impala. Я пробовал реализовать прокси, например, взяв месяц разницу, а затем примените некоторые вычисления, чтобы получить часть с плавающей запятой как таковую, но я все равно получаю неправильное количество месяцев:

DATEDIFF('month', period, CURRENT_TIMESTAMP()) + (GREATEST(DAY(period), DAY(CURRENT_TIMESTAMP())) -  LEAST(DAY(period), DAY(CURRENT_TIMESTAMP()))) / 31

а также следующее, чтобы получить еще более точную информацию, но это все равно неверно:

DATEDIFF('day', period, CURRENT_TIMESTAMP())/31 + (GREATEST(DAY(period), DAY(CURRENT_TIMESTAMP())) -  LEAST(DAY(period), DAY(CURRENT_TIMESTAMP()))) / 31

Вопрос: как я могу точно воспроизвести MONTHS_BETWEEN() Импалы в Snowflake?


snowflake-cloud-data-platform datediff
person nicholas    schedule 21.02.2020    source источник

Ответы (3)


arrow_upward
1
arrow_downward

TL;DR

IFF(DAY(DATE1) >= DAY(DATE2), DATEDIFF('month', DATE2, DATE1), DATEDIFF('month', DATE2, DATE1) - 1)
+
IFF(DAY(DATE1) >= DAY(DATE2), (GREATEST(DAY(DATE1), DAY(DATE2)) - LEAST(DAY(DATE1), day(DATE2))) / 31, 1 - (GREATEST(DAY(DATE1), DAY(DATE2)) - LEAST(DAY(DATE1), DAY(DATE2))) / 31)

Функция Impala MONTHS_BETWEEN(DATE1, DATE2) работает следующим образом:

  • MONTHS_BETWEEN('2019-04-13', '2019-02-10') доходность 2.0967 (2 полных месяцев + 3/31=0967)
  • MONTHS_BETWEEN('2019-04-13', '2019-02-03') доходность 1.7741 (1 полных месяцев + 1-(7/31)=0967)

Теперь мы знаем, что Snowflake DATEDIFF(DATE1, DATE3) применяет простую помесячную операцию:

  • DATEDIFF('month', '2019-04-13', '2019-02-10') дает 2 (04 - 02)
  • DATEDIFF('month', '2019-04-13', '2019-02-03') дает 2 (04 - 02)

Чтобы получить целую часть MONTHS_BETWEEN Импалы с помощью функций Snowflake, мы применяем следующую логику:

IFF(DAY(DATE1) >= DAY(DATE2), DATEDIFF('month', DATE2, DATE1), DATEDIFF('month', DATE2, DATE1) - 1)

Чтобы получить дробную часть MONTHS_BETWEEN Импалы с помощью функций Снежинки, мы применяем следующую логику:

IFF(DAY(DATE1) >= DAY(DATE2), (GREATEST(DAY(DATE1), DAY(DATE2)) - LEAST(DAY(DATE1), day(DATE2))) / 31, 1 - (GREATEST(DAY(DATE1), DAY(DATE2)) - LEAST(DAY(DATE1), DAY(DATE2))) / 31)

Мы просто складываем их вместе, чтобы получить точное значение Импалы:

IFF(DAY(DATE1) >= DAY(DATE2), DATEDIFF('month', DATE2, DATE1), DATEDIFF('month', DATE2, DATE1) - 1)
+
IFF(DAY(DATE1) >= DAY(DATE2), (GREATEST(DAY(DATE1), DAY(DATE2)) - LEAST(DAY(DATE1), day(DATE2))) / 31, 1 - (GREATEST(DAY(DATE1), DAY(DATE2)) - LEAST(DAY(DATE1), DAY(DATE2))) / 31)
person nicholas    schedule 21.02.2020

arrow_upward
0
arrow_downward

Насколько точной должна быть ваша разница?

Потому что мой первый призыв состоял в том, чтобы вычесть обе даты (получить разницу в днях) - или вы можете просто использовать dateiff, просто извлекая день - и перейти оттуда (либо разделив на 30/31, если приближение подойдет, либо с более сложным вычислить, если мне нужно точное решение)

Ваше решение неверно, потому что месяцы в dateiff будут отличаться на 1 даже в последующие дни, если вы возьмете, скажем, 31 декабря и 1 января - эти 2 дадут "1" в dateiff и по году, и по месяцу, и по дню. .

person MMV    schedule 21.02.2020
comment
Я отредактировал свой ответ с помощью другого прокси, который я пробовал, но мне нужна точная точность, и это близко, но не совсем! - person nicholas; 21.02.2020
comment
Хм, в этом случае я бы подумал о том, чтобы проверить полное количество месяцев, использовать add_months, чтобы добавить их, проверить, сколько дней вам еще нужно добавить, и использовать это, чтобы получить десятичную часть? - person MMV; 21.02.2020

arrow_upward
0
arrow_downward

Snowflake изначально поддерживает это: Примечания к выпуску 4.42.

MONTHS_BETWEEN

MONTHS_BETWEEN( <date_expr1> , <date_expr2> )

Возвращает количество месяцев между двумя значениями DATE или TIMESTAMP.

person Lukasz Szozda    schedule 19.12.2020