При разработке отчетов в системе 1С:Предприятие часто возникает задача вывести дополнительную колонку, которая рассчитывается на основе двух других колонок (например, вычислить чистую прибыль как разность между продажами и возвратами). На первый взгляд, задача кажется тривиальной — достаточно написать простое арифметическое выражение в поле выбора. Однако на практике разработчики сталкиваются с целым рядом неочевидных проблем: искажением итоговых сумм, некорректным поведением группировок, исчезновением данных из-за значений NULL или аварийным завершением работы Конструктора запросов.
В этой статье мы подробно разберем, почему возникают эти ошибки, проанализируем "подводные камни" связывания таблиц документов и рассмотрим несколько надежных способов реализации расчетов: от классической группировки до использования временных таблиц и механизмов СКД.
Рассмотрим типичный сценарий, с которым столкнулся автор темы. В запросе выбираются данные о продажах и возвратах в разрезе контрагентов. Для расчета разности автор попытался использовать следующую конструкцию:
РеализацияТоваров.СуммаДокумента - ВложенныйЗапрос.СуммаВозвратов КАК Поле1
После добавления этого поля запрос начал выводить некорректные данные, а Конструктор запроса принудительно добавил это выражение в секцию СГРУППИРОВАТЬ ПО. Выясним, почему это произошло. Здесь сработали сразу три классические проблемы реляционных баз данных и языка запросов 1С.
Проблема №1: Ошибки группировки неагрегированных полей. Если в вашем запросе используется агрегирование (функция СУММА, КОЛИЧЕСТВО и т.д.), то все остальные поля, выбираемые в секции ВЫБРАТЬ, обязаны находиться в секции СГРУППИРОВАТЬ ПО. В исходном запросе автора поле РеализацияТоваров.СуммаДокумента не было обернуто в агрегатную функцию СУММА() в вычисляемом выражении, хотя сама по себе сумма продаж в другой колонке рассчитывалась как СУММА(РеализацияТоваров.СуммаДокумента). Из-за этого СУБД была вынуждена группировать детальные записи по значению разности, что полностью разрушило логику сворачивания данных по контрагентам.
Проблема №2: Коварное поведение значений NULL. При левом соединении таблиц (например, когда мы присоединяем возвраты к продажам), у контрагентов, у которых не было возвратов, в поле СуммаВозвратов вернется значение NULL. В языке запросов 1С любая арифметическая операция со значением NULL (будь то сложение, вычитание, умножение или деление) всегда возвращает результат NULL. Таким образом, выражение Продажи - NULL превращается в пустоту, и вы вообще теряете данные по продажам для таких контрагентов.
Проблема №3: Эффект декартова произведения (дублирование строк). Это самая опасная логическая ошибка. Когда вы соединяете две таблицы документов напрямую (например, документы реализации и возвраты по контрагенту), СУБД сопоставляет каждую строку первой таблицы с каждой строкой второй таблицы. Если у контрагента было 5 реализаций и 3 возврата, в результате соединения получится 15 строк. При суммировании полей значения продаж и возвратов исказятся и увеличатся в несколько раз. Итоговый результат отчета превратится в "бредятину".
Ниже мы подробно разберем профессиональные способы решения этих проблем.
Если вам необходимо решить задачу в рамках одного простого запроса, ключевое правило звучит так: сначала выполните арифметическую операцию над полями внутри агрегатной функции, обязательно обработав возможные пустые значения функцией ЕСТЬNULL.
Проанализируем правильный вариант написания полей выбора:
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
СУММА(ЕСТЬNULL(РеализацияТоваров.СуммаДокумента, 0)) КАК СуммаПродаж,
СУММА(ЕСТЬNULL(ВложенныйЗапрос.СуммаВозвратов, 0)) КАК СуммаВозвратов,
СУММА(ЕСТЬNULL(РеализацияТоваров.СуммаДокумента, 0) - ЕСТЬNULL(ВложенныйЗапрос.СуммаВозвратов, 0)) КАК ЧистаяВыручка
ИЗ
Справочник.Контрагенты КАК Контрагенты
...
СГРУППИРОВАТЬ ПО
Контрагенты.Ссылка
Разберем этот пример по шагам:
ЕСТЬNULL(Поле, 0). Теперь, если возвратов не было, система подставит число 0, и расчет выполнится корректно.СУММА(...). Это избавляет нас от необходимости добавлять расчетное выражение в секцию СГРУППИРОВАТЬ ПО. СУБД сначала рассчитает разность для каждой детальной строки, а затем корректно свернет ее по группировочным полям (в данном случае — по контрагенту).Хотя первый способ исправляет синтаксические ошибки, он не решает проблему дублирования строк при соединении негруппированных таблиц документов. Чтобы отчет работал быстро и выдавал абсолютно точные данные, необходимо изолировать расчеты. Именно к этому решению пришел автор темы в финале обсуждения.
Рассмотрим по шагам схему построения пакета запросов с временными таблицами:
Шаг 1. Создаем временную таблицу для продаж. Мы выбираем данные из документов реализации, сразу группируем их по контрагенту и сохраняем сжатый результат во временную таблицу ВТ_Продажи. Здесь на одного контрагента гарантированно будет приходиться ровно одна строка.
ВЫБРАТЬ
Реализация.Контрагент КАК Контрагент,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Реализация.Ссылка) КАК КоличествоПокупок,
СУММА(Реализация.СуммаДокумента) КАК СуммаПродаж
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
Документ.РеализацияТоваров КАК Реализация
ГДЕ
Реализация.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
И Реализация.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
Реализация.Контрагент
;
Шаг 2. Создаем временную таблицу для возвратов. Аналогично собираем данные по возвратам во временную таблицу ВТ_Возвраты, также сворачивая их до одного контрагента.
ВЫБРАТЬ
Возврат.Контрагент КАК Контрагент,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Возврат.Ссылка) КАК КоличествоВозвратов,
СУММА(Возврат.СуммаДокумента) КАК СуммаВозвратов
ПОМЕСТИТЬ ВТ_Возвраты
ИЗ
Документ.ВозвратТоваровОтПокупателя КАК Возврат
ГДЕ
Возврат.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
И Возврат.Проведен = ИСТИНА
СГРУППИРОВАТЬ ПО
Возврат.Контрагент
;
Шаг 3. Соединяем подготовленные таблицы и рассчитываем разность. Теперь мы берем за основу справочник контрагентов (или таблицу продаж) и левым соединением подключаем наши временные таблицы. Поскольку в обеих временных таблицах данные уже уникальны в разрезе контрагентов, никакого умножения строк не произойдет. Мы можем безопасно вычитать показатели.
ВЫБРАТЬ
Контрагенты.Ссылка КАК Контрагент,
ЕСТЬNULL(Продажи.КоличествоПокупок, 0) КАК КоличествоПокупок,
ЕСТЬNULL(Возвраты.КоличествоВозвратов, 0) КАК КоличествоВозвратов,
ЕСТЬNULL(Продажи.СуммаПродаж, 0) КАК СуммаПродаж,
ЕСТЬNULL(Возвраты.СуммаВозвратов, 0) КАК СуммаВозвратов,
ЕСТЬNULL(Продажи.СуммаПродаж, 0) - ЕСТЬNULL(Возвраты.СуммаВозвратов, 0) КАК ЧистаяВыручка
ИЗ
Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Продажи
ПО Контрагенты.Ссылка = Продажи.Контрагент
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Возвраты КАК Возвраты
ПО Контрагенты.Ссылка = Возвраты.Контрагент
Этот подход не только гарантирует 100% точность расчетов, но и работает значительно быстрее на больших базах данных, так как СУБД проще построить оптимальный план выполнения для нескольких простых последовательных запросов, чем для одного сложного вложенного соединения.
Еще один профессиональный паттерн проектирования запросов в 1С — это объединение потоков данных с последующей единой группировкой. Этот метод позволяет полностью отказаться от тяжелых соединений JOIN, заменяя их быстрой операцией UNION ALL.
Идея заключается в том, что мы выбираем продажи с положительным знаком, а возвраты — с отрицательным (или просто разносим их по разным колонкам одного плоского потока), а затем группируем все записи один раз.
ВЫБРАТЬ
Выборка.Контрагент КАК Контрагент,
СУММА(Выборка.СуммаПродаж) КАК СуммаПродаж,
СУММА(Выборка.СуммаВозвратов) КАК СуммаВозвратов,
СУММА(Выборка.СуммаПродаж - Выборка.СуммаВозвратов) КАК ЧистаяВыручка
ИЗ
(ВЫБРАТЬ
Реализация.Контрагент КАК Контрагент,
Реализация.СуммаДокумента КАК СуммаПродаж,
0 КАК СуммаВозвратов
ИЗ
Документ.РеализацияТоваров КАК Реализация
ГДЕ
Реализация.Проведен = ИСТИНА
И Реализация.Дата МЕЖДУ &ДатаНачала И &ДатаКонца
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Возврат.Контрагент,
0,
Возврат.СуммаДокумента
ИЗ
Документ.ВозвратТоваровОтПокупателя КАК Возврат
ГДЕ
Возврат.Проведен = ИСТИНА
И Возврат.Дата МЕЖДУ &ДатаНачала И &ДатаКонца) КАК Выборка
СГРУППИРОВАТЬ ПО
Выборка.Contragent
Этот запрос работает невероятно быстро, так как СУБД выполняет линейное сканирование таблиц без создания вложенных циклов сопоставления строк.
Если вы разрабатываете отчет на базе СКД, то выполнение чисто математических вычислений (таких как сложение или вычитание колонок) непосредственно в тексте SQL-запроса считается не лучшей практикой. Для этого в компоновщике предусмотрены штатные инструменты, которые автоматически учитывают иерархию, группировки и корректно рассчитывают общие итоги.
Для реализации расчета средствами СКД выполните следующие шаги:
СуммаПродаж и СуммаВозвратов. Не пишите формулу вычитания в самом запросе.ЧистаяВыручка) и введите выражение: ЕСТЬNULL(СуммаПродаж, 0) - ЕСТЬNULL(СуммаВозвратов, 0).СУММА(ЧистаяВыручка).Благодаря этому СКД сама позаботится о расчете разности на каждом уровне группировки отчета — будь то детальная строка по конкретному договору или общие итоги по всей компании. При расчете формулы в самом запросе итоги по группировкам часто отображаются некорректно.
В реальных рабочих базах данных (особенно в конфигурациях уровня УТ 11, ERP, КА или БП 3.0) обращение напрямую к таблицам документов вроде Документ.РеализацияТоваров считается серьезной ошибкой проектирования. Документы могут быть проведены, но не делать движений по учету, либо операции могут корректироваться ручными операциями.
Для получения информации о продажах и возвратах всегда следует использовать **Виртуальные таблицы оборотов** соответствующих регистров накопления (например, регистра Продажи или ВыручкаИСебестоимостьПродаж). Данные в этих таблицах уже сгруппированы СУБД, оптимизированы для построения отчетов и содержат готовые аналитические разрезы.
Посмотрим на пример запроса к виртуальной таблице оборотов:
ВЫБРАТЬ
ПродажиОбороты.АналитикаУчетаНоменклатуры.Партнер КАК Контрагент,
СУММА(ПродажиОбороты.СуммаВыручкиОборот) КАК СуммаПродаж,
СУММА(ПродажиОбороты.СуммаНДСОборот) КАК СуммаНДС,
СУММА(ПродажиОбороты.СуммаВыручкиОборот - ПродажиОбороты.СуммаНДСОборот) КАК ЧистаяВыручкаБезНДС
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&ДатаНачала, &ДатаКонца, Авто, ) КАК ПродажиОбороты
СГРУППИРОВАТЬ ПО
ПродажиОбороты.АналитикаУчетаНоменклатуры.Партнер
Использование готовых регистров избавляет вас от необходимости самостоятельно связывать продажи и возвраты, так как в регистре продаж возвраты покупателей обычно уже отражаются со знаком "минус" или фиксируются в рамках одной структуры данных, что сводит написание отчета к паре простых строк кода.