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