Сосредоточение внимания на наиболее важных концепциях для специалистов по данным
Что делает хорошего SQL-кодировщика, так это способность манипулировать данными любым способом, который им нравится - большая часть этого - возможность манипулировать датами. Даты чрезвычайно важны, потому что компании любят сравнивать и оценивать эффективность бизнеса в разные периоды времени. Следовательно, возможность манипулировать датами имеет важное значение для бизнес-операций высшего уровня и бизнес-отчетности.
В этой статье мы рассмотрим 5 наиболее важных и наиболее полезных функций DATE в SQL, а также некоторые практические бизнес-кейсы, в которых они могут быть использованы.
1. DATE_TRUNC ()
Синтаксис:
DATE_TRUNC(date_expression, date_part)Что оно делает?
DATE_TRUNC () сокращает дату до указанной части даты.
В приведенном ниже примере, поскольку мы указали date_part = MONTH, date_trunc () усечет дату, чтобы получить первый день месяца:
DATE_TRUNC('2021-06-28', MONTH) = '2021-06-01'
Другой пример: если мы укажем date_part = YEAR, то получим первый день года указанной даты:
DATE_TRUNC('2021-06-28', YEAR) = '2021-01-01'
Когда это полезно?
- DATE_TRUNC чрезвычайно полезен, когда вы хотите агрегировать числа на периодической основе, например, еженедельно, ежемесячно или ежегодно.
- DATE_TRUNC необходим при проведении когортного анализа, когда вы обычно группируете пользователей по месяцам.
Примеры)
Пример 1: Предположим, вы хотите получить сумму продаж за неделю из приведенной ниже таблицы:
with sales_data as (
SELECT DATE_TRUNC(date, WEEK) as week_date,
daily_sales
FROM sales
)
SELECT week_date,
SUM(daily_sales) as weekly_sales
FROM sales_data
GROUP BY week_date
2. DATE_DIFF ()
Синтаксис:
DATE_DIFF(date_expression_1, date_expression_2, date_part)Что оно делает?
DATE_DIFF () сравнивает две даты и возвращает разницу в части даты между двумя датами.
Например, если date_part = DAY, то DATE_DIFF () возвращает количество ДНЕЙ между двумя датами. Если date_part = MONTH, то DATE_DIFF () возвращает количество месяцев между двумя датами.
DATE_DIFF('2021-01-02', '2021-01-01', DAY) = 1
Когда это полезно?
- DATE_DIFF () полезен, когда вы хотите сравнить две даты, например, когда пакет был отправлен и когда пакет был доставлен, или когда пользователь зарегистрировался и когда они были отменены.
- DATE_DIFF () также полезен в предложении WHERE, если вы хотите отфильтровать даты, которые произошли X периодов назад (например, 5 дней назад, 2 недели назад, последний месяц).
Примеры)
Пример 1: Предположим, вы хотите узнать время между отправкой пакета и доставкой пакета:
SELECT order_id
, DATE_DIFF(date_received, date_shipped, DAY) as shipping_time
FROM orders
Пример 2: Используя ту же таблицу выше, предположим, что вы хотите получить все заказы, время доставки которых составляет менее 10 дней:
SELECT order_id
, amount
FROM orders
WHERE DATE_DIFF(date_received, date_shipped, DAY) < 10
3. DATE_ADD () / DATE_SUB ()
Синтаксис:
DATE_ADD(date_expression, INTERVAL int64 date_part)DATE_SUB(date_expression, INTERVAL int64 date_part)
Что оно делает?
DATE_ADD () добавляет к дате указанное количество частей даты. И наоборот, DATE_SUB вычитает указанное количество частей даты из даты.
DATE_ADD('2021-01-01', INTERVAL 3 DAY) = '2021-01-04'
DATE_SUB('2021-01-04', INTERVAL 3 DAY) = '2021-01-01'
DATE_ADD('2021-01-01', INTERVAL 1 MONTH) = '2021-02-01'
Когда это полезно?
- DATE_ADD () и DATE_SUB () могут использоваться аналогично, как DATE_DIFF () в предложении WHERE, для фильтрации дат, которые произошли X периодов назад или X периодов в будущем.
Примеры):
Пример 1: Предположим, вы хотите получить все заказы, время доставки которых составляет менее 10 дней:
SELECT order_id
, amount
FROM orders
WHERE DATE_ADD(date_shipped, INTERVAL 10 DAY) > date_received
4. ВЫПИСКА ()
Синтаксис:
EXTRACT(part FROM date_expression)Что оно делает?
EXTRACT () возвращает значение, соответствующее указанной части даты.
EXTRACT(DAY FROM '2021-01-03') = 3 EXTRACT(MONTH FROM '2021-01-03') = 1 EXTRACT(YEAR FROM '2021-01-03') = 2021
Когда это полезно?
- Использование EXTRACT () - это простой способ получить определенные компоненты даты, которые вас интересуют.
- Например, если ваша компания составляет отчеты по номерам недель, вы можете использовать EXTRACT (), чтобы получить номер недели на заданную дату для данной записи.
- EXTRACT () позволяет получить номер месяца или год от даты, которые можно использовать в качестве функций для модели машинного обучения.
5. CURRENT_DATE ()
Синтаксис:
CURRENT_DATE([time_zone])Что оно делает?
CURRENT_DATE () возвращает текущую дату выполнения запроса в указанном часовом поясе. Обратите внимание, что параметр часового пояса является необязательным и указывать его не нужно.
Когда это полезно?
- Использование CURRENT_DATE () - это более простой способ ссылки на сегодняшнюю дату в отличие от жестко заданной даты, что особенно полезно, если это запрос, запланированный на Airflow, или запрос, который вы часто используете.
Примеры):
Пример 1: Предположим, вы хотите получить все заказы, которые были отправлены за последнюю неделю:
SELECT order_id
, amount
FROM orders
WHERE DATE_DIFF(CURRENT_DATE(), date_shipped, DAY) < 7
Пример 2: Предположим, вы хотите получить все заказы за любой год, которые были отправлены в том же месяце, что и сегодня:
SELECT order_id
, amount
FROM orders
WHERE EXTRACT(MONTH FROM date_shipped) = EXTRACT(MONTH FROM CURRENT_DATE())
Спасибо за прочтение!
Теперь, когда вы понимаете, как работают пять основных функций DATE в SQL, пора проверить свои знания. Я предоставлю два ресурса:
- Leetcode - это веб-сайт, на котором есть сотни проблем с кодированием, которые вы можете решить. Одна проблема под названием Повышение температуры - это хорошая проблема, которая бросает вызов вашей способности работать с финиками.
- w3resource - еще один отличный ресурс - в этой ссылке 21 проблема, связанная с datetime.
Как всегда, я желаю вам удачи в учебе!
Не знаете, что читать дальше? Я подобрала для вас другую статью:
и еще один!
Теренс Шин
- Если вам понравилось, подписывайтесь на меня на Medium, чтобы узнать больше
- Заинтересованы в сотрудничестве? Давайте подключимся к LinkedIn
