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

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

Суть технической проблемы: почему «ломается» группировка в СКД

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

1. Получение списка дат из производственного календаря за выбранный период во временную таблицу Дни.
2. Соединение таблицы дней с виртуальной таблицей остатков и оборотов для расчета остатка на каждый день по каждой номенклатуре. Результат помещается во временную таблицу ОстаткиПоДням с группировкой по полям Период и Номенклатура.
3. Финальная выборка из таблицы ОстаткиПоДням, где мы группируем данные по Номенклатура и рассчитываем количество дней, когда остаток был равен нулю, используя конструкцию СУММА(ВЫБОР КОГДА Остаток = 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ).

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

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

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

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

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

Изменим наш финальный пакет запроса, добавив в него выборку периода с использованием агрегатной функции:

ВЫБРАТЬ
    ОстаткиПоДням.Номенклатура КАК Номенклатура,
    СУММА(ВЫБОР 
        КОГДА ОстаткиПоДням.КоличествоКонечныйОстаток = 0 
            ТОГДА 1 
        ИНАЧЕ 0 
    КОНЕЦ) КАК Поле1,
    МАКСИМУМ(ОстаткиПоДням.Период) КАК Период
ИЗ
    ОстаткиПоДням КАК ОстаткиПоДням
СГРУППИРОВАТЬ ПО
    ОстаткиПоДням.Номенклатура

Теперь выполним настройку роли этого поля в конструкторе схемы компоновки данных:

1. Перейдем на вкладку «Наборы данных».
2. Найдем наше поле Период в таблице полей набора данных.
3. В колонке «Роль» нажмем на кнопку редактирования.
4. В открывшемся окне установим флаг «Обязательное».

Установка свойства Обязательное сообщает компоновщику макета, что данное поле должно присутствовать в результирующем SQL-запросе всегда, независимо от того, выбрал ли его пользователь в настройках структуры отчета. Это гарантирует сохранение детальных записей по дням во второй временной таблице.

Способ 2. Отключение флага «Автозаполнение»

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

Разберем, как это сделать:

1. На вкладке «Наборы данных» снимем флаг «Автозаполнение» у нашего набора данных-запроса.
2. После отключения автозаполнения СКД перестанет автоматически определять доступные поля. Нам потребуется вручную описать поля, которые будут доступны для выбора в отчете, отборов и группировок.
3. Для этого перейдем на вкладку «Компоновка данных» в конструкторе запроса и вручную укажем поля выборки, условий и группировок на соответствующих вкладках.

Этот способ дает 100% гарантию того, что запрос выполнится в СУБД ровно в том виде, в каком он написан в редакторе, однако он требует от разработчика больше ручной работы по настройке параметров компоновки.

Способ 3. Применение фиктивных условий (разработческий хак)

Существует альтернативный способ обмана оптимизатора без изменения структуры финальной таблицы и без отключения автозаполнения. Мы можем наложить фиктивное условие на поле Период в промежуточном или финальном запросе. Например:

ГДЕ 
    ОстаткиПоДням.Период = ОстаткиПоДням.Период ИЛИ ИСТИНА

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

Архитектурно правильное решение: нативное дополнение периодов в СКД

Использование связки производственного календаря с виртуальной таблицей остатков через декартово произведение или левое соединение во временных таблицах — крайне неэффективный подход. При увеличении объема данных в базе (росте количества номенклатуры и складских движений) такой запрос начнет приводить к колоссальным нагрузкам на СУБД и зависаниям отчета.

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

Напишем простой запрос к виртуальной таблице ОстаткиИОбороты с периодичностью День, обязательно ограничив ее параметрами начала и окончания периода:

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

Теперь настроим дополнение дней на уровне настроек структуры отчета:

1. Перейдем на вкладку «Настройки» в схеме компоновки данных.
2. В структуре отчета добавим группировку по полю ПериодДень.
3. Выделим эту группировку и в окне свойств (в правом нижнем углу) настроим параметры дополнения:
   • Тип дополнения — установим значение «День».
   • Начало периода — укажем параметр &НачалоПериода.
   • Конец периода — укажем параметр &КонецПериода.

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

Сумма(Выбор Когда Остаток = 0 Тогда 1 Иначе 0 Конец)

Как отлаживать запросы СКД и видеть реальный SQL-текст

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

Путь 1. Использование специализированных инструментов. Рекомендуется установить внешнюю обработку «Консоль компоновки данных» (доступна на ИТС) или воспользоваться подсистемой «Инструменты разработчика». Они позволяют открыть схему отчета, применить настройки и в один клик увидеть преобразованный текст запроса со всеми временными таблицами.

Путь 2. Программный вывод запроса в режиме отладки. Если мы находимся в режиме конфигуратора на точке останова в модуле объекта отчета, мы можем получить реальный текст запроса через вычисление выражения:

// Доходим до этапа создания макета компоновки
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.ПолучитьНастройки());
// В коллекциях макета будет лежать преобразованный запрос
ТекстРеальногоЗапроса = МакетКомпоновки.НаборыДанных.НаборДанных1.Запрос;

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