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