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

В практике разработки на платформе 1С:Предприятие 8 часто возникает задача вывести в один отчет данные из разных источников. Типичный пример: необходимо сопоставить количество проданного товара (из регистра накопления Продажи) и его текущий остаток на складах (из регистра ТоварыНаСкладах). Рассмотрим, как правильно построить такой запрос, разберем типичные ошибки новичков и проанализируем методы оптимизации производительности.

Различие между ОБЪЕДИНЕНИЕМ и СОЕДИНЕНИЕМ

Разберем типичную ситуацию: начинающий разработчик пытается использовать оператор ОБЪЕДИНИТЬ (UNION), чтобы собрать данные. Это приводит к тому, что данные выводятся «лесенкой» или в разные строки, а колонки не сопоставляются по смыслу. Выясним причину: оператор ОБЪЕДИНИТЬ просто дописывает строки одной таблицы в конец другой. Для решения нашей задачи нам требуется оператор СОЕДИНЕНИЕ (JOIN), который позволяет сопоставить записи разных таблиц в одной строке по ключевому полю — в данном случае по Номенклатуре.

Способ 1. Простое левое соединение виртуальных таблиц

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

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

Посмотрим на особенности этого решения:

1. Мы используем виртуальные таблицы. Это позволяет системе 1С автоматически обращаться к итоговым таблицам СУБД, а не пересчитывать все движения документа с начала времен.
2. Мы используем ЛЕВОЕ СОЕДИНЕНИЕ. Это означает, что в результат попадут все товары, которые были проданы. Если по проданному товару нет остатка, в колонке «Остаток» будет NULL.
3. Связь устанавливается по полю Номенклатура.

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

Способ 2. Оптимизация через временные таблицы (ВТ)

При работе с большими объемами данных прямое соединение двух виртуальных таблиц может вызвать замедление работы СУБД. Проанализируем ситуацию: виртуальная таблица в SQL превращается в сложный подзапрос. Соединение двух тяжелых подзапросов «на лету» часто мешает оптимизатору SQL правильно использовать индексы. Рассмотрим более надежный и профессиональный метод — использование временных таблиц.

Разобьем задачу на шаги:

1. Выберем данные по продажам во временную таблицу и проиндексируем её.
2. Во втором запросе соединим эту временную таблицу с остатками, передав список нужной номенклатуры в параметры виртуальной таблицы остатков.

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

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

Разберем преимущества этого подхода. Когда мы используем конструкцию Номенклатура В (ВЫБРАТЬ Т.Номенклатура ИЗ ВТ_Обороты) внутри параметров виртуальной таблицы Остатки, СУБД получает четкую команду: «Не нужно рассчитывать остатки по всей номенклатуре базы, рассчитай их только для тех товаров, которые есть в этой временной таблице». Это значительно снижает нагрузку на дисковую подстему и память сервера.

Анализ производительности и планы запросов

Рассмотрим, что происходит «под капотом» СУБД. При прямом соединении (Способ 1) часто выполняется NESTED LOOP или HASH JOIN по всему объему итоговых таблиц. Если товаров сотни тысяч, отчет может «зависнуть».

Использование индексации временной таблицы (ИНДЕКСИРОВАТЬ ПО) крайне важно. Индекс на временной таблице позволяет СУБД мгновенно находить соответствия. Однако помните, что создание индекса — это тоже операция, требующая времени. На малых объемах (до 100-200 строк) вы можете не заметить разницы, но на больших данных выигрыш будет многократным.

Рекомендации по использованию Системы компоновки данных (СКД)

Если ваша задача — создать отчет для пользователя, настоятельно рекомендуем использовать СКД. Почему это лучше ручного написания запроса в коде?

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

Типичные ошибки, которых следует избегать

1. Использование физических таблиц вместо виртуальных. Никогда не делайте ИЗ РегистрНакопления.Продажи, если вам нужны итоги. Обращение к физической таблице заставляет систему сканировать все записи регистра, что недопустимо для отчетов.

2. Отборы в секции ГДЕ вместо параметров виртуальной таблицы. Сравним два варианта:

1. ... Обороты() КАК Т ГДЕ Т.Номенклатура = &Ном — система сначала получит ВСЕ обороты, а потом отфильтрует их.
2. ... Обороты(,,, Номенклатура = &Ном) — система получит данные из итогов сразу только по одному товару.

Всегда стремитесь использовать параметры виртуальных таблиц для фильтрации.

3. Проблема NULL. При левом соединении, если данных во второй таблице нет, 1С вернет значение NULL. В арифметических операциях (например, при расчете разницы) это приведет к ошибке или пустому результату. Используйте функцию ЕСТЬNULL(Значение, 0), чтобы превратить отсутствие данных в числовой ноль.

Подводя итог, можно сказать: для простых задач достаточно прямого ЛЕВОГО СОЕДИНЕНИЯ виртуальных таблиц Обороты и Остатки. Для высоконагруженных систем и больших справочников номенклатуры следует использовать временные таблицы с предварительной индексацией и передачей фильтра в параметры виртуальной таблицы.