Как исправить задвоение суммы в запросах СКД при соединении таблиц

← На главную

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

Почему происходит задвоение данных?

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

Выясним основную причину: разная степень детализации аналитики в объединяемых таблицах. Выручка может храниться в разрезе чеков, а себестоимость — в разрезе регламентных документов или бухгалтерских проводок. При соединении «многие ко многим» или «один ко многим» без предварительной подготовки данных мы получаем избыточное количество строк.

Способ 1. Использование группировки в запросе

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

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


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    РезультатДоход.ПериодМесяц,
    РезультатДоход.Организация,
    РезультатДоход.Подразделение,
    РезультатДоход.Сумма КАК СуммаДохода,
    СУММА(ЕСТЬNULL(ВТ_Себестоимость.СуммаОборотДт, 0)) КАК СуммаСебестоимости
ПОМЕСТИТЬ Результат
ИЗ
    РезультатДоход КАК РезультатДоход
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Себестоимость КАК ВТ_Себестоимость
ПО РезультатДоход.Подразделение = ВТ_Себестоимость.Подразделение
СГРУППИРОВАТЬ ПО
    РезультатДоход.ПериодМесяц,
    РезультатДоход.Организация,
    РезультатДоход.Подразделение,
    РезультатДоход.Сумма;

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

Способ 2. Разделение на разные наборы данных (рекомендуемый)

Использование связей наборов данных внутри самой СКД — это наиболее «правильный» архитектурный подход в 1С. Платформа обрабатывает такие связи иначе, чем классический SQL-запрос. При использовании двух разных наборов данных СКД сначала получает итоги по каждому из них по отдельности, а затем сопоставляет их в памяти. Это полностью исключает геометрическое размножение строк.

Рассмотрим алгоритм настройки этого метода:

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

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

Способ 3. Работа с ролями полей и ресурсами

Проанализируем ситуацию, когда мы не можем разделить наборы данных. В СКД есть механизмы, позволяющие управлять тем, как суммируются данные. На закладке «Наборы данных» для каждого поля можно настроить Роль. Если указать для полей аналитики роль «Измерение», СКД будет лучше понимать структуру данных при расчете итогов.

Также иногда применяют «хитрость» в определении ресурсов. Если мы знаем, что сумма задваивается из-за того, что к одной строке выручки присоединилось 10 строк себестоимости с одинаковой суммой, мы можем использовать функции Максимум() или Среднее() вместо Сумма() в настройках ресурсов. Однако это считается временным решением («костылем»), так как оно может давать неверные итоги на уровнях группировок.

Резюме

Для предотвращения задвоения сумм в СКД придерживайтесь следующих правил:

Проверяйте детальность: если вы соединяете данные разной периодичности или разной аналитики (например, документ и остатки по складу), задвоение практически неизбежно при обычном JOIN.
Используйте связи наборов данных: это самый мощный механизм СКД, созданный специально для корректного объединения данных разной структуры.
Агрегируйте заранее: если используете временные таблицы, старайтесь СГРУППИРОВАТЬ данные во временной таблице до того, как начнете соединять её с основной выборкой.
Правильно настраивайте ресурсы: вычисления разницы между показателями всегда должны идти над агрегатными функциями (сумма разностей должна быть разностью сумм).

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