Почему запрос работает в консоли, но врет в СКД? Разбираем роли полей и дополнение периодов в системе компоновки данных

Часто разработчики 1С сталкиваются с ситуацией: написан сложный пакетный запрос с временными таблицами, соединениями и расчетами. В обычной «Консоли запросов» он отрабатывает идеально, возвращая абсолютно правильные цифры. Но стоит перенести этот же запрос в схему компоновки данных (СКД) и запустить отчет — на выходе мы получаем пустые строки, неверные итоги, задвоенные или вообще нулевые показатели (как в случае автора темы, у которого «поломались» расчетные поля Флаг, Ср_Нед_Продажи_Шт и Ср_Нед_Продажи_Руб).

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

Выясняем причину: как оптимизатор СКД перестраивает исходные запросы

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

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

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

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

Специфика работы с остатками и ролями полей

В запросе автора используется виртуальная таблица РегистрНакопления.ТоварыВРознице.ОстаткиИОбороты. Расчет остатков — одна из самых сложных задач для СУБД, и СКД имеет встроенный механизм для работы с ними, но он требует правильной разметки схемы.

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

Например, если остаток товара на понедельник был 10 шт., на вторник — 10 шт., а на среду — 10 шт., то при группировке за неделю СКД без настроенных ролей выдаст итог в 30 шт. Это в корне неверно. Чтобы СКД рассчитывала остатки методом получения последнего значения на конец периода, нам необходимо настроить Роли полей:

1. Для поля периода (например, Период или Неделя) нужно установить роль «Период» с указанием типа и иерархии периодов.
2. Для полей остатков нужно задать роль «Остатки», указать имя группы (например, Количество), тип остатка (Начальный, Конечный) и связать его с соответствующим полем периода.

Искусственные календари против встроенного дополнения периодов

В исходном запросе автора мы видим огромную конструкцию из объединений чисел от 0 до 9 и их полных соединений (cross join) по условию ПО (ИСТИНА). Таким образом программист сгенерировал виртуальную таблицу дат (от 0 до 9999 дней от базовой даты 01.01.2000), чтобы получить непрерывный календарь дней в периоде отчета.

В обычных запросах это делается, чтобы заполнить «дыры»: если в какой-то день не было движений по регистру, виртуальная таблица остатков не вернет по нему запись. Левое соединение реальных остатков с таким сгенерированным календарем позволяет получить остатки на каждый день периода, даже если движений не было.

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

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

Вычисление средних величин в запросе и в ресурсах СКД

Автор пытается рассчитать средний остаток делением суммы остатков на количество дней в неделе непосредственно внутри текста запроса: ОстаткиНеделя.КоличествоОстаток / взДнейВНеделе.Дни.

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

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

Сумма(КоличествоОстаток) / Количество(Различные Период)

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

Разберем по шагам: переход к правильной архитектуре отчета

Рассмотрим подробнее, как полностью переписать этот функционал, сделав его быстрым, легким и на 100% совместимым с механизмами СКД.

Шаг 1. Упрощение текста запроса

Мы полностью отказываемся от временной таблицы Календарь и ручного расчета нарастающего итога. Вместо этого мы будем использовать стандартную виртуальную таблицу ОстаткиИОбороты с периодичностью День. Платформа сама умеет рассчитывать остаток на каждый день, когда были движения. Наш новый запрос будет выглядеть гораздо проще:

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

Шаг 2. Настройка ролей полей в СКД

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

1. Для поля Период установим роль «Период». Укажем порядковый номер 1 и тип периода День.
2. Для поля НачальныйОстаток настроим роль «Остатки», выбрав тип «Начальный» и связав его с полем Период.
3. Для поля КонечныйОстаток настроим роль «Остатки», выбрав тип «Конечный» и также связав его с полем Период.

Шаг 3. Включение автодополнения периодов

Чтобы в отчете выводились все дни периода (даже те, в которые не было движений), настроим дополнение периодов:

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

Шаг 4. Расчет сложных показателей на уровне ресурсов

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

• ПродажиШт: выражение Сумма(ПродажиШт)
• КонечныйОстаток: выражение ВычислитьВыражениеРесурса("КонечныйОстаток", "Период")
• СреднедневнойОстаток: выражение Среднее(КонечныйОстаток)
• Флаг: выражение Выбор Когда Среднее(КонечныйОстаток) >= 1 Тогда 1 Иначе 0 Конец
• СредненедельныеПродажи: выражение Сумма(ПродажиШт) / (РазностьДат(&НачалоПериода, &КонецПериода, "ДЕНЬ") + 1) * 7

Как быстро найти расхождения с помощью Консоли СКД

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

1. Сохраните вашу схему компоновки данных (с зашитым запросом и настройками) в файл .xml.
2. Откройте Консоль компоновки данных в режиме Предприятия и загрузите этот файл.
3. Укажите параметры отчета и выполните его.
4. Перейдите на вкладку «Выполняемый запрос».

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