Функции для работы с временными рядами
Эти функции позволяют получать значения показателя, отнесенного к конкретным дате и времени. Дата и время задаются либо точными значениями, либо в виде смещения вдоль заданной временной оси.
В некоторой степени эти функции схожи с оконной LAG.
Основное отличие — LAG
не учитывает значения измерений и использует смещение, заданное в виде количества строк.
Функции временных рядов работают с конкретными значениями или смещениями, заданными во временных единицах (дни, часы или секунды). За счет этого они становятся чувствительными к пропущенным значениям в данных. В результате формула AGO(SUM([Sales]), [Date], "year")
вернет NULL
, если отсутствуют данные на ту же дату за предыдущий год. Также стоит учитывать, что при использовании данных в формате даты и времени у вас могут быть данные на ту же дату, но не на ту же самую секунду. А при округлении дат до недель сдвиг в годах тоже вернет NULL
.
Синтаксис
Функции временных рядов поддерживают расширенный синтаксис:
<FUNCTION_NAME>(
arg1, arg2, ...
[ BEFORE FILTER BY filtered_field_1, ... ]
[ IGNORE DIMENSIONS dimension_1, ... ]
)
BEFORE FILTER BY
Если какие-то поля перечислены в BEFORE FILTER BY
, то функция вычисляется до применения фильтрации по этим полям.
Например, если c помощью функции AGO рассчитать [Sales]
за период (год назад) от текущей даты:
AGO([Sales], [Date], "year", 1)
и при этом в чарте используется фильтр, ограничивающий даты одним годом (2018 < [Year] <= 2019
, где Year
равно YEAR([Date])
), то все строки, относящиеся к 2018 году, будут исключены из запроса. В результате функция вернет NULL
.
Если в формулу с AGO
добавить BEFORE FILTER BY
:
AGO([Sales], [Date], "year", 1 BEFORE FILTER BY [Year])
то функция вернет значение [Sales]
.
Временное измерение, заданное вторым аргументом функций временных рядов, автоматически добавляется в BEFORE FILTER BY
. Таким образом, следующие две формулы эквивалентны:
AGO([Sales], [Date], "week")
AGO([Sales], [Date], "week" BEFORE FILTER BY [Date])
IGNORE DIMENSIONS
IGNORE DIMENSIONS
позволяет исключить измерения из критериев поиска. Для вычисления значения показателя для какой-то даты поиск нужной строки производится с помощью построчного сопоставления значений всех измерений со значениями этих же измерений в текущей строке. Если какие-либо измерения косвенно или напрямую зависят от даты, то запрос может вернуть пустые значения (NULL
).
Например, если делается запрос с полями [Date]
, [Month]
(месяц от даты) и AGO([Sales], [Date], "month")
, то будет невозможно найти строку, в которой значение [Date]
на месяц меньше текущего, а значение [Month]
совпадает со значением в текущей строке. Из-за этого результатом AGO
будет NULL
во всех строках.
Чтобы функция вернула правильное значение [Sales]
, с помощью IGNORE DIMENSIONS
исключите измерение [Month]
:
AGO([Sales], [Date], "month" IGNORE DIMENSIONS [Month])
AGO
Синтаксис:AGO( measure, date_dimension [ , unit [ , number ] ] )
илиAGO( measure, date_dimension [ , unit [ , number ] ] [ BEFORE FILTER BY ... ] [ IGNORE DIMENSIONS ... ] )
Вычисляет значение показателя measure
для даты/времени с указанным смещением.
Аргумент date_dimension
задает измерение, вдоль которого делается смещение.
Аргумент number
задает размер смещения в единицах аргумента unit
. Задается целым числом. Может принимать отрицательные значения. Значение по умолчанию равно 1
.
Аргумент unit
задает единицу измерения для number
. Принимает следующие значения:
"year"
— год;"month"
— месяц;"week"
— неделя;"day"
— день (значение по умолчанию);"hour"
— час;"minute"
— минута;"second"
— секунда.
Возможен вариант использования AGO( measure, date_dimension, number )
. В этом случае аргумент unit
принимает значение "day"
.
AT_DATE
Синтаксис:AT_DATE( measure, date_dimension, date_expr )
илиAT_DATE( measure, date_dimension, date_expr [ BEFORE FILTER BY ... ] [ IGNORE DIMENSIONS ... ] )
Вычисляет measure
для даты/времени, заданных выражением date_expr
. Позволяет получить показатель на начало или конец периода, или на конкретную дату.
Аргумент date_dimension
задает измерение, вдоль которого делается смещение.
В качестве аргумента date_expr
можно использовать:
- конкретную дату;
- функцию получения текущей даты TODAY;
- функции для вычисления даты и времени.