В этом уроке вы узнаете про основные функции по работе с датой и временем в DataLens. Познакомитесь с их аргументами и параметрами. На конкретном примере увидите, как они работают. А в конце мы разберем по шагам три практические задачи, где наглядно увидим прикладной смысл и применение некоторых функций.
Вы научитесь делать
Рассчитывать разницу в днях между двумя датами.
Добавлять/вычитать из даты дни, недели и года.
Определять порядковый номер дня в году.
И многое другое.
ОСНОВНЫЕ ФУНКЦИИ
Для начала давайте разберем все имеющиеся функции в DataLens, которые работают с датой и временем.
DATEPART
Данная функция возвращает часть даты в виде целого числа. Например, номер дня в месяце или номер дня недели. Одним словом, порядковый номер какого-то измерителя даты.
Синтаксис: DATEPART( datetime, unit [ , firstday ] )
datetime – исходная дата, из которой нужно получить результат.
unit – аргумент, указывающий, что именно нужно получить (номер дня, номер недели и т.д.)
Возможные значения unit:
- “year” — номер года (см. YEAR);
- “quarter” — номер квартала года (от 1 до 4) (см. QUARTER);
- “month” — номер месяца в году (см. MONTH);
- “week” — номер недели в году по ISO 8601 (см. WEEK);
- “dayofweek”, “dow” — номер дня недели (см. DAYOFWEEK);
- “day” — номер дня в месяце (см. DAY);
- “hour” — номер часа в дне (см. HOUR);
- “minute” — номер минуты в часе (см. MINUTE);
- “second” — номер секунды в минуте (см. SECOND).
[ , firstday ] – если выбран “dayofweek”, то дополнительным параметром firstday можно задать, какой день недели считать первым — по умолчанию это понедельник.
Теперь давайте на практике посмотрим, как это работает. Добавим в нашу таблицу новые поля с разными аргументами unit.
А вот так выглядит формула одного из столбцов.
DATEADD
Данная функция позволяет выполнять с датой необходимые вычисления. Например, рассчитать новую дату, прибавив к ней 5 дней или 5 месяцев.
Синтаксис: DATEADD( datetime [ , unit [ , number ] ] )
datetime – исходная дата, из которой нужно получить результат.
unit – аргумент, указывающий, с какой именно частью даты необходимо произвести вычисление (месяц, день и т.д.)
Аргумент unit принимает следующие значения:
- “year” — год;
- “month” — месяц;
- “day” — день;
- “hour” — час;
- “minute” — минута;
- “second” — секунда.
number – аргумент, указывающих число дней, месяцев или лет, на которое надо вычислить дату. Задается целым числом. Может принимать отрицательные значения.
Теперь давайте на практике посмотрим, как это работает. Добавим в нашу таблицу новые поля с разными аргументами unit. Попробуем рассчитать новые даты. Названия столбцов указывают на аргументы unit в формуле, а цифры в скобках – это количество, на которое дата должна измениться.
Ниже можно посмотреть на пример формулы одной из колонок.
DATETRUNC
Данная функция позволяет округлять дату до нужной размерности. Например, до месяц, квартала или года. Ее удобно использовать, когда нужно агрегировать данные, увидеть показатели только за годы, без разбивки по дням или месяцам.
Синтаксис: DATETRUNC( datetime, unit [ , number ] )
datetime – исходная дата, из которой нужно получить результат.
unit – аргумент, указывающий на размерность даты (месяц, день и т.д.)
Поддерживаемые значения unit:
- “second”;
- “minute”;
- “hour”;
- “day” (при заданном number используется номер дня в году);
- “week”;
- “month”;
- “quarter”;
- “year”.
number – также можно указать степень округление до нужного количества временных единиц. Отсутствие аргумента эквивалентно значению 1.
Попробуем округлить даты в нашем примере. При округлении до года мы увидим, как суммы продаж агрегировались в разрезе двух лет.
А так будет выглядеть результат по кварталам. Суммы сгруппировались на первое число каждого квартала.
DAY
Данная функция возвращает номер дня в месяце необходимой даты. Упрощенный вариант функции DatePart.
Синтаксис: DAY( datetime )
datetime – исходная дата, из которой нужно получить результат.
Достаточно просто указать нужную дату, чтобы получить номер дня месяца. Результат будет выглядеть вот так.
DAYOFWEEK
Данная функция возвращает номер дня недели в соответствии с ISO 8601. Упрощенный вариант функции DatePart. По умолчанию 1 – это понедельник, 7 – воскресенье.
Синтаксис: DAYOFWEEK( datetime [ , firstday ] )
datetime – исходная дата, из которой нужно получить результат.
firstday – с помощью дополнительного параметра можно указать, какой день считать первым в неделе. Это удобно использовать при расчетах показателей с середины недели. Например, если отчет по продажам формируется раз в неделю по четвергам. С помощью данного параметра можно настроить начало операционной недели с четверга. И затем сравнивать показатели продаж с прошлой операционной неделей, которая также начиналась с четверга.
Допустимые значения:
- “Monday”, “Mon” — понедельник;
- “Tuesday”, “Tue” — вторник;
- “Wednesday”, “Wed” — среда;
- “Thursday”, “Thu” — четверг;
- “Friday”, “Fri” — пятница;
- “Saturday”, “Sat” — суббота;
- “Sunday”, “Sun” — воскресенье.
Вот так будет выглядеть результат при классической неделе.
А так, если мы установим начало недели – Четверг.
HOUR
Данная функция возвращает номер часа в дате. Если дата без времени, то результат будет 0.
Синтаксис: HOUR( datetime )
Попробуем на нашем примере.
MINUTE
Данная функция возвращает номер минуты в дате. Если дата без времени, то результат будет 0.
Синтаксис: MINUTE( datetime )
SECOND
Данная функция возвращает номер секунды в минуте в нужной дате. Если дата без времени, то результат будет 0.
Синтаксис: SECOND( datetime )
MONTH
Данная функция возвращает номер месяца в необходимой дате. Упрощенный вариант функции DatePart.
Синтаксис: MONTH( datetime )
NOW
Возвращает текущую дату и время в зависимости от источника данных и типа соединения.
Синтаксис: NOW()
Данная функция не имеет аргументов и параметров, так как просто возвращает системную дату и время.
В нашем примере это будет выглядеть вот так.
QUARTER
Данная функция возвращает номер квартала (от 1 до 4) в необходимой дате. Упрощенный вариант функции DatePart.
Синтаксис: QUARTER( datetime )
TODAY
Данная функция очень похожа на NOW(). Но она возвращает лишь текущую дату, без времени.
Синтаксис: TODAY()
WEEK
Данная функция возвращает номер недели в году в соответствии с ISO 8601. Первой считается неделя, которая содержит первый четверг года и 4.01.
Синтаксис: WEEK( value )
value – исходная дата.
YEAR
Данная функция возвращает год необходимой даты. Упрощенный вариант функции DatePart.
Синтаксис: YEAR( datetime )
datetime – исходная дата, из которой нужно получить результат.
ПРАКТИКА
А теперь давайте рассмотрим несколько простых задач, чтобы на конкретных и более прикладных примерах увидеть, как работают данные функции.
Задача 1
Рассчитать разницу в днях между текущей датой и датой отчета. Это может пригодится, чтобы, например, увидеть количество дней просрочки или простоя.
Создадим в чарте два новых поля – с текущей датой на сегодня и разницей между текущей датой и датой отчета. Для наглядности поместим это в разные столбцы.
Выведем оба поля в чарт.
В последней колонке появилось количество дней. Теперь давайте добавим условную заливку поля, чтобы сразу увидеть, где количество дней самое большое (например, самый долгий простой). Так как даты у нас идут по возрастанию, то градиент тоже распределится равномерно.
Задача 2
Допустим, у нас по условиям договора срок оплаты счета – 2 недели. В отчете у нас есть некие даты, от которых по договору и идет отсчет этих двух недель. Дабы не нарушать сроки оплаты, менеджмент компании хочет видеть этот самый расчетный последний день оплаты, чтобы заранее это отследить и не пропустить.
Для этого нам нужно будет сделать всего лишь один расчет – просто добавить к дате в отчете две недели. И поможет нам в этом функция DateAdd.
Формула будет выглядеть вот так. Просто указываем исходную дату, размерность (неделя) и количество. Если бы нам нужно было убавить две недели назад, то указали бы -2.
И получаем наш результат.
Теперь в отчете сразу видно, когда и по какой поставке истекает срок оплаты.
А теперь давайте рассчитаем, сколько у нас осталось дней для оплаты, и подсветим цветом истекающие сроки оплаты. Предположим, что текущая дата – это 12.12.2022. Добавим для наглядности этот столбец.
И теперь рассчитаем разницу между полем +2 недели и текущей датой.
В этом столбце мы и увидим, сколько дней осталось на оплату. С помощью условной заливки подсветим красным истекающие сроки (где меньше всего дней осталось), а зеленым, кому платить еще нескоро.
В итоге мы получим красивый и информативный отчет, полностью удовлетворяющий поставленную задачу бизнеса.
Задача 3
А теперь давайте рассмотрим задачку чуть посложнее. Как нам получить порядковый номер дня года. В типовых функциях DataLense есть возможность быстро получить только номер дня в месяце. Но очень часто возникает потребность узнать именно номер дня в году. Например, 31 декабря – это обычно 365 день в году. Попробуйте сначала сами догадаться, как это сделать.
На самом деле, сделать это очень просто. В этом на поможет функция DateTrunc. Если помните, данная функция изменяет размерность дат и группирует данные на первое число нужной размерности (на первое число квартала или года). Поэтому с ее помощью сначала давайте определим первое число года для каждой даты.
Формула будет выглядеть так.
В нашей таблице рядом с датами появился первый день каждого года.
И теперь все, что нам надо, это рассчитать разницу в днях между исходной датой слева и началом года.
Но обратите внимание, что к полученному результату нам нужно добавить 1. Так как формула рассчитывает именно разницу, дельту дней, то она не учитывает тот самый первый день года. Его мы и добавляем в конце.
В итоге мы получаем порядковый номер дня в каждом году.
На этом все! Надеюсь, что данный урок был вам полезен.
Также данный урок можно посмотреть или распечатать в формате PDF.
Наши курсы
Если вам понравился урок, то можете поддержать проект по кнопке ниже. Это очень поможет делать для вас больше интересных и полезных выпусков. Спасибо!
Задать вопросы и обсудить волнующие темы про аналитику данных теперь можно на нашем форуме.
Если вам понравился урок, подписывайтесь на канал или группу и ставьте лайки. А также пишите в комментариях свои кейсы или вопросы. Самые интересные мы обязательно разберем.