Почему СКД искажает или удваивает суммы при отключении детальных записей и как это исправить?

← На главную

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

Анализ исходной ситуации и симптомов проблемы

Рассмотрим классическую задачу: нам необходимо разработать отчет, который собирает данные о движениях товаров из документов оприходования и списания. В запросе мы используем временные таблицы для сбора данных по оприходованиям и списаниям за определенные периоды, объединяем их в единую таблицу Итог, а затем соединяем полученный результат с регистрами сведений ЦеныНоменклатурыМагазинов.СрезПоследних и Себестоимость.СрезПоследних, чтобы получить актуальные цены и себестоимость номенклатуры на дату инвентаризации.

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


ВЫБРАТЬ
    ЕСТЬNULL(МАКСИМУМ(ОприходованиеДо.Дата), &ПустаяДата) КАК ПериодДо
ПОМЕСТИТЬ ПериодОприхДо
ИЗ
    Документ.ОприходованиеТоваров КАК ОприходованиеДо
ГДЕ
    ОприходованиеДо.ДокументОснование = &ДокументПрошлойИнвентаризации
    И ОприходованиеДо.ДокументОснование.Дата < &ДатаТекущейИнвентаризации
    И ОприходованиеДо.Проведен
    И ОприходованиеДо.ДокументОснование.Проведен
    И ОприходованиеДо.Магазин = &Магазин
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Оприходование.Дата КАК Период
ПОМЕСТИТЬ ПериодОприх
ИЗ
    Документ.ОприходованиеТоваров КАК Оприходование
ГДЕ
    Оприходование.ДокументОснование = &Ссылка
    И Оприходование.Проведен
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ОприходованиеТоваровТовары.Номенклатура,
    СУММА(ОприходованиеТоваровТовары.Количество) КАК Количество,
    ОприходованиеТоваровТовары.Ссылка
ПОМЕСТИТЬ ОприхДо
ИЗ
    Документ.ОприходованиеТоваров.Товары КАК ОприходованиеТоваровТовары
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПериодОприхДо КАК ПериодОприхДо
        ПО ОприходованиеТоваровТовары.Ссылка.Дата > ПериодОприхДо.ПериодДо
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПериодОприх КАК ПериодОприх
        ПО ОприходованиеТоваровТовары.Ссылка.Дата <= ПериодОприх.Период
ГДЕ
    ОприходованиеТоваровТовары.Ссылка.Проведен
    И ОприходованиеТоваровТовары.Ссылка.ХозяйственнаяОперация В(&СписокХозОперацийОприх)
    И ОприходованиеТоваровТовары.Ссылка.Магазин = &Магазин
 СГРУППИРОВАТЬ ПО
    ОприходованиеТоваровТовары.Номенклатура,
    ОприходованиеТоваровТовары.Ссылка
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЕСТЬNULL(МАКСИМУМ(СписаниеДо.Дата), &ПустаяДата) КАК ПериодДо
ПОМЕСТИТЬ ПериодСписДо
ИЗ
    Документ.СписаниеТоваров КАК СписаниеДо
ГДЕ
    СписаниеДо.ДокументОснование = &ДокументПрошлойИнвентаризации
    И СписаниеДо.ДокументОснование.Дата < &ДатаТекущейИнвентаризации
    И СписаниеДо.Проведен
    И СписаниеДо.ДокументОснование.Проведен
    И СписаниеДо.Магазин = &Магазин
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Списание.Дата КАК Период
ПОМЕСТИТЬ ПериодСпис
ИЗ
    Документ.СписаниеТоваров КАК Списание
ГДЕ
    Списание.ДокументОснование = &Ссылка
    И Списание.Проведен
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    СписаниеТоваровТовары.Номенклатура,
    СУММА(СписаниеТоваровТовары.Количество) КАК Количество,
    СписаниеТоваровТовары.Ссылка
ПОМЕСТИТЬ СписДо
ИЗ
    Документ.СписаниеТоваров.Товары КАК СписаниеТоваровТовары
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПериодСписДо КАК ПериодСписДо
        ПО СписаниеТоваровТовары.Ссылка.Дата > ПериодСписДо.ПериодДо
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПериодСпис КАК ПериодСпис
        ПО СписаниеТоваровТовары.Ссылка.Дата <= ПериодСпис.Период
ГДЕ
    СписаниеТоваровТовары.Ссылка.Проведен
    И СписаниеТоваровТовары.Ссылка.ХозяйственнаяОперация В(&СписокХозОперацийСпис)
    И СписаниеТоваровТовары.Ссылка.Магазин = &Магазин
 СГРУППИРОВАТЬ ПО
    СписаниеТоваровТовары.Номенклатура,
    СписаниеТоваровТовары.Ссылка
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ОприхДо.Номенклатура,
    ОприхДо.Количество КАК КоличествоПриход,
    0 КАК КоличествоРасход,
    ОприхДо.Ссылка,
    ОприхДо.Ссылка.ХозяйственнаяОперация КАК СсылкаХозяйственнаяОперация
ПОМЕСТИТЬ Итог
ИЗ
    ОприхДо КАК ОприхДо
 ОБЪЕДИНИТЬ ВСЕ
 ВЫБРАТЬ
    СписДо.Номенклатура,
    0,
    СписДо.Количество,
    СписДо.Ссылка,
    СписДо.Ссылка.ХозяйственнаяОперация
ИЗ
    СписДо КАК СписДо
;
 ////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Итог.Номенклатура КАК Номенклатура,
    СУММА(Итог.КоличествоПриход) КАК КоличествоПриход,
    СУММА(Итог.КоличествоРасход) КАК КоличествоРасход,
    Итог.Номенклатура.УИД КАК НоменклатураУИД,
    МАКСИМУМ(ЕСТЬNULL(ЦеныНоменклатурыМагазиновСрезПоследних.Цена, 0)) КАК ЦенаВМагазине,
    Итог.Ссылка КАК Документ,
    Итог.СсылкаХозяйственнаяОперация КАК ХозяйственнаяОперация,
    МАКСИМУМ(ВЫБОР
            КОГДА СебестоимостьСрезПоследних.Количество > 0
                ТОГДА СебестоимостьСрезПоследних.Стоимость / СебестоимостьСрезПоследних.Количество
            ИНАЧЕ 0
        КОНЕЦ) КАК Цена
ИЗ
    Итог КАК Итог
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатурыМагазинов.СрезПоследних(&ДатаТекущейИнвентаризации, Магазин = &Магазин) КАК ЦеныНоменклатурыМагазиновСрезПоследних
        ПО Итог.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследних.Номенклатура
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Себестоимость.СрезПоследних(&ДатаТекущейИнвентаризации, Склад = &Склад) КАК СебестоимостьСрезПоследних
        ПО Итог.Номенклатура = СебестоимостьСрезПоследних.Номенклатура
 СГРУППИРОВАТЬ ПО
    Итог.Номенклатура,
    Итог.Номенклатура.УИД,
    Итог.Ссылка,
    Итог.СсылкаХозяйственнаяОперация

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

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

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

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

Проанализируем, что происходит, когда в структуре отчета мы оставляем только группировки по документам и хозяйственным операциям, а детальные записи с номенклатурой отключаем:

СКД видит, что поле Номенклатура не выбрано пользователем и не используется в активных группировках.
Оптимизатор принимает решение исключить поле Итог.Номенклатура из списка выборки и группировки итогового пакета запроса.
Вследствие исключения поля группировки полностью ломается логика ЛЕВОГО СОЕДИНЕНИЯ с виртуальными таблицами среза последних цен и себестоимости. Связь по полю ПО Итог.Номенклатура = ЦеныНоменклатурыМагазиновСрезПоследних.Номенклатура больше не может быть выполнена корректно "один к одному".
В СУБД происходит так называемое декартово произведение (раздувание выборки). Вместо того чтобы сопоставить одну конкретную цену одной номенклатуре внутри документа, СУБД начинает дублировать строки документов, соединяя их со всеми совпавшими записями из срезов последних.
Итоговые показатели суммируются по этим раздутым строкам, из-за чего мы и видим "астрономические" неадекватные цифры в отчете.

Пошаговое решение: применение роли «Обязательное» для полей связи

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

Выполним исправление по шагам:

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

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

Второй шаг: безопасная обработка NULL в соединениях

В процессе работы с левыми соединениями очень важно обеспечить корректность математических операций. Если для какой-то номенклатуры в базе данных не окажется установленной цены магазина или себестоимости, СУБД вернет значение NULL.

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

Поэтому настоятельно рекомендуется оборачивать все числовые поля, получаемые из левых соединений, в функцию ЕСТЬNULL непосредственно в тексте запроса или использовать аналогичную функцию при расчете вычисляемых полей в СКД:

ЕСТЬNULL(ЦеныНоменклатурыМагазиновСрезПоследних.Цена, 0) КАК ЦенаВМагазине

Этот простой шаг гарантирует, что даже при отсутствии цен система подставит числовой ноль (0), и математические формулы вычисляемых полей отработают без сбоев.

Альтернативные архитектурные решения

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

Способ 1. Отключение автозаполнения доступных полей

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

Способ 2. Использование уникальных псевдонимов во временных таблицах

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

Заключение

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