При переходе от написания обычных запросов к разработке отчетов в Системе компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда один и тот же запрос выдает правильные данные в консоли запросов, но «ломается» внутри отчета. Особенно ярко это проявляется при использовании временных таблиц и конструкции ОБЪЕДИНИТЬ ВСЕ. Рассмотрим подробнее, почему это происходит и как правильно организовать расчеты в СКД.
Представим типичную задачу: отчет по продажам, где нужно перевести штуки в килограммы. В коде запроса мы умножаем количество на коэффициент веса. В одиночном запросе все работает идеально: продано 188 штук, умножаем на 0.7 кг, получаем 131.6 кг. Однако стоит нам добавить второй аналогичный запрос через ОБЪЕДИНИТЬ (например, для сравнения периодов), как СКД внезапно начинает выводить просто количество штук (188) вместо рассчитанного веса.
Это происходит из-за того, что СКД — это не просто инструмент для выполнения SQL-запросов, а сложный генератор, который перестраивает ваш исходный текст перед отправкой в СУБД. Проанализируем основные причины такого поведения.
Одной из главных ошибок является использование пакета запросов с помещением данных во временную таблицу (ПОМЕСТИТЬ Данные) и предварительной группировкой (СУММА) внутри этой таблицы.
Когда мы пишем так:
ВЫБРАТЬ
Продажи.Номенклатура,
СУММА(Продажи.Количество * Продажи.Номенклатура.Вес) КАК ВесОборот
ПОМЕСТИТЬ ВТ_Данные
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(...) КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура
Мы ограничиваем возможности компоновщика. Если включено Автозаполнение, СКД пытается «пролезть» внутрь вашего пакета запросов, чтобы наложить отборы или добавить поля. При объединении нескольких таких запросов оптимизатор СКД может некорректно сопоставить поля временных таблиц, особенно если в итоговом запросе вы просто выбираете данные из ВТ_Данные. В результате расчет Количество * Вес может быть проигнорирован или заменен на простые итоги по количеству.
Обратите внимание на конструкцию АналитикаУчетаНоменклатуры.Номенклатура.ВесЧислитель. В 1С это называется разыменованием ссылочного поля.
Рассмотрим, что происходит на уровне СУБД: каждый такой переход через точку порождает неявное левое соединение (LEFT JOIN). Когда таких соединений становится много, а запросы объединяются, структура запроса становится тяжелой для оптимизатора СКД. В некоторых случаях при объединении (UNION) СКД может «потерять» цепочку соединений, если поле не выбрано явно или не указано в ресурсах.
Для исправления ситуации нам необходимо пересмотреть архитектуру запроса. Вместо того чтобы заставлять запрос считать все сразу, мы должны предоставить СКД «сырые» данные, а математику доверить механизму ресурсов.
Разберем, как должен выглядеть правильный запрос. Мы отказываемся от временных таблиц (если они не продиктованы жесткой необходимостью оптимизации) и убираем агрегатные функции (СУММА) из текста.
ВЫБРАТЬ
Выручка.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
Выручка.КоличествоОборот КАК Количество,
Выручка.АналитикаУчетаНоменклатуры.Номенклатура.ВесЧислитель КАК ВесКоэффициент,
Выручка.СуммаВыручкиОборот КАК СуммаВыручки,
Выручка.СтоимостьОборот КАК Стоимость
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, , ) КАК Выручка
Если нам нужно объединение, мы просто добавляем ОБЪЕДИНИТЬ ВСЕ и второй аналогичный запрос ниже. Обратите внимание: мы не считаем вес прямо в запросе.
Выясним, как заставить систему правильно считать итоги. Переходим на закладку «Ресурсы» в конструкторе СКД. Именно здесь должна происходить вся магия умножения количества на вес.
В колонке «Выражение» для поля ВесОборот (которое мы можем создать как вычисляемое поле или использовать существующее) прописываем формулу:
Сумма(Количество * ВесКоэффициент)
Почему это важно:
Количество и ВесКоэффициент как независимые источники данных и применит к ним общую формулу ресурса.Проанализируем ситуацию с производительностью. Чтобы избежать многократных обращений к справочнику через точку, лучше явно соединить таблицу оборотов с таблицей аналитики или справочником Номенклатура:
ВЫБРАТЬ
НоменклатураСпр.Ссылка КАК Номенклатура,
Выручка.КоличествоОборот КАК Количество,
НоменклатураСпр.ВесЧислитель КАК ВесКоэффициент
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, , ) КАК Выручка
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК НоменклатураСпр
ПО Выручка.АналитикаУчетаНоменклатуры.Номенклатура = НоменклатураСпр.Ссылка
Такой подход делает структуру запроса прозрачной для компоновщика, и риск «потери» данных при объединении сводится к минимуму.
Если объединение в одном запросе все равно вызывает сложности или требует разных параметров (например, разные периоды для колонок «Прошлый год» и «Текущий год»), рассмотрим альтернативу — использование нескольких наборов данных - запросов.
Вместо использования ОБЪЕДИНИТЬ в одном тексте:
Номенклатура.Это позволяет избежать конфликтов во временных таблицах и дает более гибкий контроль над тем, как данные объединяются в итоговой таблице отчета.
Проанализировав проблему объединения запросов в СКД, мы можем сформулировать золотые правила:
СУММА, КОЛИЧЕСТВО в самом запросе мешают СКД правильно рассчитывать итоги по иерархии. Выносите расчеты в ресурсы.Соблюдение этих принципов позволит вам создавать стабильные и точные отчеты, которые не «рассыпаются» при добавлении новых условий или объединении источников данных.