При переходе от написания обычных запросов к работе с Системой компоновки данных (СКД) начинающие разработчики часто сталкиваются с ситуацией, когда один и тот же код выдает разные результаты в «Консоли запросов» и в отчете СКД. Типичный пример: вам необходимо посчитать количество дней, в которых остаток товара был равен нулю. В консоли запросов вы получаете верную цифру (например, 5 дней), а в СКД — всегда 1 или 0. Проанализируем, почему это происходит, и разберем способы решения этой проблемы.
Рассмотрим ситуацию, описанную автором темы. Программист использует пакетный запрос. В первом пакете формируется таблица с периодами (днями), во втором — рассчитываются остатки по дням и помещаются во временную таблицу ОстаткиПоДням. В третьем пакете происходит агрегация: мы хотим сгруппировать данные по номенклатуре и просуммировать случаи, когда остаток был нулевым.
Логика запроса выглядит следующим образом:
ВЫБРАТЬ
ОстаткиПоДням.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР КОГДА ОстаткиПоДням.КоличествоКонечныйОстаток = 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК Поле1
ИЗ
ОстаткиПоДням КАК ОстаткиПоДням
СГРУППИРОВАТЬ ПО
ОстаткиПоДням.Номенклатура;
В обычной консоли запросов этот код работает идеально. Однако СКД — это не просто транспорт для выполнения SQL. Это генератор отчетов, который анализирует итоговые настройки (выбранные поля, группировки, отборы) и переписывает ваш запрос «на лету» ради оптимизации производительности. Если в финальном результате отчета вы не выводите поле Период, СКД решает, что это поле лишнее, и удаляет его из секций ВЫБРАТЬ и СГРУППИРОВАТЬ ПО во всех временных таблицах пакета.
В результате временная таблица ОстаткиПоДням теряет свою детальность до дня. Группировка схлопывается до уровня номенклатуры еще на этапе формирования временной таблицы. Вместо того чтобы суммировать нули по каждому дню, запрос фактически проверяет: «Был ли хотя бы один нулевой остаток за весь период?». Если да — получаем 1, если нет — 0.
Самый очевидный способ, к которому пришел автор темы, — это принудительно добавить поле Период в финальный пакет запроса и включить его в группировку. Посмотрим, как должен выглядеть исправленный участок кода:
ВЫБРАТЬ
ОстаткиПоДням.Номенклатура КАК Номенклатура,
ОстаткиПоДням.Период КАК Период,
СУММА(ВЫБОР КОГДА ОстаткиПоДням.КоличествоКонечныйОстаток = 0 ТОГДА 1 ИНАЧЕ 0 КОНЕЦ) КАК Поле1
ИЗ
ОстаткиПоДням КАК ОстаткиПоДням
СГРУППИРОВАТЬ ПО
ОстаткиПоДням.Номенклатура,
ОстаткиПоДням.Период;
Этот метод заставляет СКД сохранить поле Период во временных таблицах. Однако у него есть существенный минус: если вы не хотите видеть детальные записи по дням в отчете, вам придется настраивать группировки в схеме так, чтобы схлопывать результат обратно, что может быть неудобно при работе с итогами.
Выясним причину, по которой СКД «выбрасывает» поля. Платформа делает это только в том случае, если считает поле необязательным для получения итогового результата. Мы можем переопределить это поведение в настройках набора данных.
Разберем по шагам, как это сделать:
Период (или то поле, по которому должна сохраняться детальность во временной таблице).Важный момент: установка этого флага гарантирует, что СКД включит данное поле во все временные таблицы и вложенные запросы, даже если пользователь не выбрал его в настройках отчета. Это самый «чистый» способ решения проблемы, сохраняющий структуру вашего исходного запроса.
Зачастую расчеты вида «количество дней с условием» правильнее выносить из текста запроса в выражения ресурсов. Это позволяет избежать проблем с оптимизацией и корректно рассчитывать итоги по иерархии номенклатуры или складам.
Проанализируем алгоритм реализации:
Номенклатура, Период, Остаток. Никаких сумм и группировок по дням в самом тексте запроса не делаем.ВычислитьВыражениеСГруппировкойМассив.Пример выражения ресурса:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Выбор Когда Остаток = 0 Тогда 1 Иначе 0 Конец", "Период"))
Такой подход позволяет СКД сначала собрать массив значений по периодам, а затем просуммировать их. Это гарантирует точность данных при любой структуре отчета.
В языке компоновки данных существуют специальные конструкции — фигурные скобки {}. Они позволяют явно указать платформе, какие части запроса являются расширением компоновки. Если вы обернете поля во временной таблице в фигурные скобки, вы сможете более гибко управлять их поведением. Однако для жестко заданных расчетов во временных таблицах использование флага «Обязательное» (Решение 2) является более надежным и простым в поддержке.
Если вы видите странные цифры в отчете, не гадайте на коде запроса. Рассмотрим метод проверки фактического SQL-запроса:
Используйте специализированную «Консоль компоновки данных» (не путать с обычной консолью запросов). Она позволяет выгрузить Макет компоновки. В этом макете (в формате XML или через просмотр в консоли) можно найти узел <query>. Там будет содержаться именно тот текст запроса, который платформа сгенерировала после всех оптимизаций. Именно там вы увидите, как исчезла ваша группировка по периоду, и сможете понять, какие поля нужно пометить как обязательные.
Проанализировав ситуацию, можно сделать следующие выводы для эффективной работы с СКД:
Обязательное.Автозаполнение и вручную прописать роли полей (особенно для периодов). Это защитит вас от неожиданных «схлопываний» группировок.Следуя этим правилам, вы сможете избежать «магического» изменения цифр в ваших отчетах и научитесь использовать механизмы оптимизации 1С себе во благо, а не в ущерб точности данных.