Как исправить ошибку «Неверные параметры "AVG"» в отчете СКД

← На главную

При разработке отчетов на Системе компоновки данных (СКД) программисты часто сталкиваются с непредвиденным поведением платформы при расчете итоговых значений. Одной из наиболее коварных и часто встречающихся ошибок является сбой при формировании отчета с сообщением: «Ошибка в выражении по причине: Неверные параметры "AVG"» (в русскоязычной локализации платформы — «Неверные параметры "СРЕДНЕЕ"»).

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

Анализ исходной ситуации и запроса

Рассмотрим ситуацию, с которой столкнулся автор темы. В его отчете вычисляется коэффициент использования материала (КИМ) как отношение площади изделия к площади материала, выраженное в процентах. Для предотвращения фатальной ошибки деления на ноль в запросе была использована классическая конструкция ВЫБОР:


ВЫБРАТЬ
    ПроизводствоТабличнаяЧасть1.Ссылка.Проведен,
    ПроизводствоТабличнаяЧасть1.Ссылка.Дата,
    ПроизводствоТабличнаяЧасть1.Ссылка.Автор,
    ПроизводствоТабличнаяЧасть1.ПлощадьИзделия,
    ПроизводствоТабличнаяЧасть1.ПлощадьМатериала,
    ПроизводствоТабличнаяЧасть1.КолФотоПечать,
    ПроизводствоТабличнаяЧасть1.Рулон,
    ПроизводствоТабличнаяЧасть1.Ссылка,
    ПроизводствоТабличнаяЧасть1.Код,
    ВЫБОР
        КОГДА ПроизводствоТабличнаяЧасть1.ПлощадьМатериала = 0
            ТОГДА ""
        ИНАЧЕ ПроизводствоТабличнаяЧасть1.ПлощадьИзделия / ПроизводствоТабличнаяЧасть1.ПлощадьМатериала * 100
    КОНЕЦ КАК КИМ
ИЗ
    Документ.Производство.ТабличнаяЧасть1 КАК ПроизводствоТабличнаяЧасть1

При попытке вывести это поле в ресурсы отчета со стандартной функцией расчета Среднее(КИМ) (на уровне СУБД это функция AVG) система аварийно завершает работу. Выясним причину такого поведения.

Причина №1: Несовместимость типов данных в конструкции ВЫБОР

Главная ошибка в приведенном запросе кроется в ветке ТОГДА "".

Когда СУБД анализирует конструкцию ВЫБОР, она пытается привести результат всего выражения к единому типу данных или к совместимому составному типу:

Если условие выполняется (площадь материала равна нулю), запрос возвращает пустую строку "" (тип Строка).
Если условие не выполняется, вычисляется математическая формула, результатом которой является число (тип Число).

В итоге поле КИМ получает составной тип данных Число, Строка. Когда СКД пытается рассчитать среднее значение (функция AVG) по группировкам или общим итогам отчета, она отправляет запрос к СУБД. На уровне базы данных агрегатная функция AVG предназначена исключительно для работы с числовыми значениями. Сталкиваясь со строковым значением "" в очередной строке выборки, СУБД возвращает ошибку неверных параметров, так как не умеет складывать строки и делить их на количество.

Решение: Использование NULL вместо пустой строки

Самым простым и технически правильным решением является замена пустой строки "" на значение NULL. Изменим конструкцию в запросе следующим образом:


ВЫБОР
    КОГДА ПроизводствоТабличнаяЧасть1.ПлощадьМатериала = 0
        ТОГДА NULL
    ИНАЧЕ ПроизводствоТабличнаяЧасть1.ПлощадьИзделия / ПроизводствоТабличнаяЧасть1.ПлощадьМатериала * 100
КОНЕЦ КАК КИМ

Разберем подробнее, почему использование NULL решает проблему:

Игнорирование агрегатными функциями: По стандарту SQL и правилам работы СКД любые агрегатные функции (такие как СУММА, СРЕДНЕЕ, МИНИМУМ, МАКСИМУМ) полностью игнорируют значения NULL при расчете. Строка со значением NULL просто отбрасывается из вычислений, не вызывая конфликта типов данных.
Сохранение типа поля: Поле сохраняет числовой тип данных для всех строк, где значение определено.

Математическая разница между NULL и 0

В процессе обсуждения на форуме прозвучало предложение заменить пустую строку на числовой ноль (ТОГДА 0). Это действительно решает проблему с технической ошибкой, так как тип поля становится строго числовым. Однако с точки зрения математики и корректности бизнес-отчетов такое решение таит в себе серьезную опасность.

Проанализируем простейший пример. Допустим, у нас есть три строки в отчете:

Строка 1: КИМ = 50%
Строка 2: КИМ = 100%
Строка 3: ПлощадьМатериала = 0 (деление невозможно)

Посмотрим, как изменится расчет среднего значения в зависимости от нашего решения:

Вариант с использованием 0: В расчете участвуют значения 50, 100 и 0. Среднее значение рассчитывается как: (50 + 100 + 0) / 3 = 50%. Результат сильно искажен в меньшую сторону, так как строка без расхода материала необоснованно потянула статистику вниз.
Вариант с использованием NULL: Значение NULL в третьей строке игнорируется. В расчете участвуют только первая и вторая строки. Среднее значение рассчитывается как: (50 + 100) / 2 = 75%. Это математически верный результат, отражающий реальную картину по тем строкам, где расчет был возможен.

Таким образом, использование NULL — это единственный способ получить достоверные средние показатели при наличии пустых или неопределенных значений.

Альтернативный расчет ресурса (взвешенное среднее)

Зачастую расчет простого среднего значения по строкам детальных записей не дает нужного бизнес-результата. В нашем примере среднее значение КИМ по всем строкам может не совпадать с общим КИМ по всему производству (который должен рассчитываться как отношение суммарной площади всех изделий к суммарной площади всех материалов).

Для реализации этой логики правильнее рассчитывать ресурс в СКД не как среднее от готового коэффициента, а по формуле на вкладке «Ресурсы»:


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

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

Другие частые причины ошибки «Неверные параметры "AVG"»

Помимо деления на ноль с возвратом строки, в практике разработчиков 1С встречаются еще две типичные ситуации, приводящие к аналогичному сбою.

А. Использование функции Формат() в вычисляемых полях СКД

Иногда разработчики пытаются округлить или красиво оформить число непосредственно в выражении вычисляемого поля, используя функцию Формат(), например: Формат(Цена, "ЧДЦ=2").

Функция Формат() преобразует числовое значение в строку. Если далее для этого поля на вкладке «Ресурсы» указать расчет Среднее(МоеПоле), СКД выдаст ошибку «Неверные параметры "AVG"», так как попытается рассчитать среднее от отформатированных строковых представлений чисел.

Правильное решение: Оставляйте вычисляемое поле чисто числовым. Для изменения внешнего вида числа перейдите в настройки СКД на вкладку «Наборы данных», выберите ваше поле и настройте его оформление через колонку «Оформление» -> параметр «Формат». Если требуется математическое округление для последующих расчетов, используйте функцию Окр(Значение, 2).

Б. Попытка расчета среднего от ссылочных полей

Попытка перетащить в ресурсы поле, содержащее ссылку на справочник или документ (например, Среднее(Номенклатура)), также приведет к ошибке СУБД. Ссылка представляет собой уникальный идентификатор (GUID) и не может быть усреднена математически.

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