При работе с Системой компоновки данных (СКД) разработчики часто сталкиваются с интеллектуальным поведением встроенного оптимизатора запросов. Пытаясь снизить нагрузку на СУБД и уменьшить объем данных во временных таблицах, СКД анализирует итоговые настройки отчета (выбранные поля, группировки, отборы). Если, по мнению системы, какое-то поле из промежуточного запроса или временной таблицы (ВТ) не выводится на экран и не участвует в финальных отборах, оптимизатор исключает его из секции ВЫБРАТЬ временной таблицы.
Это приводит к серьезной логической ошибке: если в ВТ выполнялась группировка по этому полю для расчета агрегатных функций (например, исключения дублей, расчета средних показателей или развернутого сальдо), то после удаления поля оптимизатором логика свертки полностью ломается. Данные на выходе получаются неверными.
В этой статье мы подробно разберем, почему это происходит, проанализируем ситуацию на практических примерах и рассмотрим все существующие способы решения этой проблемы — от официальных рекомендаций фирмы «1С» до проверенных временем обходных путей.
---
Рассмотрим стандартный механизм работы компоновщика. Перед отправкой запроса в СУБД СКД динамически перестраивает его текст. Если у вас в схеме компоновки написан пакет запросов, система пытается максимально оптимизировать его структуру.
Представим, что во временной таблице мы группируем продажи по товарам и складам, чтобы получить уникальные пары значений:
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Склад КАК Склад,
СУММА(Продажи.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_ПродажиПоСкладам
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура,
Продажи.Склад
Если в результирующем запросе пакета мы выбираем только поле Номенклатура и выводим его в отчет, СКД посчитает поле Склад лишним. Оптимизатор исключит его из списка выборки и группировки временной таблицы ВТ_ПродажиПоСкладам. С точки зрения производительности СУБД это плюс, но с точки зрения логики расчетов — огромный минус, так как группировка по складам внутри ВТ больше не выполняется.
---
Самый надежный и правильный с точки зрения архитектуры платформы способ — это явное указание системе, что поле нельзя исключать из запроса. Для этого используется свойство роли поля «Обязательное».
Выясним по шагам, как настроить это свойство в конструкторе схемы компоновки данных:
Установка роли Обязательное жестко указывает оптимизатору СКД, что данное поле должно всегда присутствовать в финальном SQL-запросе, независимо от пользовательских настроек отчета.
---
Часто разработчики сталкиваются с тем, что поля временных таблиц не отображаются в списке полей схемы компоновки, и им невозможно назначить роль. Это происходит из-за включенного свойства «Автозаполнение».
Разберем, как решить эту проблему через ручное управление полями:
При отключенном автозаполнении СКД перестает самостоятельно «додумывать» структуру полей и строго следует вашим явным указаниям на вкладке набора данных.
---
Существует крайне интересный и простой обходной путь («фича»), основанный на особенностях работы алгоритма сопоставления полей в оптимизаторе СКД. Если дать полю во временной таблице уникальный синоним (псевдоним), который не встречается больше нигде в тексте запроса и в итоговом наборе данных, оптимизатор не сможет сопоставить его и «побоится» удалять.
Проанализируем пример переименования поля:
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Склад КАК Склад_Технический_Для_Группировки_ВТ,
СУММА(Продажи.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура,
Продажи.Склад_Технический_Для_Группировки_ВТ
В данном случае имя поля Склад_Технический_Для_Группировки_ВТ не совпадает с именами полей в результирующем запросе. Оптимизатор СКД обойдет эту временную таблицу стороной и не станет вырезать данное поле. Однако помните, что этот метод является недокументированным. Разработчики платформы в будущих релизах могут доработать оптимизатор, и этот трюк перестанет работать. Используйте его только тогда, когда стандартный метод через роли по каким-то причинам применить не удается.
---
Оптимизатор СКД наиболее агрессивно вмешивается во временные таблицы первого уровня. Если мы усложним структуру запроса, добавив промежуточную переливку данных, оптимизатор зачастую пасует.
Рассмотрим, как выглядит этот прием на практике в тексте запроса:
// Первый запрос пакета: создаем группировку
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.Склад КАК Склад,
СУММА(Продажи.Количество) КАК Количество
ПОМЕСТИТЬ ВТ_ПервыйШаг
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура,
Продажи.Склад;
////////////////////////////////////////////////////////////////////////////////
// Второй запрос пакета: просто переносим данные без изменений
ВЫБРАТЬ
ВТ_ПервыйШаг.Номенклатура КАК Номенклатура,
ВТ_ПервыйШаг.Склад КАК Склад,
ВТ_ПервыйШаг.Количество КАК Количество
ПОМЕСТИТЬ ВТ_ВторойШаг
ИЗ
ВТ_ПервыйШаг КАК ВТ_ПервыйШаг;
В этом сценарии СКД не станет оптимизировать и удалять группировочные поля из таблицы ВТ_ПервыйШаг, поскольку она напрямую не связана с результирующей выборкой, а используется как транзитный источник данных.
---
Если ваш отчет содержит крайне сложную логику, многоуровневые временные таблицы, расчеты и оптимизатор СКД упорно продолжает портить итоговые цифры, лучшим решением будет полный отказ от выполнения этого запроса силами СКД.
Рассмотрим данный подход по шагам:
Запрос.Таблицу Значений:
Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ ... ПОМЕСТИТЬ ..."; // Ваш исходный сложный запрос
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
ТаблицаДанных.
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ТаблицаДанных", РезультатЗапроса);
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, ВнешниеНаборыДанных, ДанныеРасшифровки);
Этот способ гарантирует 100% стабильность и предсказуемость результата. СКД получит уже свернутые, сгруппированные и подготовленные данные, которые останется только красиво вывести пользователю. Для автоматизации настройки схемы можно использовать инструмент для создания набора-объекта СКД с генерацией полей по тексту запроса.
---
Подводя итоги нашего разбора, дадим краткую рекомендацию по выбору решения:
Таблицу Значений (Способ 5).