Как рассчитать среднее значение по уровням группировок в СКД

При работе с Системой компоновки данных (СКД) разработчики часто сталкиваются с задачей, когда стандартная агрегатная функция Среднее() выдает не тот результат, который ожидается. Проблема заключается в том, что по умолчанию СКД рассчитывает среднее значение по всем детальным записям (строкам таблицы), попавшим в группировку. Однако в бизнес-отчетах часто требуется найти среднее от сумм. Например, нам нужно узнать среднее количество проданных товаров на одного сотрудника в рамках региона. Если мы просто укажем Среднее(Количество), система сложит все продажи из чеков и разделит на количество чеков, а не на количество сотрудников. Разберем, как решить эту задачу правильно.

Проблема «плоского» расчета среднего

Проанализируем ситуацию на примере. Допустим, у нас есть Регион 1, в котором работают два сотрудника: Иванов и Петров. Иванов совершил 3 продажи по 1 бублику, а Петров — 1 продажу, но сразу 5 бубликов.

1. Иванов: 1+1+1 = 3 бублика.
2. Петров: 5 бубликов.

Общая сумма по региону — 8 бубликов. Сотрудников двое. Правильное среднее по сотрудникам: 8 / 2 = 4. Однако, если СКД будет считать среднее по детальным записям, она увидит четыре записи (1, 1, 1, 5) и выдаст результат: (1+1+1+5) / 4 = 2. Чтобы избежать этой ошибки, нам нужно заставить систему сначала рассчитать суммы по сотрудникам, а затем вычислить среднее от этих сумм.

Способ 1. Использование функции ВычислитьВыражениеСГруппировкойМассив

Это самый мощный и гибкий механизм в СКД для подобных расчетов. Рассмотрим, как составить формулу для ресурса. Вместо стандартного поля мы пропишем выражение, которое сначала соберет суммы по каждой вложенной группировке в массив, а затем извлечет среднее из этого массива.

Перейдем на вкладку «Ресурсы» и для поля Бублики в колонке «Выражение» укажем следующее:

Среднее(ВычислитьВыражениеСГруппировкойМассив("Сумма(Бублики)", "Сотрудник"))

Разберем по шагам, что делает эта формула:

1. Сумма(Бублики) — это агрегат, который мы хотим рассчитать для каждого элемента вложенной группы.
2. "Сотрудник" — это имя группировки, по которой будут собраны суммы. Очень важно, чтобы в настройках отчета у группировки по сотруднику в поле «Имя» было указано именно Сотрудник.
3. ВычислитьВыражениеСГруппировкойМассив — функция «обходит» всех сотрудников внутри текущего региона, считает сумму для каждого и складывает результаты в виртуальный массив. Для нашего примера получится массив [3, 5].
4. Среднее(...) — внешняя функция вычисляет среднее арифметическое элементов полученного массива: (3 + 5) / 2 = 4.

Способ 2. Настройка расчета ресурса по конкретным группировкам

СКД позволяет задавать разные формулы расчета одного и того же ресурса для разных уровней отчета. Это полезно, когда на уровне сотрудника нам нужна просто сумма, а на уровне региона и общего итога — среднее значение. Проанализируем, как это настроить в таблице ресурсов:

1. Добавим ресурс Количество. В выражении напишем Сумма(Количество). В колонке «Рассчитывать по...» выберем только группировку Сотрудник.
2. Добавим это же поле Количество еще раз. В выражении напишем нашу сложную формулу с массивом:
   Скопировать код

   
   Среднее(ВычислитьВыражениеСГруппировкойМассив("Сумма(Количество)", "Сотрудник"))
   

   В колонке «Рассчитывать по...» выберем Регион и Общий итог.

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

Способ 3. Подготовка данных в запросе

Если структура отчета сложная или динамическая, можно подготовить данные заранее с помощью языка запросов. Рассмотрим пример формирования временной таблицы, где данные уже агрегированы до нужного уровня. Это избавит нас от сложных формул в самой схеме компоновки.

Посмотрим на пример запроса, который можно отладить через консоль запросов 8.3 (удобно через консоль СКД для отладки выражений и компоновки), чтобы эмулировать выборку данных для таких расчетов:

ВЫБРАТЬ
    "Регион 1" КАК Регион,
    "Иванов" КАК Сотрудник,
    3 КАК Продажи
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    "Регион 1",
    "Петров",
    5

Если мы подадим в СКД уже сгруппированные данные по сотрудникам, то стандартная функция Среднее(Продажи) отработает корректно даже на уровне региона, так как «детальной записью» для СКД теперь является общая сумма продаж сотрудника, а не каждый отдельный чек.

Также стоит помнить, что в современных версиях платформы (начиная с 8.3.15) стали доступны элементы оконных функций. Выясним, как их можно применить. Хотя полноценного AVG() OVER() нет, мы можем использовать кумулятивные итоги или количество записей для формирования знаменателя среднего значения непосредственно в запросе.

Важные нюансы при расчете среднего

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

• Обработка NULL: Помните, что функция Среднее() игнорирует значения NULL. Если сотрудник ничего не продал и записи по нему нет в выборке, он не попадет в знаменатель, и среднее будет завышено. Чтобы учесть «нулевых» сотрудников, используйте ЕСТЬNULL(Поле, 0) в запросе или левое соединение со справочником сотрудников.
• Имена группировок: При использовании ВычислитьВыражениеСГруппировкойМассив всегда явно задавайте имена группировок в настройках (вкладка «Дополнительные настройки» -> «Имя»). Если оставить имя пустым, формула может перестать работать при изменении структуры отчета пользователем.
• Общий итог: Для корректного расчета в строке «Общий итог» убедитесь, что формула ресурса учитывает все необходимые уровни. Иногда требуется вложенность функций, например, среднее от средних, что реализуется через ВычислитьВыражениеСГруппировкойТаблицаЗначений.

Используя данные методы, мы можем гибко управлять расчетами в СКД, создавая универсальные отчеты, которые точно соответствуют требованиям управленческого учета (есть готовая Low-code платформа для построения управленческой отчетности) и корректно интерпретируют иерархические данные.