Почему меняется или увеличивается сумма в запросе 1С при ЛЕВОМ СОЕДИНЕНИИ и как это исправить?

← На главную

При проектировании сложных отчетов на платформе 1С:Предприятие разработчики часто сталкиваются с ситуацией, когда итоговые числовые показатели (суммы, количество) в запросе начинают вести себя непредсказуемо после добавления новых связей. Классический пример: мы добавляем ЛЕВОЕ СОЕДИНЕНИЕ для получения дополнительного свойства или аналитики из регистра сведений, и вдруг итоговая сумма по документу или номенклатуре увеличивается в несколько раз.

В этой статье мы подробно разберем, почему левое соединение способно искажать числовые итоги, проанализируем реальный пример с форума и рассмотрим несколько надежных архитектурных решений этой проблемы.

Выясняем причину: почему ЛЕВОЕ СОЕДИНЕНИЕ размножает строки

Существует распространенное заблуждение, что ЛЕВОЕ СОЕДИНЕНИЕ гарантирует неизменность количества строк и сумм левой (основной) таблицы. Это справедливо только в одном случае: если связь между левой и правой таблицами имеет отношение "один к одному" (1:1) или "многие к одному" (N:1).

Если же связь оказывается типа "один ко многим" (1:N), СУБД выполняет декартово произведение для неидентичных строк. На каждую одну строку из левой таблицы система найдет несколько подходящих строк в правой таблице. В результате строка левой таблицы продублируется ровно столько раз, сколько соответствий нашлось справа.

Проанализируем ситуацию автора темы. В его исходном запросе рассчитывалась сумма по полю ОбщийДоходПоСпециальности во временной таблице ДоходПоСпециальностям. На этом этапе выборка содержала всего 40 строк с общей суммой около 140 миллионов рублей. После этого было добавлено левое соединение с виртуальной таблицей среза последних регистра сведений:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.БиТ_НагрузкаПоСпециальностям.СрезПоследних(&Период, ) КАК БиТ_НагрузкаПоСпециальностямСрезПоследних
ПО ДоходПоСпециальностям.Специальность = БиТ_НагрузкаПоСпециальностямСрезПоследних.Специальность

Поскольку в регистре сведений БиТ_НагрузкаПоСпециальностям для одной и той же специальности было введено несколько записей в разрезе других измерений (например, разные подразделения ЦФО или учебные группы), СУБД размножила исходные 40 строк доходов до 371 строки. Каждая строка дохода продублировалась для каждого ЦФО. В итоге общая сумма по полю ОбщийДоходПоСпециальности выросла до 1,38 миллиарда рублей, так как одни и те же показатели доходов сложились многократно.

Способ 1. Предварительное сворачивание (группировка) присоединяемой таблицы

Самый простой способ избежать размножения строк на уровне чистого SQL-запроса — это гарантировать, что правая таблица будет содержать строго одну уникальную строку по ключевым полям связи.

Если нам необходимо получить данные из регистра сведений, где возможны дубли по ключевому полю (например, по полю Специальность), мы должны предварительно сгруппировать эти данные во временной таблице. Рассмотрим, как это сделать по шагам.

Выбираем данные из регистра во временную таблицу.
Группируем записи по ключевому полю связи, агрегируя остальные поля (например, получая средний коэффициент, максимальную дату или сумму).
Соединяем основную таблицу уже со свернутой временной таблицей.

Посмотрим на пример реализации такого подхода в запросе:


// Шаг 1 и 2: Получаем уникальные коэффициенты по специальностям
ВЫБРАТЬ
    Нагрузка.Специальность КАК Специальность,
    МАКСИМУМ(Нагрузка.КоэффициентВклада) КАК КоэффициентВклада
ПОМЕСТИТЬ ВТ_СвернутаяНагрузка
ИЗ
    РегистрСведений.БиТ_НагрузкаПоСпециальностям.СрезПоследних(&Период, ) КАК Нагрузка
СГРУППИРОВАТЬ ПО
    Нагрузка.Специальность
;

// Шаг 3: Безопасное левое соединение 1:1
ВЫБРАТЬ
    Доход.Специальность КАК Специальность,
    Доход.ОбщийДоходПоСпециальности КАК ОбщийДоходПоСпециальности,
    ЕСТЬNULL(Нагрузка.КоэффициентВклада, 0) КАК КоэффициентВклада
ИЗ
    ДоходПоСпециальностям КАК Доход
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СвернутаяНагрузка КАК Нагрузка
    ПО Доход.Специальность = Нагрузка.Специальность

Благодаря предварительной группировке во временной таблице ВТ_СвернутаяНагрузка гарантированно не будет дублирующихся строк по полю Специальность. Сумма доходов останется абсолютно корректной.

Способ 2. Использование Системы компоновки данных (СКД)

Если вы разрабатываете отчет на базе СКД, соединять таблицы запросом для решения проблемы дублирования суммарных показателей — это грубая методическая ошибка. Платформа 1С содержит мощный встроенный инструмент, предназначенный именно для таких сценариев — Связи наборов данных.

Разберем алгоритм настройки связи наборов данных в СКД:

Создаем НаборДанных1 (запрос), в котором выбираем только основные данные (доходы по специальностям). Рассчитываем сумму доходов.
Создаем отдельный НаборДанных2 (запрос), в котором выбираем данные по нагрузкам (из регистра сведений).
Переходим на вкладку "Связи наборов данных".
Добавляем новую строку связи: Источник связи — НаборДанных1, Приемник связи — НаборДанных2. В качестве условия связи (выражение источника и приемника) указываем поле Специальность.
На вкладке "Ресурсы" добавляем поле ОбщийДоходПоСпециальности с функцией Сумма(ОбщийДоходПоСпециальности).

При такой архитектуре процессор вывода компоновки данных самостоятельно сопоставит строки. Детальные записи по нагрузкам будут выведены корректно, но итоговые ресурсы по доходам не будут удваиваться или размножаться, так как СКД рассчитывает итоги по первичному набору данных.

Способ 3. Расчет ресурсов СКД по группировкам

Иногда обойти соединение таблиц в одном запросе невозможно по технологическим причинам. В этом случае мы можем скорректировать расчет итогов непосредственно в настройках ресурсов компоновки данных.

Для этого используется агрегатная функция СКД ВычислитьВыражениеСГруппировкойМассива. Рассмотрим подробнее выражение, которое необходимо прописать в поле "Выражение" для ресурса ОбщийДоходПоСпециальности:


Сумма(ВычислитьВыражениеСГруппировкойМассива("Максимум(ОбщийДоходПоСпециальности)", "Специальность"))

Проанализируем, как работает эта конструкция: система сначала сгруппирует данные по полю Специальность, выберет максимальное значение дохода для каждой уникальной специальности (тем самым проигнорировав дубли, возникшие из-за правого соединения), а затем просуммирует эти уникальные значения для расчета общих итогов отчета.

Скрытые ловушки при работе с ЛЕВЫМ СОЕДИНЕНИЕМ

Помимо размножения строк, разработчики при использовании связей часто совершают еще две критические ошибки:

1. Превращение левого соединения во внутреннее. Если вы наложите условие на поля правой таблицы в секции ГДЕ, левое соединение автоматически превратится во внутреннее. Например:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.БиТ_НагрузкаПоСпециальностям КАК Нагрузка
ПО Доход.Специальность = Нагрузка.Специальность
ГДЕ Нагрузка.ЦФО = &ПараметрЦФО // ОШИБКА! Строки с NULL будут отброшены.

Для тех специальностей, по которым нет записей в регистре нагрузок, поле ЦФО вернет значение NULL. Сравнение NULL = &ПараметрЦФО вернет ложь, и эти специальности вообще пропадут из результата. Правильно накладывать условия на правую таблицу непосредственно внутри параметров виртуальной таблицы или в секции связи ПО:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.БиТ_НагрузкаПоСпециальностям.СрезПоследних(&Период, ЦФО = &ПараметрЦФО) КАК Нагрузка
ПО Доход.Специальность = Нагрузка.Специальность

2. Игнорирование значений NULL в расчетах. При выполнении арифметических операций с полями из правой таблицы обязательно используйте функцию ЕСТЬNULL. Помните, что любая математическая операция с неопределенным значением (например, Доход * NULL) вернет NULL, что приведет к полной потере итоговой суммы по строке.

Используйте безопасное приведение типов:

ЕСТЬNULL(Нагрузка.КоэффициентВклада, 0)