LOD в DalaLens на пальцах

Большинству агрегатных функций в Даталенс может быть задан уровень детализации

Структура описания агрегации с уровнем детализации:

AGG -- агрегаткая функция (SUM, COUNT, AVG, MAX...)

(

   [field] -- поле по которому выполняется агрегация

   FIXED | INCLUDE | EXCLUDE -- ключевое слово, определяющие группировку

   [field_a], [field_b] ... -- поле или перечень полей-измерений по которым выполняется группировка

)

FIXED - задает поля для группировки отличающейся от той, что есть в чарте. Например, если в чарте есть поля Страна, Город, Район, то показатели будут агрегированны по каждой комбинации из них. Если задать FIXED [Страна], то в полученном поле будут агрегаты по Странам, с игнорированием Городов и Районов, например, все население страны, а FIXED [Континент] - континента. Для агрегации по всему диапазону значений, список полей оставляется пустым, так COUNT([Дом] FIXED) даст полное число записей о домах.

INCLUDE - задает поля для более узкой группировки чем в чарте, позволяя включить в чарт агрегации по измерениям, которых в чарте нет. Например, если в чарте нет Домов, по ним можно сделать агрегацию INCLUDE [Дом] и добавить результат в чарт.

ВАЖНО: Полученное через INCLUDE новое значение в чарте должно быть показателем, по нему должна быть задана агрегация. Eсли собрано количество жильцов в каждом доме, нужно определить, что с этими числами делать по Району-Городу-Стране в чарте без Домов. Суммировать или среднее или максимум...? AVG(COUNT([число этажей] INCLUDE [Дом])) - даст среднее количество этажей на дом по каждому Району-Городу-Стране

EXCLUDE - исключает поля из группировки чарта, то есть то же, что FIXED но с другого бока. EXCLUDE [Район] идентично FIXED [Страна],[Город]

LOD выражение аналогично подзапросу:

SELECT t.country, t.city, t.district,

   AVG(subq.c)

FROM t

LEFT JOIN

   (SELECT COUNT(levels) as c, house FROM t GROUP BY house) as subq

ON t.house = subq.house

GROUP BY t.country, t.city, t.district;

--нельзя во внешнем запросе использовать subq.c без агрегации, так как оно не входит в GROUP BY

--а что если результаты LOD (подзапроса) должны быть частью логической функции (сравнения)?

--как нельзя написать WHERE t.levels > AVG(subq.c), так нельзя написать [число этажей] > AVG(COUNT([число этажей] INCLUDE [Дом]))

--решение аналогично тому, что и для SQL запроса...

Подсчет уникальных значений или "размножились значения, как быть?"

Один из самых частых запросов, связанный LOD - необходимость учитывать только одно значение из повторяющихся в группе строк. Повторяющиеся значения возникают в следствии соединения таблиц 1:N, когда одной строке одной таблицы соответствует несколько строк в другой (план отгрузки и факты отгрузок), либо когда в каждой записи есть информация по группе записей (позиция в заказе и сумма/количество всего заказа).

В простом SQL это решалось бы, например, так: SELECT sum(st.val) FROM (SELECT DISTINCT id, val FROM t) as st

Или SELECT sum(st.val) FROM (SELECT id, MAX(val) FROM t GROUP BY id) as st

В Datalens аналогичное решается функцией: SUM(MAX([val] INCLUDE [id]))

При использовании Clickhouse вместо MAX рациональнее использовать ANY.

Агрегация по условию на результат агрегации на примере подсчета суммы за последнюю неделю

LOD - аналог подзапросов и для реализации вложенных констукций нужно мыслить как с подзапросами

Сбор суммы по данным за последнюю неделю, представленную в данных, требует нахождения последней недели и зачем сбор суммы по условию соответствия даты найденому максимуму.

То есть мы сначала ищем максимум, а затем агрегируем с условием:

SUM_IF(SUM([val] INCLUDE [week]),ANY([week] INCLUDE [week])=MAX([week] fixed))

Этот подзапрос собирает пары сумма-неделя: SUM([val] INCLUDE [week])

Этот подзапрос находит последнюю неделю: MAX([week] fixed)

Тут некоторая условность синтаксиса, чтобы с обеих сторон условия был одинаковый уровень агрегации: ANY([week] INCLUDE [week])

Нюансы AGO

Функции для работы с временными рядами AGO и AT_DATE аналогичны оконным функциям, работающими с окном из двух записей, включая текущую.

При этом, если LAG использует сосдение записи (или смешенные на заданный шаг), соседство которых определяется сортировкой, AGO и AT_DATE работают с записями, объединяемыми в окно по полю даты или даты и времени с заданным смещением в единицах даты и времени.

Для данных функций важно наличие данных с датой, соответствующей исходной со смещением.

То есть если смещение в минус 10 минут берется для записи с временной отметкой 2025-01-01 01:10:00, необходимо чтобы запись с временной меткой 2025-01-01 01:00:00 существовала, в противном случае результат будет NULL

Соответственно, запись с временной меткой 2025-01-01 01:00:00 не попадет в вычисления если нет записи с временной отметкой 2025-01-01 01:10:00.

Если данные распределены неравномерно по временным отметкам, например, события просиходят не каждый день, то для корректной работы AGO необходимо собирать (агрегировать) данные в "гарантированные" интервалы времени. Например, вместо посуточных данных, округлять дату до месяца и работать с месячными показателями.

В SQL AGO и AT_DATE реализуются окнами с ... ORDER BY dt RANGE ...

   Пример: sum(val) over (partition by city order by toDate(dt) RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)

LAG реализуются окнами с ... ORDER BY ... ROWS ...