Как рассчитать среднее значение у ресурса в СКД с фильтрацией пустых строк

← На главную

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

Проблема учета нулевых значений в агрегатных функциях

Разберем типичную ситуацию: у нас есть данные по трем товарам. По первому товару конверсия 100%, по второму данных нет (продажи и поступления равны 0), по третьему — 50%. Если мы просто попросим СКД посчитать среднее значение, система сложит 100 + 0 + 50 и разделит на 3, получив 50%. Однако бизнес-логика часто требует игнорировать строки без активности, то есть результат должен быть (100 + 50) / 2 = 75%.

Основная причина некорректного расчета кроется в том, как стандартные функции агрегации работают с типами данных:

Значение 0 — воспринимается как полноправное число и учитывается в знаменателе при расчете среднего.
Значение NULL — полностью игнорируется функцией Среднее(). Знаменатель (количество записей) автоматически уменьшается на число строк, содержащих это значение.

Способ 1. Использование NULL на уровне запроса

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

Рассмотрим пример кода в тексте запроса:


ВЫБОР
    КОГДА Продажи = 0 И Поступления = 0 ТОГДА NULL
    КОГДА Поступления = 0 ТОГДА 0 // Исключаем деление на 0, если продажи были без поступлений
    ИНАЧЕ Продажи / Поступления * 100
КОНЕЦ КАК Конверсия

В этом случае, когда СКД применит функцию Среднее(Конверсия) на вкладке "Ресурсы", строки со значением NULL просто не будут учитываться, и вы получите желаемые 75%.

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

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

Перейдем на закладку Вычисляемые поля.
Добавим поле Конверсия.
В выражении пропишем условие проверки делителя на ноль и исключение пустых строк:


ВЫБОР 
    КОГДА Поступления = 0 И Продажи = 0 ТОГДА NULL 
    КОГДА Поступления = 0 ТОГДА 0 
    ИНАЧЕ Продажи / Поступления * 100 
КОНЕЦ

Затем на закладке Ресурсы для этого поля выберем функцию Среднее(Конверсия). Важно помнить: если вы хотите получить именно среднее арифметическое по строкам, используйте Среднее(). Если же вам нужно "взвешенное" среднее (отношение общей суммы продаж к общей сумме поступлений), формула будет иной:


ВЫБОР 
    КОГДА СУММА(Поступления) = 0 ТОГДА 0 
    ИНАЧЕ СУММА(Продажи) / СУММА(Поступления) * 100 
КОНЕЦ

Способ 3. Расчет "Среднего от среднего" по группировкам

Бывают ситуации, когда заказчик просит рассчитать итог не по детальным записям, а как среднее значение итогов по группам (например, средняя конверсия по ответственным менеджерам). В этом случае стандартная функция Среднее() по полю даст неверный результат, так как она будет считать среднее по всем строкам чеков, а не по итоговым цифрам менеджеров.

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

Выясним, как должна выглядеть формула в ресурсах для общего итога:


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

Что здесь происходит:

СКД сначала рассчитывает показатель для каждого Ответственного.
Результаты помещаются в массив (например: [100, 50, 75]).
Внешняя функция Среднее() вычисляет среднее арифметическое из элементов этого массива.

Способ 4. Ручное управление знаменателем

Если по каким-то причинам использование NULL невозможно, мы можем вручную вычислить среднее значение, определив количество "валидных" строк. Посмотрим на пример выражения в ресурсах:


СУММА(Конверсия) / 
ВЫБОР 
    КОГДА КОЛИЧЕСТВО(ВЫБОР КОГДА Продажи > 0 ИЛИ Поступления > 0 ТОГДА Товар КОНЕЦ) = 0 
    ТОГДА 1 
    ИНАЧЕ КОЛИЧЕСТВО(ВЫБОР КОГДА Продажи > 0 ИЛИ Поступления > 0 ТОГДА Товар КОНЕЦ) 
КОНЕЦ

В этой формуле мы принудительно считаем количество только тех товаров, по которым была активность, и делим на это число общую сумму показателей. Это дает полный контроль над математикой расчета.

Важные нюансы при работе с иерархией

При использовании иерархических справочников (например, группы номенклатуры) расчет среднего может выдавать странные результаты. Это происходит потому, что СКД включает в расчет и родительские группы, и дочерние элементы. Чтобы избежать "удвоения", при использовании функций типа ВычислитьВыражениеСГруппировкойМассив рекомендуется четко указывать уровни группировки или использовать отборы по типам записей.

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