При разработке отчетов и обработок в системе 1С:Предприятие разработчики часто сталкиваются с необходимостью собрать сводные данные сразу из нескольких табличных частей одного документа. Типичный пример: в документе есть табличная часть со списком исполнителей или ответственных, табличная часть с перечнем товаров и табличная часть с услугами. Если связать эти таблицы через обычное левое соединение (ЛЕВОЕ СОЕДИНЕНИЕ), итоговые суммы по товарам и услугам кратно увеличиваются. Разберем по шагам, почему возникает эта ошибка и как составить запрос правильно.
Проанализируем механику работы СУБД при выполнении запроса. Каждая табличная часть документа хранится в базе данных как отдельная таблица, связанная с шапкой документа по полю Ссылка. Строки одной табличной части никак не связаны со строками другой табличной части на уровне конкретных позиций — их объединяет только ссылка на документ.
Когда мы выполняем прямое соединение двух или трех табличных частей по условию ТЧ1.Ссылка = ТЧ2.Ссылка И ТЧ1.Ссылка = ТЧ3.Ссылка, СУБД выполняет декартово произведение строк (связь «многие-ко-многим»).
Рассмотрим конкретный пример: если в табличной части товаров содержится 10 строк, а в табличной части услуг — 5 строк, то при соединении по ссылке результатом выборки для одного документа станут не 15, а 50 строк (10 × 5). Если к этому добавить еще и строки из табличной части исполнителей, объем дубликатов возрастет еще больше. При последующем суммировании полей через агрегатную функцию СУММА() сумма каждой строки товаров умножится на количество строк услуг, а сумма услуг — на количество строк товаров. В результате получаются завышенные в разы цифры.
Наиболее надежным и универсальным подходом в языке запросов 1С является разделение выборки данных на пакет с временными таблицами. Разберем этот подход подробно:
СГРУППИРОВАТЬ ПО по ссылке документа (и другим необходимым разрезам), рассчитывая агрегатную сумму.Ссылка уже стало уникальным (отношение «один-к-одному»), размножения строк не происходит.ЕСТЬNULL(), чтобы избежать получения значений NULL, если в документе отсутствует одна из табличных частей.Посмотрим на пример реализации пакетного запроса:
ВЫБРАТЬ
ТЧ_Товары.Ссылка КАК Ссылка,
СУММА(ТЧ_Товары.Сумма) КАК СуммаТоваров
ПОМЕСТИТЬ ВТ_ТоварыИтоги
ИЗ
Документ._Сборка.Товары КАК ТЧ_Товары
ГДЕ
ТЧ_Товары.Ссылка.Проведен = ИСТИНА
И ТЧ_Товары.Ссылка.ПериодРегистрации МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
ТЧ_Товары.Ссылка
;
ВЫБРАТЬ
ТЧ_Услуги.Ссылка КАК Ссылка,
СУММА(ТЧ_Услуги.Сумма) КАК СуммаУслуг
ПОМЕСТИТЬ ВТ_УслугиИтоги
ИЗ
Документ._Сборка.Услуги КАК ТЧ_Услуги
ГДЕ
ТЧ_Услуги.Ссылка.Проведен = ИСТИНА
И ТЧ_Услуги.Ссылка.ПериодРегистрации МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
ТЧ_Услуги.Ссылка
;
ВЫБРАТЬ
ТЧ_Сборщики.Ссылка КАК Документ,
ТЧ_Сборщики.Сборщик КАК Сборщик,
ЕСТЬNULL(ВТ_ТоварыИтоги.СуммаТоваров, 0) КАК СуммаТоваров,
ЕСТЬNULL(ВТ_УслугиИтоги.СуммаУслуг, 0) КАК СуммаУслуг
ИЗ
Документ._Сборка.Сборщики КАК ТЧ_Сборщики
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТоварыИтоги КАК ВТ_ТоварыИтоги
ПО ТЧ_Сборщики.Ссылка = ВТ_ТоварыИтоги.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_УслугиИтоги КАК ВТ_УслугиИтоги
ПО ТЧ_Сборщики.Ссылка = ВТ_УслугиИтоги.Ссылка
ГДЕ
ТЧ_Сборщики.Ссылка.Проведен = ИСТИНА
И ТЧ_Сборщики.Ссылка.ПериодРегистрации МЕЖДУ &НачалоПериода И &КонецПериода
И ТЧ_Сборщики.Сборщик = &Сборщик
ИТОГИ
СУММА(СуммаТоваров),
СУММА(СуммаУслуг)
ПО
Сборщик
Другой способ решения задачи — объединение строк табличных частей в единый поток данных. При использовании конструкции ОБЪЕДИНИТЬ ВСЕ таблицы не умножаются друг на друга, а выстраиваются последовательно.
Важно помнить о правилах построения объединений:
ВЫРАЗИТЬ(0 КАК ЧИСЛО(15, 2)). Использование литералов 1 или 2 приведет к искажению итоговых расчетов.Разберем пример запроса через объединение:
ВЫБРАТЬ
ТЧ_Товары.Ссылка КАК Документ,
ТЧ_Сборщики.Сборщик КАК Сборщик,
ТЧ_Товары.Сумма КАК СуммаТоваров,
ВЫРАЗИТЬ(0 КАК ЧИСЛО(15, 2)) КАК СуммаУслуг
ИЗ
Документ._Сборка.Товары КАК ТЧ_Товары
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ._Сборка.Сборщики КАК ТЧ_Сборщики
ПО ТЧ_Товары.Ссылка = ТЧ_Сборщики.Ссылка
ГДЕ
ТЧ_Товары.Ссылка.Проведен = ИСТИНА
И ТЧ_Товары.Ссылка.ПериодРегистрации МЕЖДУ &НачалоПериода И &КонецПериода
И ТЧ_Сборщики.Сборщик = &Сборщик
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТЧ_Услуги.Ссылка,
ТЧ_Сборщики.Сборщик,
ВЫРАЗИТЬ(0 КАК ЧИСЛО(15, 2)),
ТЧ_Услуги.Сумма
ИЗ
Документ._Сборка.Услуги КАК ТЧ_Услуги
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ._Сборка.Сборщики КАК ТЧ_Сборщики
ПО ТЧ_Услуги.Ссылка = ТЧ_Сборщики.Ссылка
ГДЕ
ТЧ_Услуги.Ссылка.Проведен = ИСТИНА
И ТЧ_Услуги.Ссылка.ПериодРегистрации МЕЖДУ &НачалоПериода И &КонецПериода
И ТЧ_Сборщики.Сборщик = &Сборщик
ИТОГИ
СУММА(СуммаТоваров),
СУММА(СуммаУслуг)
ПО
Сборщик
Если отчет разрабатывается с использованием Системы компоновки данных (СКД), проблему декартова произведения можно обойти штатными механизмами компоновки без объединений в тексте запроса:
Ссылка (источник связи — основной набор, приемник — подчиненный набор).В этом случае платформа рассчитывает агрегаты для каждого набора данных изолированно, исключая риск задвоения сумм.
Рассмотрим важные аспекты производительности при работе с табличными частями:
ТЧ.Ссылка.Организация или ТЧ.Ссылка.ПериодРегистрации приводят к неявным соединениям с таблицей документа на уровне СУБД. При необходимости фильтрации по шапке эффективнее сначала отобрать ссылки на документы во временную таблицу, проиндексировать ее по полю Ссылка, а затем связывать с табличными частями.РегистрНакопления при проведении. Обращение к виртуальной таблице Обороты регистра обеспечивает максимальное быстродействие и гарантирует корректность сумм.