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