Почему запрос СКД выдает неверные группировки по сравнению с консолью запросов и как это исправить?

← На главную

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

В чем суть проблемы и почему расходятся результаты?

Когда мы выполняем запрос в обычной консоли запросов, СУБД выполняет его «как есть» — строго по написанному коду. Однако СКД работает иначе. Перед отправкой запроса в СУБД его обрабатывает генератор макета компоновки данных.

Компоновщик анализирует настройки отчета (какие поля выбрал пользователь, какие отборы и группировки применил) и пытается максимально оптимизировать запрос, убирая из него все «лишнее» ради повышения производительности. Если какое-то поле (например, Регистратор) используется во временной таблице (ВТ) для группировки, но не выбирается в финальном запросе пакета и не выводится в отчет, СКД считает его неиспользуемым и безжалостно удаляет из секции ВЫБРАТЬ промежуточных таблиц.

Как только поле удаляется из выборки временной таблицы, логика группировки СГРУППИРОВАТЬ ПО ломается. Данные «схлопываются» раньше времени, расчет агрегатных функций (таких как МАКСИМУМ или СУММА) происходит некорректно, и на выходе мы получаем неверные итоговые показатели.

Проанализируем структуру запроса, с которым столкнулся автор темы:


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

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

Решение 1. Отключение автозаполнения и ручная разметка запроса

Самый надежный способ контролировать поведение генератора СКД — отключить автоматическое определение доступных полей и разметить их вручную с помощью фигурных скобок {}.

Разберем этот процесс по шагам:

Откройте схему компоновки данных и выберите ваш набор данных.
Снимите флаг Автозаполнение (в XML-схеме это свойство <autoFillFields>false</autoFillFields>).
В последнем (результирующем) запросе пакета укажите необязательные поля и отборы с помощью фигурных скобок. В промежуточных временных таблицах фигурные скобки ставить не нужно. Это сделает структуру запроса жесткой и предсказуемой для СКД.

Пример разметки результирующего запроса:


ВЫБРАТЬ
    ВТ_ГруппыДовольствующихсяПоМесяцу.МесяцРасхода КАК МесяцРасхода,
    ВТ_ГруппыДовольствующихсяПоМесяцу.Организация КАК Организация,
    ВТ_ГруппыДовольствующихсяПоМесяцу.КатегорияДовольствующихся КАК КатегорияДовольствующихся,
    СУММА(ВТ_ГруппыДовольствующихсяПоМесяцу.КоличествоДовольствующихся) КАК КоличествоДовольствующихся
    {ВЫБРАТЬ
        МесяцРасхода.*,
        Организация.*,
        КатегорияДовольствующихся.*,
        КоличествоДовольствующихся}
ИЗ
    ВТ_ГруппыДовольствующихсяПоМесяцу КАК ВТ_ГруппыДовольствующихсяПоМесяцу
{ГДЕ
    ВТ_ГруппыДовольствующихсяПоМесяцу.МесяцРасхода.*,
    ВТ_ГруппыДовольствующихсяПоМесяцу.Организация.*}
СГРУППИРОВАТЬ ПО
    ВТ_ГруппыДовольствующихсяПоМесяцу.МесяцРасхода,
    ВТ_ГруппыДовольствующихсяПоМесяцу.Организация,
    ВТ_ГруппыДовольствующихсяПоМесяцу.КатегорияДовольствующихся

Решение 2. Использование вкладки «Компоновка данных» в Конструкторе запросов

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

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

Решение 3. Настройка роли «Обязательное» для поля схемы СКД

Еще один элегантный способ запретить СКД исключать поле из промежуточных выборок — настроить его роль на вкладке «Наборы данных».

Выясним, как это сделать:

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

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

Решение 4. Добавление фиктивного условия в итоговый запрос

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

Посмотрим на пример реализации: в самом последнем запросе пакета добавьте обращение к проблемному полю в секции ГДЕ с заведомо истинным условием:


ГДЕ
    ВТ_ГруппыДовольствующихсяПоМесяцу.Регистратор ЕСТЬ НЕ NULL
    ИЛИ ВТ_ГруппыДовольствующихсяПоМесяцу.Регистратор = &НеопределенноеЗначение

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

Решение 5. Использование уникальных синонимов (алиасов)

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

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

Решение 6. Программный обход оптимизации СКД через внешний набор данных

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

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


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

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