При разработке отчетов на схеме компоновки данных (СКД) разработчики часто сталкиваются с задачей так называемого двойного агрегирования. Типичный пример: на уровне детальных записей или определенной группировки (например, по документам) нам необходимо получить максимальное значение показателя, а на уровне вышестоящих группировок или в общих итогах — сложить (просуммировать) эти полученные максимумы.
Такая необходимость часто возникает при соединении таблиц с разной периодичностью и детализацией. Например, когда мы соединяем в одном запросе планы продаж за месяц с планами сдачи по декадам. Из-за разной детализации строки с планом продаж за месяц начинают дублироваться (размножаться) под каждую декадную запись. В итоге обычное суммирование дает завышенные показатели, и нам приходится прибегать к вычислению максимума по документу с последующим сложением этих максимумов в итогах.
В этой статье мы подробно разберем, как решить эту задачу в СКД несколькими способами: от использования специализированных функций языка выражений СКД до правильного проектирования схемы компоновки данных.
Давайте проанализируем ситуацию на примере исходного запроса. Допустим, у нас есть две временные таблицы:
ТЧ_Товары — содержит плановые объемы продаж номенклатуры с детализацией по месяцам (поле ПланПродажКоличество).ТЧ_ПланСдачи — содержит плановые объемы сдачи той же номенклатуры, но уже с детализацией по декадам месяца (поле ПланСдачиКоличество).Когда мы соединяем эти таблицы по полям Документ, Склад, Номенклатура и Месяц, мы получаем следующий результат:
НОМЕНКЛАТУРА МЕСЯЦ ДЕКАДА ПланПродажКоличество ПланСдачиКоличество ДОКУМЕНТ
Номенклатура1 01.04.2020 01.04.2020 24 4 Документ1
Номенклатура1 01.04.2020 11.04.2020 24 5 Документ1
Номенклатура1 01.04.2020 21.04.2020 24 6 Документ1
Как мы видим, из-за наличия трех декад в апреле месячное значение плана продаж (24) продублировалось трижды. Если мы просто применим функцию Сумма(ПланПродажКоличество), то в итогах по документу мы получим 72 вместо реальных 24. Чтобы решить эту проблему, нам нужно брать Максимум(ПланПродажКоличество) в рамках группировки по документу, а в общих итогах — суммировать эти максимумы по всем документам.
Рассмотрим по шагам три основных способа решения этой задачи.
Самый быстрый способ решить задачу «на лету» без изменения структуры запроса — это использование системной функции СКД ВычислитьВыражениеСГруппировкойМассив на вкладке Ресурсы.
Эта функция позволяет СКД обходить записи в указанном разрезе группировок, вычислять для каждой уникальной комбинации заданное выражение, помещать результаты во временный массив в оперативной памяти, а затем применять к этому массиву внешнюю агрегатную функцию (в нашем случае — Сумма).
Выясним, как правильно настроить ресурсы в схеме компоновки данных:
ПланПродажКоличество в качестве ресурса первый раз. В колонке Выражение укажем:
Максимум(ПланПродажКоличество)
Документ.
Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(ПланПродажКоличество)", "Номенклатура, Склад, ПланПродажМесяц, Документ"))
Общий итог.
Критически важный нюанс: Обратите внимание на второй параметр функции ВычислитьВыражениеСГруппировкойМассив. Если в отчете выше группировки Документ присутствуют другие родительские группировки (например, Номенклатура, Склад, ПланПродажМесяц), их обязательно нужно перечислить через запятую в параметре полей группировки. Если указать только "Документ", СКД выполнит сворачивание массива без учета иерархии и контекста вышестоящих группировок, что приведет к неверному расчету итогов.
Хотя решение с использованием ВычислитьВыражениеСГруппировкойМассив является рабочим, оно имеет существенный недостаток — низкую производительность на больших объемах данных. Создание временных массивов в оперативной памяти при формировании отчетов с десятками тысяч строк может привести к сильному замедлению работы и даже к ошибкам нехватки памяти.
Наиболее правильным и производительным архитектурным решением является разделение одного большого запроса на два отдельных набора данных с последующей настройкой связей между ними средствами СКД.
Выполним это по шагам:
ТЧ_Товары.ТЧ_ПланСдачи.НаборДанных1НаборДанных2Документ, Склад, Номенклатура и Месяц из обоих наборов.ПланПродажКоличество укажем стандартное выражение:
Сумма(ПланПродажКоличество)
Почему это работает правильно без сложных формул? В платформу 1С встроен оптимизированный механизм расчета ресурсов при связях наборов данных. Платформа строго соблюдает правило: запись из ведущего набора данных (в нашем случае — плановое количество продаж за месяц из Набора 1) учитывается при суммировании итогов ровно один раз, независимо от того, сколько подчиненных записей по декадам сопоставлено ей из Набора 2. Это избавляет нас от необходимости использовать ресурсоемкие функции работы с массивами и гарантирует мгновенный расчет итогов.
Если по каким-то причинам вы хотите сохранить один набор данных, проблему дублирования строк можно эффективно обойти за счет оптимизации структуры самого запроса. Вместо соединения таблиц с разным уровнем детализации "напрямую", мы можем предварительно сгруппировать подчиненную таблицу до уровня ведущей.
Рассмотрим, как изменить логику соединения таблиц в запросе:
// Сначала подготавливаем таблицу планов продаж за месяц
ВЫБРАТЬ
ПланПродажТовары.Ссылка КАК Документ,
ПланПродажТовары.Ссылка.Склад КАК Склад,
ПланПродажТовары.Номенклатура КАК Номенклатура,
ПланПродажТовары.ДатаОтгрузки КАК Месяц,
СУММА(ПланПродажТовары.Количество) КАК ПланПродажКоличество
ПОМЕСТИТЬ ТЧ_Товары
...
СГРУППИРОВАТЬ ПО ... ;
// Сразу агрегируем декадные данные до уровня месяца в рамках одного запроса
ВЫБРАТЬ
ПланСдачи.Ссылка КАК Документ,
ПланСдачи.Ссылка.Склад КАК Склад,
ПланСдачи.Номенклатура КАК Номенклатура,
НАЧАЛОПЕРИОДА(ПланСдачи.ДатаСдачи, МЕСЯЦ) КАК Месяц,
СУММА(ПланСдачи.Количество) КАК ПланСдачиКоличествоМесяц
ПОМЕСТИТЬ ТЧ_ПланСдачиСвернуто
ИЗ
Документ.ПланПродаж.БП_ПланСдачи КАК ПланСдачи
...
СГРУППИРОВАТЬ ПО
ПланСдачи.Ссылка,
ПланСдачи.Ссылка.Склад,
ПланСдачи.Номенклатура,
НАЧАЛОПЕРИОДА(ПланСдачи.ДатаСдачи, МЕСЯЦ);
// Теперь соединяем таблицы строго "один к одному"
ВЫБРАТЬ
ТЧ_Товары.Документ,
ТЧ_Товары.Склад,
ТЧ_Товары.Номенклатура,
ТЧ_Товары.Месяц КАК ПланПродажМесяц,
ТЧ_Товары.ПланПродажКоличество,
ТЧ_ПланСдачиСвернуто.ПланСдачиКоличествоМесяц
ИЗ
ТЧ_Товары КАК ТЧ_Товары
ЛЕВОЕ СОЕДИНЕНИЕ ТЧ_ПланСдачиСвернуто КАК ТЧ_ПланСдачиСвернуто
ПО ТЧ_Товары.Документ = ТЧ_ПланСдачиСвернуто.Документ
И ТЧ_Товары.Склад = ТЧ_ПланСдачиСвернуто.Склад
И ТЧ_Товары.Номенклатура = ТЧ_ПланСдачиСвернуто.Номенклатура
И ТЧ_Товары.Месяц = ТЧ_ПланСдачиСвернуто.Месяц
При таком подходе в результирующем наборе данных СКД строки перестанут дублироваться. Каждому документу и номенклатуре в конкретном месяце будет соответствовать ровно одна строка. Соответственно, для настройки ресурса ПланПродажКоличество нам больше не понадобятся сложные манипуляции и вычисление максимумов. Мы сможем использовать стандартную агрегатную функцию Сумма(ПланПродажКоличество) как на уровне детальных записей группировки, так и в общих итогах отчета.
Подведем итог нашему анализу:
ВычислитьВыражениеСГруппировкойМассив, не забывая перечислить все вышестоящие группировки во втором параметре.