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