При построении сложных отчетов и выборок данных через классический язык запросов 1С разработчики нередко сталкиваются с ситуацией, когда суммы документов в вышестоящих группировках многократно завышаются. Проанализируем типичный сценарий: необходимо вывести список контрагентов, оформленные по ним реализации, а также привязанные к ним возвраты товаров или документы управленческого учета. Если к одной накладной относится несколько подчиненных документов, в итоговых строках сумма реализации умножается на количество присоединенных записей.
Выясним причину такого поведения платформы. В отличие от Системы компоновки данных (СКД), где для каждого уровня группировки можно задать индивидуальное выражение расчета ресурса, в обычном запросе 1С агрегатные функции секции ИТОГИ всегда рассчитываются по детальным записям, а не по значениям подчиненных итогов. Если применить ИТОГИ СУММА(СуммаДокумента) ПО Контрагент, Документ, то при связи «один-ко-многим» сумма документа будет продублирована столько раз, сколько строк получилось в соединении. Если же использовать МАКСИМУМ(СуммаДокумента), то внутри группы документа сумма станет верной, но на уровне контрагента отобразится лишь максимальная сумма среди всех его документов, а не общая сумма накладных.
Разберем подробнее практические варианты решения этой проблемы.
Наиболее архитектурно правильный и производительный метод решения проблемы дублирования строк — предварительное сворачивание всех подчиненных таблиц перед выполнением основного соединения. Если к одной реализации может быть несколько возвратов, мы не должны соединять исходные таблицы документов «напрямую». Сначала сгруппируем возвраты по документу-основанию во временной таблице, чтобы кардинальность связи стала строгой («один-к-одному» или «один-к-нулю»).
Посмотрим на пример правильной структуры запроса с предварительным суммированием подчиненных данных:
ВЫБРАТЬ
Реализация.Контрагент КАК Контрагент,
Реализация.Ссылка КАК ДокументБУ,
Реализация.СуммаДокумента КАК СуммаБУ
ПОМЕСТИТЬ ВТ_Реализации
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
Реализация.Проведен = ИСТИНА;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Возврат.ДокументРеализации КАК ДокументБУ,
СУММА(Возврат.СуммаДокумента) КАК СуммаВозвратаБУ
ПОМЕСТИТЬ ВТ_ВозвратыСвернутые
ИЗ
Документ.ВозвратТоваровОтКлиента КАК Возврат
ГДЕ
Возврат.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
Возврат.ДокументРеализации;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Реализации.Контрагент КАК Контрагент,
ВТ_Реализации.ДокументБУ КАК ДокументБУ,
ВТ_Реализации.СуммаБУ КАК СуммаБУ,
ЕСТЬNULL(ВТ_ВозвратыСвернутые.СуммаВозвратаБУ, 0) КАК СуммаВозвратаБУ
ИЗ
ВТ_Реализации КАК ВТ_Реализации
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ВозвратыСвернутые КАК ВТ_ВозвратыСвернутые
ПО ВТ_Реализации.ДокументБУ = ВТ_ВозвратыСвернутые.ДокументБУ
ИТОГИ
СУММА(СуммаБУ),
СУММА(СуммаВозвратаБУ)
ПО
Контрагент,
ДокументБУ
В такой структуре количество детальных строк в выборке строго равно количеству исходных документов реализации. Соответственно, стандартная агрегатная функция СУММА() в секции ИТОГИ корректно посчитает общие суммы и по накладным, и по контрагентам, исключая декартово произведение.
Если изменить и переписать сложные соединения в запросе невозможно, решим задачу на этапе программного обхода результата. В запросе рассчитаем итог по документу с помощью агрегатной функции МАКСИМУМ() или СРЕДНЕЕ(). Это позволит получить неискаженную сумму в группировке второго уровня, а сумму верхнего уровня (по контрагенту) вычислим в коде встроенного языка путем суммирования показателей вложенных строк.
Разберем реализацию этого алгоритма по шагам:
ИТОГИ для поля суммы указываем функцию МАКСИМУМ(СуммаДокумента).ОбходРезультатаЗапроса.ПоГруппировкам.Посмотрим на пример кода обхода выборки:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Документы.Контрагент КАК Контрагент,
| Документы.Ссылка КАК ДокументБУ,
| Документы.СуммаДокумента КАК СуммаБУ
|ИЗ
| Документ.РеализацияТоваровУслуг КАК Документы
|ИТОГИ
| МАКСИМУМ(СуммаБУ) КАК СуммаБУ
|ПО
| Контрагент,
| ДокументБУ";
Результат = Запрос.Выполнить();
// Обходим группировку верхнего уровня (Контрагенты)
ВыборкаКонтрагент = Результат.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаКонтрагент.Следующий() Цикл
СуммаПоКонтрагенту = 0;
// Получаем выборку следующего уровня (Документы)
ВыборкаДокумент = ВыборкаКонтрагент.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаДокумент.Следующий() Цикл
// Накапливаем сумму без дублей
СуммаПоКонтрагенту = СуммаПоКонтрагенту + ВыборкаДокумент.СуммаБУ;
КонецЦикла;
// Значение СуммаПоКонтрагенту теперь содержит точную сумму всех документов
КонецЦикла;
Обращение напрямую к таблицам документов в запросах для построения отчетов считается неоптимальной практикой в 1С. В типовых конфигурациях финансовые показатели реализации и возвратов регистрируются в специализированных регистрах накопления (например, ВыручкаИСебестоимостьПродаж или Продажи). Применение виртуальной таблицы Обороты регистра накопления полностью решает проблему задвоения сумм.
Рассмотрим пример запроса к регистру накопления с детализацией до регистратора:
ВЫБРАТЬ
ПродажиОбороты.Регистратор КАК Регистратор,
ПродажиОбороты.Регистратор.Контрагент КАК Контрагент,
ПродажиОбороты.СуммаВыручкиОборот КАК СуммаВыручкиОборот
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, Регистратор, ) КАК ПродажиОбороты
ИТОГИ
СУММА(СуммаВыручкиОборот)
ПО
Контрагент
Виртуальная таблица оборотов сама выполняет агрегацию данных на уровне СУБД. В результате поле СуммаВыручкиОборот содержит корректные итоговые показатели как по конкретному документу-регистратору, так и в целом по контрагенту при расчете итогов стандартной функцией СУММА().