Перейти к основному содержимому

SQL DATEDIFF

«Сколько времени прошло с тех пор, как этот клиент последний раз делал у нас заказ?»
«Какое среднее количество дней до конверсии?»

Бизнес-пользователи задаются этими вопросами, и специалистам по данным придется на них отвечать. Единственный способ решить их — это вычислить время между двумя разными датами. К счастью, существует удобная функция DATEDIFF, которая может это сделать за вас.

Функция DATEDIFF возвращает разницу в указанных единицах (например, дни, недели, годы) между начальной и конечной датой/временем. Это простая и широко используемая функция, которую вы будете использовать чаще, чем ожидаете.

DATEDIFF немного похожа на вашу любимую пару носков; обычно вы легко находите первый и чувствуете, что день будет отличным. Но по какой-то причине, чтобы найти второй носок, нужно немного покопаться в ящике. DATEDIFF — это эта пара носков: вы неизбежно будете искать синтаксис в Google почти каждый раз, когда используете её, но не сможете обойтись без неё в течение дня.

На этой странице мы рассмотрим, как использовать функцию DATEDIFF в различных хранилищах данных и как писать более стандартизированные функции DATEDIFF с использованием макроса dbt (или успешно находить свои носки в паре за один раз).

Как использовать функцию DATEDIFF​

Для функции DATEDIFF передаются три элемента или аргумента:

  • Часть даты: это дни/месяцы/недели/годы (единица) разницы, которая вычисляется
  • Первая (начальная) дата/время
  • Вторая (конечная) дата/время

Функция DATEDIFF может использоваться в операторах SELECT и в условиях WHERE.

Большинство современных облачных хранилищ данных поддерживают некоторый тип функции DATEDIFF. Могут быть небольшие различия в порядке аргументов и названии функции DATEDIFF в разных хранилищах данных, но функциональность остается практически одинаковой.

Ниже мы опишем некоторые незначительные различия в реализации между некоторыми хранилищами данных.

Синтаксис функции SQL DATEDIFF в Snowflake, Databricks и Redshift​

Синтаксис для использования функции DATEDIFF в Snowflake, Amazon Redshift и Databricks выглядит следующим образом:

datediff(<date part>, <start date/time>, <end date/time>)

Примечание о Databricks: Databricks дополнительно поддерживает отдельную функцию DATEDIFF, которая принимает только два аргумента: начальную и конечную дату. Функция всегда возвращает разницу между двумя датами в днях.

DATEDIFF в Google BigQuery​

Синтаксис для использования функции DATEDIFF в Google BigQuery выглядит следующим образом:

  • В отличие от Snowflake, Amazon Redshift и Databricks, где <date part> передается в качестве первого аргумента, в Google BigQuery <date part> передается в качестве последнего аргумента.
  • Google BigQuery также называет функцию DATETIME_DIFF с дополнительным подчеркиванием, разделяющим имя функции. Это соответствует предпочтению Google BigQuery использовать подчеркивания в именах функций.
  • Аргументы DATETIME_DIFF — это datetime, а не date; функции DATEDIFF в Snowflake, Redshift и Databricks поддерживают несколько типов дат, таких как даты и временные метки. BigQuery также поддерживает отдельную функцию DATE_DIFF, которая возвращает разницу между двумя типами дат, в отличие от DATETIME_DIFF, которая поддерживает только тип datetime.

Герой в тени: макрос DATEDIFF в dbt!​

Возможно, вы сможете запомнить синтаксис функции DATEDIFF для основного хранилища данных, которое вы используете. Что произойдет, когда вы перейдете на другое для новой работы или нового стека данных? Запоминание, есть ли подчеркивание в имени функции или в каком аргументе передается <date part>, не доставляет удовольствия и приводит к неизбежным, бесчисленным поискам в Google «datediff в bigquery».

К счастью, dbt-core вас поддерживает! dbt Core — это продукт с открытым исходным кодом, который помогает специалистам по данным писать свои трансформации данных, следуя лучшим практикам программной инженерии.

Адаптеры поддерживают кросс-базовые макросы, чтобы помочь вам писать определенные функции, такие как DATE_TRUNC и DATEDIFF, без необходимости запоминать сложный синтаксис функций.

Используя макрос DATEDIFF, вы можете вычислить разницу между двумя датами, не беспокоясь о сложном синтаксисе. Это означает, что вы можете успешно запускать один и тот же код в нескольких базах данных, не беспокоясь о сложных различиях в синтаксисе.

Используя jaffle shop, простой набор данных и проект dbt, мы можем вычислить разницу между двумя датами, используя макрос DATEDIFF в dbt:

select
   *,
   {{ datediff("order_date", "'2022-06-09'", "day") }}
from {{ ref('orders') }}

Это вернет все поля из таблицы заказов и разницу в днях между датами заказов и 9 июня 2022 года.

Под капотом этот макрос принимает ваши входные данные и создает соответствующий SQL-синтаксис для функции DATEDIFF, специфичный для вашего хранилища данных.

0
Отредактировать эту страницу