При разработке отчетов в системе «1С:Предприятие» программисты часто сталкиваются с задачей сопоставления цепочки документов. Классический пример — построение отчета по выставленным счетам на оплату, оформленным на их основании расходным накладным (документам реализации) и платежным поручениям (документам поступления денежных средств). На первый взгляд задача кажется тривиальной: достаточно взять три таблицы и связать их левым соединением. Однако на практике разработчики получают неверные и многократно завышенные суммы. Разберем детально, почему так происходит, и выясним, как решить эту проблему правильно.
Рассмотрим структуру связей между нашими документами. Между счетом на оплату, реализацией и оплатой существует отношение «один-ко-многим». Это означает, что на основании одного счета покупателю может быть выписано несколько накладных (например, при частичной отгрузке товара по мере его поступления на склад) и проведено несколько платежей (в случае поэтапной оплаты или предоплаты).
Если мы попытаемся связать эти таблицы напрямую с помощью конструкции ЛЕВОЕ СОЕДИНЕНИЕ, СУБД выполнит операцию декартова произведения строк для каждого счета. Проанализируем простую ситуацию:
При прямом левом соединении Счета с Реализациями, а затем с Поступлениями по полю связи (Счет на оплату), СУБД сформирует таблицу из четырех строк ($2 \times 2 = 4$), так как каждая реализация свяжется с каждой оплатой в рамках одного счета. В результате в итоговой выборке мы получим четыре записи, где сумма самого счета продублируется четыре раза, а суммы реализаций и оплат — по два раза.
Даже если мы применим секцию СГРУППИРОВАТЬ ПО, обычное суммирование числовых полей выдаст неверные (завышенные) результаты. Простая группировка по счету не спасет, так как СУБД группирует уже объединенный декартовым произведением набор данных. Нам необходимо кардинально изменить архитектуру самого запроса.
Прежде чем перейти к правильным решениям, проанализируем исходный код запроса автора темы и выделим ключевые ошибки, снижающие производительность системы:
СчетаВТ выбираются абсолютно все счета из базы данных. Фильтрация по контрагенту и периоду происходит только в самом конце финального запроса. На реальной базе данных с сотнями тысяч документов это приведет к колоссальному падению производительности и перегрузке памяти сервера СУБД. Накладывать условия ГДЕ нужно на самом первом шаге формирования временной таблицы.Комментарий в документах 1С имеет тип «Строка неограниченной длины». В языке запросов такие поля запрещено использовать в секциях СГРУППИРОВАТЬ ПО и УПОРЯДОЧИТЬ ПО. Чтобы обойти это ограничение, длину строки необходимо лимитировать с помощью функции ПОДСТРОКА или использовать альтернативные методы связи.ГДЕ РеализацияТоваровУслуг.СчетНаОплатуПокупателю В (ВЫБРАТЬ А.Ссылка ИЗ СчетаВТ КАК А) заставляет СУБД строить неоптимальные планы выполнения. Гораздо эффективнее использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ с индексированной временной таблицей.РеализацияТоваровУслуг.СчетНаОплатуПокупателю.ДоговорКонтрагента порождает неявные левые соединения в SQL-запросе. Поскольку все необходимые реквизиты счета уже содержатся во временной таблице счетов, обращаться к ним «через точку» из таблиц реализаций избыточно.Посмотрим на первый архитектурный паттерн решения проблемы. Если детальные строки документов отгрузки и оплаты в отчете не требуются (необходимо видеть только общие суммы реализации и оплаты в разрезе каждого конкретного счета), то группировку нужно производить внутри временных таблиц.
Сгруппируем реализации по полю СчетНаОплатуПокупателю, а оплаты — по полю СчетНаОплату еще до момента их соединения. В результате в финальном запросе к таблице счетов будут присоединяться таблицы, в которых на один счет приходится строго одна строка. При связи «один-к-одному» левое соединение отработает корректно.
Разберем по шагам реализацию этого метода в коде запроса:
ВЫБРАТЬ
СчетНаОплатуПокупателю.Ссылка КАК Ссылка,
СчетНаОплатуПокупателю.Номер КАК Номер,
СчетНаОплатуПокупателю.Дата КАК Дата,
СчетНаОплатуПокупателю.Контрагент КАК Контрагент,
СчетНаОплатуПокупателю.СуммаДокумента КАК СуммаДокумента,
ПОДСТРОКА(СчетНаОплатуПокупателю.Комментарий, 1, 1000) КАК Комментарий,
СчетНаОплатуПокупателю.ДоговорКонтрагента КАК ДоговорКонтрагента
ПОМЕСТИТЬ СчетаВТ
ИЗ
Документ.СчетНаОплатуПокупателю КАК СчетНаОплатуПокупателю
ГДЕ
СчетНаОплатуПокупателю.Контрагент = &Контрагент
И СчетНаОплатуПокупателю.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
РеализацияТоваровУслуг.СчетНаОплатуПокупателю КАК СчетНаОплатуПокупателю,
СУММА(РеализацияТоваровУслуг.СуммаДокумента) КАК СуммаРеализации
ПОМЕСТИТЬ РеализацииВТ
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СчетаВТ КАК СчетаВТ
ПО РеализацияТоваровУслуг.СчетНаОплатуПокупателю = СчетаВТ.Ссылка
ГРУППИРОВАТЬ ПО
РеализацияТоваровУслуг.СчетНаОплатуПокупателю
ИНДЕКСИРОВАТЬ ПО
СчетНаОплатуПокупателю
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СчетНаОплату КАК СчетНаОплату,
СУММА(ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СуммаВзаиморасчетов) КАК СуммаОплаты
ПОМЕСТИТЬ ПоступлениеВТ
ИЗ
Документ.ПоступлениеНаРасчетныйСчет.РасшифровкаПлатежа КАК ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СчетаВТ КАК СчетаВТ
ПО ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СчетНаОплату = СчетаВТ.Ссылка
ГРУППИРОВАТЬ ПО
ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СчетНаОплату
ИНДЕКСИРОВАТЬ ПО
СчетНаОплату
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СчетаВТ.Контрагент КАК КонтрагентСчета,
СчетаВТ.Номер КАК НомерСчетаНаОплату,
СчетаВТ.Дата КАК ДатаСчетаНаОплату,
СчетаВТ.СуммаДокумента КАК СуммаСчетаНаОплату,
СчетаВТ.ДоговорКонтрагента КАК Договор1,
СчетаВТ.Комментарий КАК Комментарий1,
ЕСТЬNULL(РеализацииВТ.СуммаРеализации, 0) КАК СуммаРеализации,
ЕСТЬNULL(ПоступлениеВТ.СуммаОплаты, 0) КАК СуммаОплаты
ИЗ
СчетаВТ КАК СчетаВТ
ЛЕВОЕ СОЕДИНЕНИЕ РеализацииВТ КАК РеализацииВТ
ПО СчетаВТ.Ссылка = РеализацииВТ.СчетНаОплатуПокупателю
ЛЕВОЕ СОЕДИНЕНИЕ ПоступлениеВТ КАК ПоступлениеВТ
ПО СчетаВТ.Ссылка = ПоступлениеВТ.СчетНаОплату
УПОРЯДОЧИТЬ ПО
ДатаСчетаНаОплату
Обратим внимание на важную деталь: использование инструкции ИНДЕКСИРОВАТЬ ПО для временных таблиц по полям связей. Это позволяет СУБД мгновенно сопоставлять строки таблиц при соединении, что критически важно для производительности на крупных базах данных.
Второй способ — самый гибкий и производительный. Он позволяет получить детальные строки по каждому документу отгрузки и оплаты без риска задвоения сумм. Вместо горизонтального соединения таблиц мы объединим их вертикально с помощью конструкции ОБЪЕДИНИТЬ ВСЕ, распределив суммы по разным колонкам.
При таком подходе данные каждого типа документа выбираются в отдельные колонки, а пустые значения заполняются нулями. На конечном этапе мы просто сгруппируем результирующую таблицу по счету на оплату.
Проанализируем пример построения такого запроса:
ВЫБРАТЬ
СчетНаОплатуПокупателю.Ссылка КАК Ссылка,
СчетНаОплатуПокупателю.Номер КАК Номер,
СчетНаОплатуПокупателю.Дата КАК Дата,
СчетНаОплатуПокупателю.Контрагент КАК Контрагент,
СчетНаОплатуПокупателю.СуммаДокумента КАК СуммаДокумента,
СчетНаОплатуПокупателю.ДоговорКонтрагента КАК ДоговорКонтрагента,
ПОДСТРОКА(СчетНаОплатуПокупателю.Комментарий, 1, 1000) КАК Комментарий
ПОМЕСТИТЬ СчетаВТ
ИЗ
Документ.СчетНаОплатуПокупателю КАК СчетНаОплатуПокупателю
ГДЕ
СчетНаОплатуПокупателю.Контрагент = &Контрагент
И СчетНаОплатуПокупателю.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ОбъединениеДанных.СсылкаСчета КАК СсылкаСчета,
СУММА(ОбъединениеДанных.СуммаСчета) КАК СуммаСчета,
СУММА(ОбъединениеДанных.СуммаРеализации) КАК СуммаРеализации,
СУММА(ОбъединениеДанных.СуммаОплаты) КАК СуммаОплаты
ПОМЕСТИТЬ ИтогиВТ
ИЗ
(ВЫБРАТЬ
СчетаВТ.Ссылка КАК СсылкаСчета,
СчетаВТ.СуммаДокумента КАК СуммаСчета,
0 КАК СуммаРеализации,
0 КАК СуммаОплаты
ИЗ
СчетаВТ КАК СчетаВТ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РеализацияТоваровУслуг.СчетНаОплатуПокупателю,
0,
РеализацияТоваровУслуг.СуммаДокумента,
0
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СчетаВТ КАК СчетаВТ
ПО РеализацияТоваровУслуг.SчетНаОплатуПокупателю = СчетаВТ.Ссылка
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СчетНаОплату,
0,
0,
ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СуммаВзаиморасчетов
ИЗ
Документ.ПоступлениеНаРасчетныйСчет.РасшифровкаПлатежа КАК ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СчетаВТ КАК СчетаВТ
ПО ПоступлениеНаРасчетныйСчетРасшифровкаПлатежа.СчетНаОплату = СчетаВТ.Ссылка) КАК ОбъединениеДанных
ГРУППИРОВАТЬ ПО
ОбъединениеДанных.СсылкаСчета
ИНДЕКСИРОВАТЬ ПО
СсылкаСчета
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СчетаВТ.Контрагент КАК Контрагент,
СчетаВТ.Номер КАК НомерСчета,
СчетаВТ.Дата КАК ДатаСчета,
ИтогиВТ.СуммаСчета КАК СуммаСчета,
ИтогиВТ.СуммаРеализации КАК СуммаРеализации,
ИтогиВТ.СуммаОплаты КАК СуммаОплаты,
СчетаВТ.ДоговорКонтрагента КАК Договор,
СчетаВТ.Комментарий КАК Комментарий
ИЗ
ИтогиВТ КАК ИтогиВТ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ СчетаВТ КАК СчетаВТ
ПО ИтогиВТ.СсылкаСчета = СчетаВТ.Ссылка
УПОРЯДОЧИТЬ ПО
ДатаСчета
Проанализируем преимущества этого подхода. Использование конструкции ОБЪЕДИНИТЬ ВСЕ гарантирует полное отсутствие ложных декартовых связей. Каждая сумма попадает в итоговую агрегацию ровно один раз, что обеспечивает стопроцентную точность расчетов. В финальном запросе мы соединяем полученные итоги со СчетаВТ, чтобы безболезненно получить строковые реквизиты, включая Комментарий, без необходимости их группировки.
Если перед нами стоит задача разработать отчет, а не просто получить выборку в коде, лучшим решением будет использование Схемы компоновки данных (СКД). В СКД заложена штатная возможность обхода проблемы связи «один-ко-многим».
Для реализации этого метода выполним следующие действия:
СчетНаОплату.Каждое из описанных решений является архитектурно верным и выбирается исходя из конкретной прикладной задачи: предварительная агрегация удобна для точечных алгоритмов в коде, вертикальное объединение оптимально для сложных выборок, а СКД идеально подходит для построения пользовательских отчетов.