Как в СКД избежать задвоения сумм и итогов при связи один ко многим?

← На главную

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

Проанализируем природу этой проблемы и разберем надежные способы ее решения в системе компоновки данных.

Почему возникает задвоение данных

Выясним причину некорректного поведения. В реляционной модели при выполнении соединения таблиц с отношением «один ко многим» СУБД возвращает декартово произведение строк. Если для одного плана закупок оформлено два заказа, а по каждому заказу прошло по два поступления, строка плана продублируется четыре раза.

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

Способ 1. Разделение запроса на отдельные наборы данных

Рассмотрим вариант, который помог автору вопроса. Вместо одного громоздкого запроса со сложными связями разделим выборку на отдельные наборы данных типа Запрос:

НаборДанных_Планы: выбирает документы планирования, номенклатуру, плановое количество и сумму.
НаборДанных_Заказы: выбирает данные заказов поставщикам в разрезе номенклатуры и ссылок на планы.
НаборДанных_Поступления: выбирает фактические поступления по заказам.

Перейдем на вкладку Связи наборов данных и свяжем наборы между собой:

Укажем источник связи НаборДанных_Планы, приемник НаборДанных_Заказы.
В условии связи свяжем поля: ДокументПланирования = ПланЗакупок и Номенклатура = Номенклатура.
Аналогично свяжем НаборДанных_Заказы с НаборДанных_Поступления по полям заказа и номенклатуры.

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

Важное ограничение: помним, что при очень больших объемах выборки связь наборов данных на уровне СКД может выполняться медленнее прямого SQL-соединения, так как подчиненные наборы могут запрашиваться пакетами.

Способ 2. Использование функции ВычислитьВыражениеСГруппировкойМассив в ресурсах

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

Перейдем на вкладку Ресурсы. Добавим поле СуммаПоПлану и поле СуммаПоДокументу (сумма заказа). Настроим выражение для ресурса СуммаПоПлану в поле Рассчитывать по...:

Для поля плановой суммы по группировке Номенклатура или ДокументПлан оставим базовое выражение:

Максимум(СуммаПоПлану)

Для вышестоящих уровней группировок и строки Общий итог пропишем агрегацию по массиву уникальных значений:


Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаПоПлану)", "ДокументПлан"))

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


Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаПоДокументу)", "ДокументЗаказ"))

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

Способ 3. Построение запроса через ОБЪЕДИНИТЬ ВСЕ

Посмотрим на классический архитектурный прием, применяемый при сборе разнородных показателей в единую таблицу. Вместо горизонтального размножения колонок через ЛЕВОЕ СОЕДИНЕНИЕ объединим выборки вертикально с помощью конструкции ОБЪЕДИНИТЬ ВСЕ.

Посмотрим на пример структуры запроса:


ВЫБРАТЬ
	План.Номенклатура КАК Номенклатура,
	План.Ссылка КАК ДокументПлан,
	План.Сумма КАК СуммаПоПлану,
	НЕОПРЕДЕЛЕНО КАК ДокументЗаказ,
	0 КАК СуммаПоЗаказу,
	НЕОПРЕДЕЛЕНО КАК ДокументПоступления,
	0 КАК СуммаПоступления
ИЗ
	Документ.ПланЗакупок.СоставПлана КАК План

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	Заказ.Номенклатура,
	Заказ.Ссылка.ПланЗакупок,
	0,
	Заказ.Ссылка,
	Заказ.Сумма,
	НЕОПРЕДЕЛЕНО,
	0
ИЗ
	Документ.ЗаказПоставщику.Товары КАК Заказ

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	Поступление.Номенклатура,
	Поступление.Ссылка.Сделка.ПланЗакупок,
	0,
	Поступление.Ссылка.Сделка,
	0,
	Поступление.Ссылка,
	Поступление.Сумма
ИЗ
	Документ.ПоступлениеТоваровУслуг.Товары КАК Поступление

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

Настройка структуры отчета для визуального скрытия дублей

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

Вместо вывода только детальных записей построим иерархию группировок: Номенклатура → ДокументПлан → ДокументЗаказ → Детальные записи (куда выведем поступление).
На вкладке Другие настройки установим параметр Расположение полей группировок в значение В отдельной колонке.
Для родительских группировок в параметрах вывода отключим тип макета Заголовок и подвал, если не требуется выводить промежуточные итоги по каждому документу.

В результате значения родительских документов будут выводиться в отдельной ячейке только один раз для всей подчиненной ветки, обеспечивая чистый и читаемый вид отчета.