При разработке отчетов на Системе компоновки данных (СКД) программисты часто сталкиваются с задачей, когда на разных уровнях группировок необходимо применять различные агрегатные функции к одному и тому же полю. Например, в самой детальной группировке нужно получить максимум от суммы документов, на уровне выше — просуммировать эти максимумы, а на самом верхнем уровне — найти минимальное или среднее значение среди полученных сумм.
В этой статье мы подробно разберем, как устроена логика вычисления ресурсов в СКД, почему стандартное суммирование иногда работает не так, как ожидается, и как правильно использовать мощную встроенную функцию ВычислитьВыражениеСГруппировкойМассив для решения задач любой сложности.
Для многих начинающих разработчиков становится открытием тот факт, что итоги по вышестоящим группировкам в СКД рассчитываются независимо от итогов по вложенным группировкам. Нам интуитивно кажется, что система должна просто сложить уже рассчитанные значения детальных записей, но СКД работает иначе.
Когда рассчитывается ресурс для вышестоящей группировки, СКД берет исходную выборку детальных записей, попадающих в эту группировку, и применяет к ним формулу ресурса «с нуля». Если у вас для ресурса написано Максимум(СуммаДокумента), то и на уровне детальной группировки, и на уровне общих итогов будет вычислен именно максимум из всей выборки, а не сумма локальных максимумов.
Для того чтобы заставить СКД агрегировать уже агрегированные данные, используются специальные функции, такие как ВычислитьВыражениеСГруппировкойМассив.
Рассмотрим классический сценарий. У нас есть иерархия группировок:
Задача: в группировке СсылкаВозврат получить МАКСИМУМ от суммы документа, а в группировке Ярлык вывести СУММУ этих максимумов.
Разберем по шагам, как настроить вкладку «Ресурсы» в схеме компоновки данных:
Шаг 1. Добавим поле ресурса для детальной группировки. В колонке «Путь к данным» выбираем наше поле (например, РегистраторСуммаДокумента). В выражении пишем:
Максимум(РегистраторСуммаДокумента)
В колонке «Рассчитывать по...» обязательно указываем конкретную группировку — СсылкаВозврат. Это указывает СКД, что данная простая формула применяется только на этом уровне иерархии.
Шаг 2. Добавим вторую строку для этого же ресурса. Это поле будет отвечать за расчет итогов на уровне группировки Ярлык и всех вышестоящих уровней (включая общие итоги). Пропишем следующее выражение:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(РегистраторСуммаДокумента)", "СсылкаВозврат"))
Колонку «Рассчитывать по...» для этой строки оставляем пустой. Если её заполнить, то расчет не поднимется на уровень общих итогов отчета. Оставив поле пустым, мы делаем формулу универсальной для всех уровней выше СсылкаВозврат.
Проанализируем, как работает эта формула. Функция ВычислитьВыражениеСГруппировкойМассив выполняет виртуальную группировку данных по полю СсылкаВозврат внутри текущего контекста (например, внутри конкретного «Ярлыка»). Для каждого элемента этой виртуальной группировки вычисляется максимум суммы документа. Результаты возвращаются в виде массива чисел. Затем внешняя функция Сумма складывает все элементы этого массива.
Теперь усложним задачу (сценарий из Сообщения 9 форума). Предположим, у нас три уровня группировки:
Требования к расчету ресурса РегистраторСуммаДокумента:
МАКСИМУМ(Сумма).СУММА от максимумов группировки СсылкаВозврат.МИНИМУМ от полученных сумм группировки Ярлык.Вопреки распространенному заблуждению, СКД отлично поддерживает вложенные («двухэтажные» и более) агрегатные функции на уровне ресурсов. Главное — правильно соблюсти синтаксис экранирования кавычек.
Настроим три строки ресурса на вкладке «Ресурсы»:
Выражение:
Максимум(РегистраторСуммаДокумента)
Рассчитывать по: СсылкаВозврат
Выражение:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(РегистраторСуммаДокумента)", "СсылкаВозврат"))
Рассчитывать по: Ярлык
Выражение содержит вложенную конструкцию. Обратите внимание на удвоение кавычек для внутренних параметров строкового типа:
Минимум(ВычислитьВыражениеСГруппировкойМассив("Сумма(ВычислитьВыражениеСГруппировкойМассив(""Максимум(РегистраторСуммаДокумента)"", ""СсылкаВозврат""))", "Ярлык"))
Рассчитывать по: Оставляем пустым (чтобы рассчитывалось и для Маршрута, и для Общих итогов).
При таком подходе СКД сначала сформирует массивы сумм для каждого «Ярлыка» внутри текущего «Маршрута», а затем функция Минимум выберет наименьшее значение из этого набора данных. Расчет будет абсолютно точным.
Хотя СКД успешно справляется с вложенными функциями в оперативной памяти, при больших объемах данных (десятки и сотни тысяч строк) формулы с ВычислитьВыражениеСГруппировкойМассив могут вызывать существенные задержки и повышенное потребление ресурсов сервера приложений.
Наиболее профессиональным решением будет перенос части агрегации на уровень SQL-запроса в схеме компоновки данных. Мы можем заранее рассчитать промежуточные итоги во временных таблицах.
Рассмотрим пример построения такого запроса:
// Шаг 1: Получаем максимумы на уровне СсылкаВозврат
ВЫБРАТЬ
Т.Маршрут КАК Маршрут,
Т.Ярлык КАК Ярлык,
Т.СсылкаВозврат КАК СсылкаВозврат,
МАКСИМУМ(Т.РегистраторСуммаДокумента) КАК СуммаМаксимум
ПОМЕСТИТЬ ВТ_МаксимумыСсылкаВозврат
ИЗ
ДокументыПродаж КАК Т
СГРУППИРОВАТЬ ПО
Т.Маршрут,
Т.Ярлык,
Т.СсылкаВозврат
;
// Шаг 2: Получаем суммы этих максимумов на уровне Ярлык
ВЫБРАТЬ
ВТ.Маршрут КАК Маршрут,
ВТ.Ярлык КАК Ярлык,
СУММА(ВТ.СуммаМаксимум) КАК СуммаПоЯрлыку
ПОМЕСТИТЬ ВТ_СуммыЯрлык
ИЗ
ВТ_МаксимумыСсылкаВозврат КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Маршрут,
ВТ.Ярлык
;
// Шаг 3: Соединяем исходные данные с агрегированными таблицами
ВЫБРАТЬ
ИсходныеДанные.Маршрут КАК Маршрут,
ИсходныеДанные.Ярлык КАК Ярлык,
ИсходныеДанные.СсылкаВозврат КАК СсылкаВозврат,
ИсходныеДанные.РегистраторСуммаДокумента КАК РегистраторСуммаДокумента,
ЕСТЬNULL(ВТ_Максимумы.СуммаМаксимум, 0) КАК Поле_Максимум,
ЕСТЬNULL(ВТ_Суммы.СуммаПоЯрлыку, 0) КАК Поле_Сумма
ИЗ
ДокументыПродаж КАК ИсходныеДанные
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_МаксимумыСсылкаВозврат КАК ВТ_Максимумы
ПО ИсходныеДанные.СсылкаВозврат = ВТ_Максимумы.СсылкаВозврат
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СуммыЯрлык КАК ВТ_Суммы
ПО ИсходныеДанные.Ярлык = ВТ_Суммы.Ярлык
Теперь в СКД нам больше не нужно писать громоздкие формулы с двойным вложением в ресурсах. Мы можем просто указать простые агрегатные функции для соответствующих полей на вкладке «Ресурсы»:
СсылкаВозврат: Максимум(Поле_Максимум)Ярлык: Максимум(Поле_Сумма) (так как сумма по ярлыку уже посчитана в запросе)Маршрут: Минимум(Поле_Сумма)Этот метод работает значительно быстрее, так как основная нагрузка по группировке данных ложится на СУБД, которая оптимизирует такие запросы на уровне индексов.
Если изменять запрос нежелательно (например, если источник данных — внешний набор данных или сложная иерархия объектов), можно упростить формулы в ресурсах с помощью «Вычисляемых полей».
Перейдем на вкладку «Вычисляемые поля» и добавим новое поле СуммаПоЯрлыку. В колонку «Выражение» запишем расчет промежуточного итога:
ВычислитьВыражениеСГруппировкойМассив("Максимум(РегистраторСуммаДокумента)", "СсылкаВозврат")
После этого на вкладке «Ресурсы» формула для группировки Маршрут станет гораздо более читаемой и простой в поддержке:
Минимум(ВычислитьВыражениеСГруппировкойМассив("Сумма(СуммаПоЯрлыку)", "Ярлык"))
Такой подход помогает избежать синтаксических ошибок, связанных с некорректным количеством скобок или кавычек при написании сложных многоуровневых выражений.
Для реализации нетривиальных математических расчетов (например, когда логика вычисления итогов завязана на условия, проверку прав или сложные финансовые формулы) можно передать массив значений во внешнюю функцию общего модуля 1С.
Общий модуль должен быть неглобальным, иметь установленные флаги «Сервер», «Вызов сервера» и содержать экспортную функцию. Например, создадим функцию в модуле РасчетыВывескиПереопределяемый:
Функция РассчитатьМинимумИзСумм(МассивЗначений) Экспорт
Если МассивЗначений.Количество() = 0 Тогда
Возврат 0;
КонецЕсли;
// Любая произвольная логика обработки данных
МинимальноеЗначение = Неопределено;
Для Каждого ТекущееЗначение Из МассивЗначений Цикл
Если МинимальноеЗначение = Неопределено Или ТекущееЗначение < МинимальноеЗначение Тогда
МинимальноеЗначение = ТекущееЗначение;
КонецЕсли;
КонецЦикла;
Возврат МинимальноеЗначение;
КонецФункции
В выражении ресурса СКД мы можем вызвать эту функцию, передав результат работы системного метода СКД в качестве параметра:
РасчетыВывескиПереопределяемый.РассчитатьМинимумИзСумм(ВычислитьВыражениеСГруппировкойМассив("Сумма(ВычислитьВыражениеСГруппировкойМассив(""Максимум(РегистраторСуммаДокумента)"", ""СсылкаВозврат""))", "Ярлык"))
Это обеспечивает максимальную гибкость алгоритмов и позволяет реализовывать расчеты, которые невозможно описать стандартным языком выражений СКД.
Мы детально разобрали особенности расчета сложных ресурсов в СКД. При выборе метода реализации руководствуйтесь следующими правилами:
ВычислитьВыражениеСГруппировкойМассив на вкладке «Ресурсы». Это быстро настраивается и не требует изменения структуры запросов.