LOD или оконные функции: что выбрать

Разрабатывая BI‑проекты в DataLens и активно участвуя в жизни комьюнити, нередко сталкиваюсь с тем, что агрегации с детализацией (LOD) и оконные функции вызывают у многих трудности. Часть задач может быть решена обоими инструментами, а часть только одним. Нередко пользователям трудно с синтаксисом, из‑за того что они не понимают его структуру. В документации всё есть, но вопросы продолжают регулярно поступать.

Сначала определимся с терминами.

LOD (англ. Levels of Detail) — управление уровнем детализации агрегатных функций. Позволяет создавать вложенные агрегации и агрегации над группами данных, отличающиеся от группировки в чарте. Узнать больше о LOD можно в документации.

Оконные функции — агрегатные функции от показателей (агрегатов) на измерениях, задающих группировки. То есть агрегации над агрегациями в окне данных. Подробнее читайте в документации.

На развилке дорог: LOD или оконные функции

Как видите, исходная потребность общая — получить данные, агрегированные способом, отличающимся от базовой агрегации чарта. Теперь мы на развилке дорог: «Налево пойдёшь — LOD найдёшь, направо пойдёшь — окна откроешь». Так с какой задачей куда идти?

Выше описаны четыре варианта различных задач и их решений, в одном из которых можно использовать либо оконные функции, либо LOD.

Индикатор — перекрёсток

И, конечно же, как и обычные агрегатные функции, агрегации с детализацией и оконные функции можно комбинировать и вкладывать друг в друга. Распространённая потребность связана с отображением в индикаторе значения, рассчитываемого как бы в несколько этапов с группировками по разным измерениям.

В обычном случае в индикатор можно поместить один показатель и выбрать вид агрегации над ним по всем данным. Но если для расчётов нужны дополнительные измерения, то добавить их можно с помощью LOD‑секций INCLUDE и FIXED и вкладывать результат агрегации по ним в другие агрегации или оконные функции.

В примере хорошо видно, что синтаксис прост. Внутри скобок указывается показатель, который хотим считать, и действие над измерениями для этого расчёта с соответствующим списком измерений. Главное не путать секции LOD и оконных функций друг с другом и не забывать, что оконные функции работают строго с показателями. То есть, если для SALES не задана агрегация, то DataLens будет считать его измерением и сделать SUM([SALES] FIXED [CITY]) получится, а вот сделать SUM([SALES] WITHIN [CITY]) — нет. Надо будет записать так: SUM(SUM([SALES]) WITHIN [CITY]).

Ещё бывает потребность сделать так, чтобы на расчёт не влияли селекторы. В этом случае и в LOD, и в оконные функции добавляется секция BEFORE FILTERED BY с указанием списка измерений, фильтрация по которым будет игнорироваться.

RSUM(SUM([SALES]) WITHIN [MONTH] BEFORE FILTERED BY [DEP])

Ещё одной группой, очень близкой к оконным, являются функции для работы с временными рядами AGO и AT_DATE. Здесь для агрегации можно задать поле, по которому будет сдвиг, шаг сдвига и количество шагов. Например:

SUM(SUM(AGO(SUM([SALES]),[DT],'week',1) INCLUDE [DT])), где SUM([SALES]),[DT],'week',1) подсчитывает суммы с датами в измерении DT недельной давности.

Тут ошибки часто связаны с «промахами». Например, если используется поле с типом «Дата и время» и пользователь делает сдвиг на дни, то учтутся только те данные, которые по времени совпадают до секунды. Или, если даты округлены до недель и делается сдвиг на месяц или год, то совпадений не будет найдено. Хотя в феврале и марте с неделями может повезти.

Заключение

У внимательного читателя мог возникнуть вопрос, что же лучше выбирать там, где можно использовать и LOD, и оконные функции. Для меня это вопрос в том, на кого я хочу возложить эту задачу — на источник или на DataLens, так как на данный момент оконные функции реализуются внутри DataLens по полученным из источника результатам агрегации. В этом есть как плюсы в меньшей нагрузке на источник, так и минусы с такими функциями как COUNT. Ведь там, где сумма сумм или максимум от максимумов даст корректный результат, количество количеств даст именно количество количеств, а не общее количество.