При работе с Системой компоновки данных (СКД) разработчики часто сталкиваются с задачей, когда стандартная агрегатная функция Среднее() выдает не тот результат, который ожидается. Проблема заключается в том, что по умолчанию СКД рассчитывает среднее значение по всем детальным записям (строкам таблицы), попавшим в группировку. Однако в бизнес-отчетах часто требуется найти среднее от сумм. Например, нам нужно узнать среднее количество проданных товаров на одного сотрудника в рамках региона. Если мы просто укажем Среднее(Количество), система сложит все продажи из чеков и разделит на количество чеков, а не на количество сотрудников. Разберем, как решить эту задачу правильно.
Проанализируем ситуацию на примере. Допустим, у нас есть Регион 1, в котором работают два сотрудника: Иванов и Петров. Иванов совершил 3 продажи по 1 бублику, а Петров — 1 продажу, но сразу 5 бубликов.
Общая сумма по региону — 8 бубликов. Сотрудников двое. Правильное среднее по сотрудникам: 8 / 2 = 4. Однако, если СКД будет считать среднее по детальным записям, она увидит четыре записи (1, 1, 1, 5) и выдаст результат: (1+1+1+5) / 4 = 2. Чтобы избежать этой ошибки, нам нужно заставить систему сначала рассчитать суммы по сотрудникам, а затем вычислить среднее от этих сумм.
Это самый мощный и гибкий механизм в СКД для подобных расчетов. Рассмотрим, как составить формулу для ресурса. Вместо стандартного поля мы пропишем выражение, которое сначала соберет суммы по каждой вложенной группировке в массив, а затем извлечет среднее из этого массива. При необходимости можно также использовать готовые шаблоны для расчета дельты, нарастающего итога и других показателей в СКД.
Перейдем на вкладку «Ресурсы» и для поля Бублики в колонке «Выражение» укажем следующее:
Среднее(ВычислитьВыражениеСГруппировкойМассив("Сумма(Бублики)", "Сотрудник"))
Разберем по шагам, что делает эта формула:
Сумма(Бублики) — это агрегат, который мы хотим рассчитать для каждого элемента вложенной группы."Сотрудник" — это имя группировки, по которой будут собраны суммы. Очень важно, чтобы в настройках отчета у группировки по сотруднику в поле «Имя» было указано именно Сотрудник.ВычислитьВыражениеСГруппировкойМассив — функция «обходит» всех сотрудников внутри текущего региона, считает сумму для каждого и складывает результаты в виртуальный массив. Для нашего примера получится массив [3, 5].Среднее(...) — внешняя функция вычисляет среднее арифметическое элементов полученного массива: (3 + 5) / 2 = 4.СКД позволяет задавать разные формулы расчета одного и того же ресурса для разных уровней отчета. Это полезно, когда на уровне сотрудника нам нужна просто сумма, а на уровне региона и общего итога — среднее значение. Проанализируем, как это настроить в таблице ресурсов:
Количество. В выражении напишем Сумма(Количество). В колонке «Рассчитывать по...» выберем только группировку Сотрудник.Количество еще раз. В выражении напишем нашу сложную формулу с массивом:
Среднее(ВычислитьВыражениеСГруппировкойМассив("Сумма(Количество)", "Сотрудник"))
В колонке «Рассчитывать по...» выберем Регион и Общий итог.Таким образом, система будет использовать простую сумму для нижнего уровня и сложный расчет для верхних уровней иерархии. Для детального изучения этого подхода можно использовать руководство по настройке различных формул расчета одного ресурса для разных уровней группировок.
Если структура отчета сложная или динамическая, можно подготовить данные заранее с помощью языка запросов. Рассмотрим пример формирования временной таблицы, где данные уже агрегированы до нужного уровня. Это избавит нас от сложных формул в самой схеме компоновки.
Посмотрим на пример запроса, который можно отладить через консоль запросов 8.3 (удобно через консоль СКД для отладки выражений и компоновки), чтобы эмулировать выборку данных для таких расчетов:
ВЫБРАТЬ
"Регион 1" КАК Регион,
"Иванов" КАК Сотрудник,
3 КАК Продажи
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Регион 1",
"Петров",
5
Если мы подадим в СКД уже сгруппированные данные по сотрудникам, то стандартная функция Среднее(Продажи) отработает корректно даже на уровне региона, так как «детальной записью» для СКД теперь является общая сумма продаж сотрудника, а не каждый отдельный чек.
Также стоит помнить, что в современных версиях платформы (начиная с 8.3.15) стали доступны элементы оконных функций. Выясним, как их можно применить. Хотя полноценного AVG() OVER() нет, мы можем использовать кумулятивные итоги или количество записей для формирования знаменателя среднего значения непосредственно в запросе.
Проанализируем несколько критических моментов, которые могут исказить результат:
Среднее() игнорирует значения NULL. Если сотрудник ничего не продал и записи по нему нет в выборке, он не попадет в знаменатель, и среднее будет завышено. Чтобы учесть «нулевых» сотрудников, используйте ЕСТЬNULL(Поле, 0) в запросе или левое соединение со справочником сотрудников.ВычислитьВыражениеСГруппировкойМассив всегда явно задавайте имена группировок в настройках (вкладка «Дополнительные настройки» -> «Имя»). Если оставить имя пустым, формула может перестать работать при изменении структуры отчета пользователем.ВычислитьВыражениеСГруппировкойТаблицаЗначений.Используя данные методы, мы можем гибко управлять расчетами в СКД, создавая универсальные отчеты, которые точно соответствуют требованиям управленческого учета (есть готовая Low-code платформа для построения управленческой отчетности) и корректно интерпретируют иерархические данные.