Большинству агрегатных функций в Даталенс может быть задан уровень детализации
Структура описания агрегации с уровнем детализации:
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 ...