SQL DATE_TRUNC
В целом специалисты по данным предпочитают более детализированные данные менее детализированным. Временные метки > даты, ежедневные данные > еженедельные и т. д.; наличие данных на более гранулярном уровне всегда позволяет «приблизить» картину. Однако чаще всего вы смотрите на данные в более «отдалённом» масштабе — на уровне недель, месяцев или даже лет. Чтобы это сделать, вам понадобится удобная функция, которая помогает округлять поля с датами или временем.
Функция DATE_TRUNC усечет дату или время до первого вхождения указанной части даты. Многословно, многословно, многословно! Что это действительно значит? Если вы усечете 2021-12-13 до месяца, это вернет 2021-12-01 (первый день месяца).
С помощью функции DATE_TRUNC вы можете усекать до недель, месяцев, лет или других частей даты для поля даты или времени. Это может сделать поля даты/времени более читаемыми, а также помочь выполнять более чистый анализ на основе времени.
В целом, это отличная функция, которую можно использовать для агрегации данных в определенные части даты, сохраняя формат даты. Однако функция DATE_TRUNC не является вашим швейцарским ножом — она не может творить чудеса или решать все ваши проблемы (мы смотрим на вас, звезда). Вместо этого DATE_TRUNC — это ваш стандартный кухонный нож — он прост и эффективен, и вы почти никогда не начинаете готовить (моделирование данных) без него.
Как использовать функцию DATE_TRUNC
Для функции DATE_TRUNC необходимо передать два аргумента:
- Часть даты: Это дни/месяцы/недели/годы (уровень), до которых вы хотите усечь ваше поле
- Дата/время, которые вы хотите усечь
Функция DATE_TRUNC может использоваться в SELECT операторах и WHERE условиях.
Большинство современных облачных хранилищ данных поддерживают некоторый тип функции DATE_TRUNC. Могут быть небольшие различия в порядке аргументов для DATE_TRUNC в разных хранилищах данных, но функциональность остается практически одинаковой.
Ниже мы опишем некоторые незначительные различия в реализации между некоторыми хранилищами данных.
Функция DATE_TRUNC в Snowflake и Databricks
В Snowflake и Databricks вы можете использовать функцию DATE_TRUNC с помощью следующего синтаксиса:
date_trunc(<date_part>, <date/time field>)
На этих платформах <date_part> передается в качестве первого аргумента в функции DATE_TRUNC.
Функция DATE_TRUNC в Google BigQuery и Amazon Redshift
В Google BigQuery и Amazon Redshift <date_part> передается в качестве первого аргумента, а <date/time field> — в качестве второго аргумента:
date_trunc(<date/time field>, <date part>)
Примечание о BigQuery: функция DATE_TRUNC в BigQuery поддерживает усечение типов данных даты, тогда как в Snowflake, Redshift и Databricks <date/time field> может быть типом данных даты или метки времени. BigQuery также поддерживает функции DATETIME_TRUNC и TIMESTAMP_TRUNC для усечения более детализированных типов данных даты/времени.
Макрос dbt, который стоит запомнить
Почему Snowflake, Amazon Redshift, Databricks и Google BigQuery решили использовать разные реализации по сути одной и той же функции, остается загадкой, и не стоит ломать голову, пытаясь это выяснить. Вместо того чтобы запоминать, что идет первым — <date_part> или <date/time field>, (что, честно говоря, мы никогда не можем запомнить) вы можете полагаться на макрос dbt Core, чтобы избежать капризного синтаксиса.
Адаптеры поддерживают кросс-базовые макросы, которые помогают писать некоторые функции, такие как DATE_TRUNC и DATEDIFF, без необходимости запоминать специфичный синтаксис функций для каждой базы данных.
Используя Jaffle Shop — простой набор данных и проект dbt — вы можете усечь order_date из таблицы orders с помощью макроса dbt DATE_TRUNC:
select
order_id,
order_date,
{{ date_trunc("week", "order_date") }} as order_week,
{{ date_trunc("month", "order_date") }} as order_month,
{{ date_trunc("year", "order_date") }} as order_year
from {{ ref('orders') }}
Выполнение вышеуказанного кода даст следующие примерные результаты:
| Loading table... |
Поля order_week, order_month и order_year являются усеченными значениями из поля order_date.
