Почему СКД неправильно рассчитывает агрегатные функции во временных таблицах при группировке и как это исправить?

← На главную

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

Разберем, почему возникает такое расхождение и какими способами можно получить правильные результаты.

Анализ проблемы: как оптимизатор СКД изменяет исходный запрос

Рассмотрим типовой сценарий. В первой временной таблице формируются данные с группировкой по партнерам и рассчитывается количество различных позиций:


ВЫБРАТЬ
    РеализацияТоваровУслугТовары.ЗаказКлиента.Партнер КАК Партнер,
    РеализацияТоваровУслугТовары.Период КАК Период,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РеализацияТоваровУслугТовары.Номенклатура) КАК СуммаФейсов,
    РеализацияТоваровУслугТовары.ЗаказКлиента.Соглашение.Менеджер КАК Менеджер
ПОМЕСТИТЬ ВТ_Подготовка
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары
СГРУППИРОВАТЬ ПО
    РеализацияТоваровУслугТовары.ЗаказКлиента.Партнер,
    РеализацияТоваровУслугТовары.Период,
    РеализацияТоваровУслугТовары.ЗаказКлиента.Соглашение.Менеджер
;

Во второй таблице выполняется суммирование поля СуммаФейсов без разреза по партнерам:


ВЫБРАТЬ
    ВТ_Подготовка.Период КАК Период,
    ВТ_Подготовка.Менеджер КАК Менеджер,
    СУММА(ВТ_Подготовка.СуммаФейсов) КАК СуммаФейсов
ПОМЕСТИТЬ ВТ_Итог
ИЗ
    ВТ_Подготовка КАК ВТ_Подготовка
СГРУППИРОВАТЬ ПО
    ВТ_Подготовка.Период,
    ВТ_Подготовка.Менеджер
;

В консоли запросов при наличии двух партнеров с 1 и 2 уникальными позициями итоговый запрос выдаст сумму 3. Однако внутри СКД отчет возвращает значение 2.

Выясним причину такого поведения. Генератор макета СКД перед отправкой запроса в СУБД анализирует текущие настройки отчета (выбранные поля, группировки, отборы). Если поле Партнер не выводится в финальном отчете и не участвует в группировках настроек компоновки, интеллектуальный оптимизатор СКД исключает его из секций ВЫБРАТЬ и СГРУППИРОВАТЬ ПО всех промежуточных запросов. В результате первая таблица группируется напрямую по полям Период и Менеджер, а функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) рассчитывается сразу по всему объему данных, полностью ломая логику промежуточного суммирования.

Проанализируем доступные способы решения этой задачи.

Способ 1. Проброс поля в финальный запрос и установка роли «Обязательное»

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

В тексте запроса пробросим поле Партнер через все последующие временные таблицы вплоть до финальной выборки набора данных.
Перейдем на вкладку «Наборы данных» схемы компоновки данных.
Найдем поле Партнер в списке доступных полей набора данных.
В колонке «Роль» откроем окно настройки роли поля и установим флаг «Обязательное».

Свойство Обязательное сообщает генератору макета, что данное поле должно присутствовать во всех промежуточных выборках и группировках СУБД независимо от того, выбрал ли пользователь это поле в настройках структуры отчета.

Способ 2. Перенос логики агрегации в ресурсы СКД (рекомендуемый вариант)

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

В запросе оставляем единую временную таблицу с группировкой по всем необходимым разрезам (включая Партнер):


ВЫБРАТЬ
    РеализацияТоваровУслугТовары.Период КАК Период,
    РеализацияТоваровУслугТовары.ЗаказКлиента.Соглашение.Менеджер КАК Менеджер,
    РеализацияТоваровУслугТовары.ЗаказКлиента.Партнер КАК Партнер,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РеализацияТоваровУслугТовары.Номенклатура) КАК СуммаФейсов
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары
СГРУППИРОВАТЬ ПО
    РеализацияТоваровУслугТовары.Период,
    РеализацияТоваровУслугТовары.ЗаказКлиента.Соглашение.Менеджер,
    РеализацияТоваровУслугТовары.ЗаказКлиента.Партнер

Перейдем на вкладку «Ресурсы».
Добавим ресурс для поля СуммаФейсов.
В поле «Выражение» укажем формулу вычисления суммы по массиву нижестоящей группировки: Сумма(ВычислитьВыражениеСГруппировкойМассив("СуммаФейсов", "Партнер"))
В колонке «Рассчитывать по...» укажем вышестоящие группировки (например, Период, Менеджер) и Общий итог.

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

Способ 3. Отключение автозаполнения в наборе данных

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

На вкладке «Наборы данных» снимем флаг «Автозаполнение» у набора данных-запроса.
В тексте запроса вручную опишем доступные поля для отборов и настроек с помощью синтаксиса расширения языка запросов СКД в фигурных скобках {ВЫБРАТЬ ...} и {ГДЕ ...}.

Когда автозаполнение отключено, компоновщик данных выполняет все временные таблицы ровно в том виде, как они написаны в тексте запроса, сохраняя все промежуточные группировки.

Способ 4. Формирование временных таблиц в коде через МенеджерВременныхТаблиц

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

Разберем реализацию в модуле отчета через событие ПриКомпоновкеРезультата:


Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
    
    СтандартнаяОбработка = Ложь;
    
    // 1. Создаем менеджер временных таблиц и выполняем расчет
    МенеджерВТ = Новый МенеджерВременныхТаблиц;
    
    Запрос = Новый Запрос;
    Запрос.МенеджерВременныхТаблиц = МенеджерВТ;
    Запрос.Текст = 
        "ВЫБРАТЬ
        |	РеализацияТоваровУслугТовары.Период КАК Период,
        |	РеализацияТоваровУслугТовары.ЗаказКлиента.Соглашение.Менеджер КАК Менеджер,
        |	РеализацияТоваровУслугТовары.ЗаказКлиента.Партнер КАК Партнер,
        |	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РеализацияТоваровУслугТовары.Номенклатура) КАК СуммаФейсов
        |ПОМЕСТИТЬ ВТ_Подготовка
        |ИЗ
        |	Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТоваровУслугТовары
        |СГРУППИРОВАТЬ ПО
        |	РеализацияТоваровУслугТовары.Период,
        |	РеализацияТоваровУслугТовары.ЗаказКлиента.Соглашение.Менеджер,
        |	РеализацияТоваровУслугТовары.ЗаказКлиента.Партнер
        |;
        |
        |ВЫБРАТЬ
        |	ВТ_Подготовка.Период КАК Период,
        |	ВТ_Подготовка.Менеджер КАК Менеджер,
        |	СУММА(ВТ_Подготовка.СуммаФейсов) КАК СуммаФейсов
        |ПОМЕСТИТЬ ВТ_ИтоговыеДанные
        |ИЗ
        |	ВТ_Подготовка КАК ВТ_Подготовка
        |СГРУППИРОВАТЬ ПО
        |	ВТ_Подготовка.Период,
        |	ВТ_Подготовка.Менеджер";
        
    Запрос.Выполнить();
    
    // 2. Передаем менеджер временных таблиц в компоновщик макета
    Настройки = КомпоновщикНастроек.ПолучитьНастройки();
    
    КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
    МакетКомпоновки = КомпоновщикМакета.Выполнить(
        СхемаКомпоновкиДанных, 
        Настройки, 
        ДанныеРасшифровки, 
        , 
        , 
        Ложь, 
        МенеджерВТ
    );
    
    // 3. Выполняем компоновку и выводим результат
    ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
    ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, , ДанныеРасшифровки, Истина);
    
    ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
    ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
    ПроцессорВывода.Вывести(ПроцессорКомпоновки);
    
    МенеджерВТ.Закрыть();
    
КонецПроцедуры

В этом случае в самом наборе данных СКД достаточно написать простой запрос к уже готовой таблице ВТ_ИтоговыеДанные. Оптимизатор СКД не сможет повлиять на логику предварительного расчета, так как временные таблицы формируются стандартным объектом Запрос.