Как рассчитать сумму максимальных значений по группировкам в ресурсах СКД?

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

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

Постановка задачи и анализ ситуации

Представим структуру данных, где фигурируют заказы, реализации (РТУ) и количество товаров. Из-за особенностей связей в запросе одна и та же реализация может дублироваться или содержать несколько строк, которые при определенных условиях нужно схлопнуть по максимуму. Рассмотрим исходную таблицу:

1. Заказ1 — РТУ 1 — Количество: 2
2. Заказ1 — РТУ 2 — Количество: 3
3. Заказ2 — РТУ 1 — Количество: 4
4. Заказ2 — РТУ 2 — Количество: 5
5. Заказ3 — РТУ 3 — Количество: 9

Наша цель — получить в ресурсе значение, которое рассчитывается по следующему алгоритму:

1. Для каждой уникальной реализации (РТУ) найти максимальное количество среди всех связанных заказов.
2. Сложить полученные максимальные значения.

Проанализируем расчет: для РТУ 1 максимум между 2 и 4 равен 4. Для РТУ 2 максимум между 3 и 5 равен 5. Для РТУ 3 значение равно 9. Итоговая сумма: 4 + 5 + 9 = 18. Обычная функция Сумма(Количество) выдала бы 23, что было бы неверно в контексте данной бизнес-задачи.

Решение через функцию ВычислитьВыражениеСГруппировкойМассив

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

Разберем синтаксис решения, который применил автор темы:

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

Рассмотрим подробнее, что происходит внутри этого выражения:

• "Максимум(Количество)" — это первое выражение, которое вычисляется для каждой виртуальной группировки. В нашем случае мы ищем максимальное значение поля Количество.
• "РТУ" — это поле, по которому система произведет временную группировку данных в памяти сервера 1С перед расчетом.
• ВычислитьВыражениеСГруппировкойМассив — собирает все полученные максимумы в массив: [4, 5, 9].
• Сумма(...) — внешняя функция, которая складывает элементы полученного массива, давая итоговый результат 18.

Пошаговое внедрение в схему компоновки данных

Для того чтобы применить это решение в вашем отчете, выполните следующие действия:

1. Откройте схему компоновки данных и перейдите на закладку «Ресурсы».
2. Выберите поле, которое должно стать ресурсом (например, Количество).
3. В колонке «Выражение» вставьте конструкцию:
   Скопировать код

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

4. Обратите внимание на колонку «Рассчитывать по...». Если вы хотите, чтобы этот сложный расчет применялся только в общих итогах или в определенных группировках (например, по Контрагенту), укажите эти группировки. Если оставить поле пустым, формула будет применяться ко всем уровням иерархии отчета.

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

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

Модифицируем наше выражение для повышения отказоустойчивости:

Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(ЕстьNULL(Количество, 0))", "ДокументРТУ"))

Также выясним причину возможных проблем с производительностью. Функция ВычислитьВыражениеСГруппировкойМассив выполняется не в SQL-запросе, а на стороне сервера приложений 1С. Система получает детальные записи и начинает строить внутренние таблицы в памяти. Если уникальных значений в группировке (в нашем примере — РТУ) десятки тысяч, формирование отчета может потребовать значительного объема оперативной памяти и времени. В таких случаях стоит рассмотреть возможность предварительной группировки данных в самом тексте запроса.

Расширенные возможности: использование условий

Функция ВычислитьВыражениеСГруппировкойМассив поддерживает дополнительные параметры, такие как отбор. Это позволяет вычислять «сумму максимумов» только для определенных строк, не накладывая общий фильтр на весь отчет.

Рассмотрим пример, где нам нужны максимумы только по проведенным документам:

Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(Количество)", "ДокументРТУ", "ДокументРТУ.Проведен = Истина"))

Здесь третий параметр выступает в роли фильтра, который применяется на этапе формирования массива значений. Это избавляет от необходимости создавать сложные вычисляемые поля с условиями ВЫБОР КОГДА....

Альтернатива: ВычислитьВыражениеСГруппировкойТаблицаЗначений

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

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

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