Как объединить данные из двух регистров накопления без дублирования характеристик в 1С

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

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

Анализ исходной проблемы: почему двоятся строки?

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

• ХарактеристикаНоменклатуры — разрез учета товаров.
• Штрихкод — идентификатор из регистра сведений.
• Поступило (количество) — данные из регистра прихода.
• Реализовано (количество) — данные из регистра расхода.

Автор попытался решить задачу «в лоб» через объединение двух выборок с помощью оператора ОБЪЕДИНИТЬ ВСЕ, связав каждую из частей с регистром сведений РегистрСведений.Штрихкоды через ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Однако этот подход привел к дублированию характеристик номенклатуры.

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

1. Природа оператора ОБЪЕДИНИТЬ ВСЕ. Этот оператор предназначен для механического сложения результатов двух запросов «друг под друга». Если по одной и той же характеристике номенклатуры происходило и поступление, и реализация, СУБД вернет как минимум две строки. В первой строке будет заполнено поле прихода, а в поле расхода будет стоять NULL, а во второй строке — наоборот. Без последующей группировки эти строки никогда не «схлопнутся».
2. Группировка по полю Период. В детальных записях обоих запросов присутствовало поле Период. Поскольку приходные и расходные накладные проводятся в абсолютно разное время, группировка по периоду заставляет систему выводить каждую транзакцию отдельной строкой. Для итогового отчета за период эта детальная информация избыточна и мешает агрегации.
3. Проблема использования значений NULL в математических операциях. В исходном запросе в качестве пустых полей для суммирования использовались литералы NULL. При попытке сложить числовое значение с NULL в СУБД результатом всегда будет NULL. Это препятствует корректному сворачиванию данных при группировках. Как справедливо отметили участники обсуждения, все значения NULL в числовых колонках необходимо заменять на нули.
4. Опасность дублирования из-за регистра «Штрихкоды». Внутреннее соединение с регистром сведений Штрихкоды по характеристике номенклатуры таит скрытую угрозу. Если для одной характеристики в базе зарегистрировано несколько штрихкодов (например, для разных видов упаковки, штучный и групповой штрихкоды), СУБД размножит строки с суммами пропорционально количеству штрихкодов. В итоге показатели прихода и расхода будут искусственно завышены.

Решение №1: Быстрое исправление с помощью вложенного запроса

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

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

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

ВЫБРАТЬ
    ВложенныйЗапрос.Штрихкод КАК Штрихкод,
    ВложенныйЗапрос.Контрагент КАК Контрагент,
    ВложенныйЗапрос.Склад КАК Склад,
    ВложенныйЗапрос.Номенклатура КАК Номенклатура,
    ВложенныйЗапрос.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    СУММА(ВложенныйЗапрос.Приход) КАК Приход,
    СУММА(ВложенныйЗапрос.Расход) КАК Расход
ИЗ
    (ВЫБРАТЬ
        ПОДСТРОКА(Штрихкоды.Штрихкод, 1, 13) КАК Штрихкод,
        ПоступлениеТоваров.Контрагент КАК Контрагент,
        ПоступлениеТоваров.Склад КАК Склад,
        ПоступлениеТоваров.Номенклатура КАК Номенклатура,
        ПоступлениеТоваров.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
        ПоступлениеТоваров.Количество КАК Приход,
        0 КАК Расход
    ИЗ
        РегистрНакопления.ПоступлениеТоваров КАК ПоступлениеТоваров
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
            ПО ПоступлениеТоваров.ХарактеристикаНоменклатуры = Штрихкоды.ХарактеристикаНоменклатуры

    ОБЪЕДИНИТЬ ВСЕ

    ВЫБРАТЬ
        ПОДСТРОКА(Штрихкоды.Штрихкод, 1, 13),
        РеализацияТоваров.Контрагент,
        РеализацияТоваров.Склад,
        РеализацияТоваров.Номенклатура,
        РеализацияТоваров.ХарактеристикаНоменклатуры,
        0,
        РеализацияТоваров.Количество
    ИЗ
        РегистрНакопления.РеализацияТоваров КАК РеализацияТоваров
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
            ПО РеализацияТоваров.ХарактеристикаНоменклатуры = Штрихкоды.ХарактеристикаНоменклатуры) КАК ВложенныйЗапрос

СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Штрихкод,
    ВложенныйЗапрос.Контрагент,
    ВложенныйЗапрос.Склад,
    ВложенныйЗапрос.Номенклатура,
    ВложенныйЗапрос.ХарактеристикаНоменклатуры
УПОРЯДОЧИТЬ ПО
    Штрихкод,
    ХарактеристикаНоменклатуры

Этот запрос успешно решает проблему дублирования строк на уровне детальных записей, поскольку окончательная группировка происходит на самом верхнем уровне и "схлопывает" строки с одинаковыми характеристиками и штрихкодами.

Решение №2: Профессиональный подход по стандартам «1С» (Временные таблицы и виртуальные таблицы оборотов)

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

Недостатки использования вложенных запросов и физических таблиц:

• Проблемы оптимизации СУБД. Оптимизатор СУБД (будь то MS SQL Server, PostgreSQL или Oracle) не может точно спрогнозировать объем данных, который вернет вложенный запрос. В результате строится неоптимальный план выполнения, что ведет к колоссальным просадкам производительности на больших объемах данных.
• Чтение физических таблиц. В исходном коде запросы обращаются к физическим таблицам регистров накопления (РегистрНакопления.ПоступлениеТоваров). Это заставляет СУБД сканировать все записи движений, вместо того чтобы использовать предварительно рассчитанные итоги.
• Внутреннее соединение (INNER JOIN). Использование внутреннего соединения с регистром штрихкодов приведет к тому, что товары, у которых в базе не заполнен штрихкод, вообще не попадут в отчет. Пользователи отчета не увидят объемы поступлений и продаж по таким позициям, что является критической ошибкой бизнес-логики.

Рассмотрим правильный, профессиональный алгоритм решения этой задачи, состоящий из трех последовательных шагов (пакетов запроса):

1. Шаг 1: Сбор поступлений во временную таблицу. Мы обращаемся к виртуальной таблице оборотов регистра поступлений (например, РегистрНакопления.Закупки.Обороты или аналогичному регистру в вашей конфигурации). Извлекаем данные, группируем их и помещаем во временную таблицу ВТ_Поступления.
2. Шаг 2: Сбор продаж во временную таблицу. Аналогичным образом получаем данные по продажам из виртуальной таблицы оборотов регистра продаж и сохраняем их во временную таблицу ВТ_Продажи.
3. Шаг 3: Объединение данных и левое соединение со штрихкодами. В результирующем запросе пакета мы объединяем данные из двух временных таблиц через ОБЪЕДИНИТЬ ВСЕ, группируем их, чтобы исключить дубли, и только после этого связываем результат через ЛЕВОЕ СОЕДИНЕНИЕ с регистром сведений Штрихкоды. Это гарантирует, что в отчете появятся даже те товары, у которых штрихкод отсутствует.

Посмотрим на пример оптимального пакетного запроса:

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

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

// ШАГ 3: Объединяем, сворачиваем данные и присоединяем штрихкоды
ВЫБРАТЬ
    ЕСТЬNULL(Штрихкоды.Штрихкод, "") КАК Штрихкод,
    ОбъединенныеДанные.Склад КАК Склад,
    ОбъединенныеДанные.Контрагент КАК Контрагент,
    ОбъединенныеДанные.Номенклатура КАК Номенклатура,
    ОбъединенныеДанные.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    СУММА(ОбъединенныеДанные.Приход) КАК Приход,
    СУММА(ОбъединенныеДанные.Расход) КАК Расход
ИЗ
    (ВЫБРАТЬ
        ВТ_Поступления.Склад КАК Склад,
        ВТ_Поступления.Контрагент КАК Контрагент,
        ВТ_Поступления.Номенклатура КАК Номенклатура,
        ВТ_Поступления.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
        ВТ_Поступления.Приход КАК Приход,
        ВТ_Поступления.Расход КАК Расход
    ИЗ
        ВТ_Поступления КАК ВТ_Поступления

    ОБЪЕДИНИТЬ ВСЕ

    ВЫБРАТЬ
        ВТ_Продажи.Склад,
        ВТ_Продажи.Контрагент,
        ВТ_Продажи.Номенклатура,
        ВТ_Продажи.ХарактеристикаНоменклатуры,
        ВТ_Продажи.Приход,
        ВТ_Продажи.Расход
    ИЗ
        ВТ_Продажи КАК ВТ_Продажи) КАК ОбъединенныеДанные
    
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Штрихкоды КАК Штрихкоды
        ПО ОбъединенныеДанные.ХарактеристикаНоменклатуры = Штрихкоды.Характеристика
        И ОбъединенныеДанные.Номенклатура = Штрихкоды.Номенклатура

СГРУППИРОВАТЬ ПО
    ЕСТЬNULL(Штрихкоды.Штрихкод, ""),
    ОбъединенныеДанные.Склад,
    ОбъединенныеДанные.Контрагент,
    ОбъединенныеДанные.Номенклатура,
    ОбъединенныеДанные.ХарактеристикаНоменклатуры

УПОРЯДОЧИТЬ ПО
    Штрихкод,
    ОбъединенныеДанные.Номенклатура.Наименование

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

• Максимальное быстродействие. СУБД оперирует уже подготовленными и сгруппированными данными во временных таблицах ВТ_Поступления и ВТ_Продажи. Это исключает необходимость полного сканирования таблиц движений при соединении.
• Использование виртуальных таблиц оборотов. Вместо чтения физических таблиц запросы задействуют механизм итогов 1С, что сокращает время выполнения операции в десятки раз.
• Корректное отображение товаров без штрихкодов. Благодаря применению конструкции ЛЕВОЕ СОЕДИНЕНИЕ и функции ЕСТЬNULL, все позиции номенклатуры будут корректно выведены в отчет, независимо от наличия зарегистрированного штрихкода в базе данных.

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