Как в запросе 1С получить уникальные остатки номенклатуры при соединении с табличной частью справочника?

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

Проанализируем типичную ситуацию на примере исходного запроса:

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

В результате выполнения такого запроса по одной позиции номенклатуры (например, «Цыпленок-бройлер 1кат.» с фактическим остатком 0,204) в результат попадает несколько одинаковых строк с тем же остатком 0,204, различающихся только строками норм выхода. При попытке применить конструкцию СГРУППИРОВАТЬ ПО и функцию СУММА(ПартииНоменклатурыОстатки.КоличествоОстаток) остаток математически умножается на количество строк в табличной части (получается 1,428 вместо 0,204), что полностью искажает данные.

Причины возникновения проблемы

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

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

Способ 1. Получение уникального списка номенклатуры через временную таблицу

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

Рассмотрим текст двухпакетного запроса:

ВЫБРАТЬ РАЗЛИЧНЫЕ
    Нормы.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_НоменклатураСНормами
ИЗ
    Справочник.Номенклатура.УР_НормыВыходаПолуфабрикатов КАК Нормы
ГДЕ
    Нормы.Номенклатура ЕСТЬ НЕ NULL
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

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

Посмотрим, почему этот вариант работает правильно и быстро:

1. В первом пакете ключевое слово РАЗЛИЧНЫЕ сворачивает все дубликаты ссылок номенклатуры из табличной части до уникального набора.
2. Конструкция ИНДЕКСИРОВАТЬ ПО строит индекс по полю связи, ускоряя последующее соединение.
3. Во втором пакете виртуальная таблица Остатки рассчитывает данные только по тем позициям, которые попали во временную таблицу. Каждая номенклатура возвращается строго в единственном экземпляре с реальным значением остатка (0,204), а необходимость в вызове СУММА() и СГРУППИРОВАТЬ ПО отпадает.

Способ 2. Фильтрация через вложенный запрос в параметрах виртуальной таблицы

Если запрос небольшой и не требует создания отдельного пакета, отбор можно перенести непосредственно в параметры виртуальной таблицы регистра остатков.

Рассмотрим пример запроса с вложенным отбором:

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

В этом случае платформа 1С и СУБД используют полусоединение (semi-join). Табличная часть справочника выступает только фильтром, физического размножения строк выборки не происходит, и остатки остаются корректными.

Рекомендации по оптимизации запросов к остаткам

Разберем правила, которых следует придерживаться при составлении подобных запросов:

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