Как правильно объединить таблицы продаж и возвратов в СКД и рассчитать процент возврата без задвоения итогов?

Мы часто сталкиваемся с необходимостью построения сложных аналитических отчетов, объединяющих разнородные данные. Типичный пример из торговой практики — расчет процента возвратов товаров относительно объема продаж. На первый взгляд задача кажется простой: достаточно получить данные по продажам, данные по возвратам, соединить их по номенклатуре и рассчитать процент. Однако при реализации этого механизма в Системе компоновки данных (СКД) разработчики часто сталкиваются с неприятной проблемой — задвоением (или кратным завышением) итогов по продажам, если по одной и той же номенклатуре было несколько разных причин возврата.

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

Выясняем причину: почему левое соединение в одном запросе «ломает» итоги

Рассмотрим классическую ситуацию. У нас есть две таблицы данных за определенный период:

1. Таблица «Возвраты» (детализированная по номенклатуре и причинам возврата):
   • Стул офисный — Причина: «Брак» — Возврат: 5 шт.
   • Стул офисный — Причина: «Отказ клиента» — Возврат: 10 шт.
2. Таблица «Продажи» (детализированная только по номенклатуре):
   • Стул офисный — Продано всего: 100 шт.

Если мы попытаемся объединить эти таблицы обычным левым соединением (ЛЕВОЕ СОЕДИНЕНИЕ) внутри одного SQL-запроса по полю Номенклатура, СУБД вернет нам следующую плоскую таблицу:

• Строка 1: Стул офисный | Причина: «Брак» | Возврат: 5 шт. | Продано: 100 шт.
• Строка 2: Стул офисный | Причина: «Отказ» | Возврат: 10 шт. | Продано: 100 шт.

Когда СКД начнет рассчитывать общие итоги (Grand Total) по этой таблице, она применит стандартную агрегатную функцию СУММА(Продано). В результате в итоговой строке мы увидим объем продаж, равный 200 шт., хотя на самом деле было продано всего 100 стульев. Произошло физическое дублирование (задвоение) строк на уровне СУБД, исправить которое простым суммированием в ресурсах невозможно.

Решение 1: Разделение запроса на два набора данных в СКД

Самым простым, красивым и методологически верным способом решения этой проблемы является разделение одного «тяжелого» запроса на два независимых набора данных на уровне схемы компоновки данных.

Рассмотрим по шагам, как это настроить:

Шаг 1. Создание Набора данных №1 (Возвраты)

В схеме СКД добавим набор данных - запрос с именем НаборДанныхВозвраты. Этот запрос будет собирать информацию о возвратах из документов или регистров:

ВЫБРАТЬ
    ВозвратТоваров.ПричинаВозврата КАК ПричинаВозврата,
    ВозвратТоваров.Номенклатура КАК Номенклатура,
    СУММА(ВозвратТоваров.Сумма) КАК ВозвратСумма,
    СУММА(ВозвратТоваров.Количество) КАК ВозвратКоличество
ИЗ
    Документ.ВозвратТоваровОтПокупателя.Товары КАК ВозвратТоваров
ГДЕ
    ВозвратТоваров.Ссылка.Проведен = ИСТИНА
    И ВозвратТоваров.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
    ВозвратТоваров.ПричинаВозврата,
    ВозвратТоваров.Номенклатура

Шаг 2. Создание Набора данных №2 (Продажи)

Добавим второй набор данных - запрос с именем НаборДанныхПродажи. Он будет получать чистые продажи из оборотного регистра накопления:

ВЫБРАТЬ
    ПродажиОбороты.Номенклатура КАК Номенклатура,
    ПродажиОбороты.КоличествоОборот КАК ПродажаКоличество,
    ПродажиОбороты.СтоимостьОборот КАК ПродажаСумма
ИЗ
    РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ПродажиОбороты

Шаг 3. Настройка связей наборов данных

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

• Источник связи: НаборДанныхВозвраты
• Приемник связи: НаборДанныхПродажи
• Условие связи: Номенклатура = Номенклатура

Важный нюанс: Чтобы связь работала эффективно и не вызывала проблем с производительностью (известная проблема «запроса в цикле»), во втором наборе данных рекомендуется установить условие фильтрации ГДЕ Номенклатура В (&СписокНоменклатуры). А на вкладке связи указать в поле «Параметры» имя параметра СписокНоменклатуры и обязательно поставить галочку «Список параметров». Платформа сама соберет уникальный массив товаров из первого набора и передаст его одним пакетом во второй запрос.

Также рекомендуется в первом наборе данных для поля Номенклатура в настройках полей набора установить роль «Обязательное», чтобы связь корректно инициировалась при построении дерева группировок.

Почему СКД не «задваивает» итоги при связи наборов?

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

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

Золотое правило расчета процентов в ресурсах СКД

Частой ошибкой начинающих разработчиков является расчет процентного соотношения непосредственно в тексте запроса или добавление простого вычисляемого поля со статичным расчетом деления.

Если мы напишем формулу расчета процента как ВозвратКоличество * 100 / ПродажаКоличество в вычисляемом поле, то на детальных записях расчет выполнится правильно. Но при расчете итогов СКД попытается просто сложить эти проценты по строкам (например, 5% + 10% = 15%), что математически не имеет никакого смысла.

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

Перейдем на вкладку «Ресурсы» и добавим наши показатели:

1. Для ресурса ВозвратКоличество выражение: Сумма(ВозвратКоличество)
2. Для ресурса ПродажаКоличество выражение: Сумма(ПродажаКоличество)
3. Для нового вычисляемого поля ПроцентКоличество выражение расчета ресурса запишем следующим образом:

ВЫБОР
    КОГДА ЕСТЬNULL(Сумма(ПродажаКоличество), 0) <> 0
        ТОГДА Сумма(ВозвратКоличество) * 100 / Сумма(ПродажаКоличество)
    ИНАЧЕ 0
КОНЕЦ

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

Альтернативный подход: Использование «Набора данных - Объединение»

Существует еще один профессиональный архитектурный паттерн в СКД, который позволяет полностью избежать горизонтального связывания таблиц. Это использование виртуального набора типа «Объединение».

Рассмотрим, как спроектировать схему по этому принципу:

1. Создаем два независимых набора-запроса: `НаборПродажи` и `НаборВозвраты`.
2. Создаем корневой набор данных типа «Объединение» и перетаскиваем наши два запроса внутрь него.
3. Настраиваем соответствие полей:
   • Поле Номенклатура присутствует в обоих наборах и сопоставляется автоматически.
   • Поле ПричинаВозврата присутствует только в возвратах. В наборе продаж его просто не будет (для строк продаж оно примет значение NULL).
   • Ресурс ПродажаКоличество выбирается только в наборе продаж.
   • Ресурс ВозвратКоличество выбирается только в наборе возвратов.

При таком подходе СКД выполнит объединение таблиц по вертикали (аналог UNION ALL). Строки продаж и возвратов будут находиться в разных записях, что гарантирует физическую невозможность задвоения показателей при группировке. СКД сама схлопнет эти данные на уровне группировки Номенклатура.

Ограничение ресурсов для группировок

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

Чтобы не путать пользователей выводом нераспределяемых данных на неподходящих уровнях, мы можем управлять расчетом ресурсов. В настройках ресурса ПродажаКоличество на вкладке «Ресурсы» в колонке «Рассчитывать по...» мы можем четко перечислить только те группировки, для которых этот показатель имеет смысл (например, указать только Номенклатура и Общий итог, исключив группировку ПричинаВозврата). Тогда на уровне причин ячейка продаж просто останется пустой, а в итогах отразится верная сумма.