Как в запросе 1С отсечь номенклатуру с нулевыми движениями за период

← На главную

При построении отчетов в 1С (как на СКД, так и с использованием классических запросов) часто возникает типовая задача: собрать данные по нескольким показателям в разрезе номенклатуры (например: поступление, реализация, списание, возвраты) за определенный период и не выводить позиции, по которым за выбранный интервал времени не было никаких движений.

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

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

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

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

Способ 1. Использование конструкции ОБЪЕДИНИТЬ ВСЕ и группировки (Оптимальный вариант)

Наиболее эффективный и правильный способ в языке запросов 1С — отказаться от выборки всего справочника и объединить таблицы движений с помощью оператора ОБЪЕДИНИТЬ ВСЕ с последующей группировкой по номенклатуре.

Рассмотрим пример построения такого запроса на основе вложенного запроса:


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

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

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

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

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

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

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

СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура

Разберем ключевые особенности этого подхода:

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

Способ 2. Использование временных таблиц с индексацией

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


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

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

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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Движения.Номенклатура КАК Номенклатура,
    СУММА(ВТ_Движения.Поступило) КАК Поступило,
    СУММА(ВТ_Движения.Реализовали) КАК Реализовали
ИЗ
    ВТ_Движения КАК ВТ_Движения
СГРУППИРОВАТЬ ПО
    ВТ_Движения.Номенклатура

Способ 3. Отсечение строк через секцию ИМЕЮЩИЕ

Если в выборке могут встречаться строки, где итоги по всем показателям равны нулю (или при необходимости отсечь взаимокомпенсированные нулевые обороты), используем условие в секции ИМЕЮЩИЕ.

Важно: нельзя складывать показатели (например, СУММА(Поступило) + СУММА(Потеряли) <> 0), поскольку при наличии разнонаправленных движений (например, +1 и -1) сумма даст 0, и строка ошибочно скроется. Условия проверяются отдельно для каждого показателя через оператор ИЛИ:


СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура
ИМЕЮЩИЕ
    ЕСТЬNULL(СУММА(ВложенныйЗапрос.Поступило), 0) <> 0
    ИЛИ ЕСТЬNULL(СУММА(ВложенныйЗапрос.Реализовали), 0) <> 0
    ИЛИ ЕСТЬNULL(СУММА(ВложенныйЗапрос.Украли), 0) <> 0
    ИЛИ ЕСТЬNULL(СУММА(ВложенныйЗапрос.Потеряли), 0) <> 0

Обратим внимание на вызов ЕСТЬNULL(СУММА(Поле), 0): расчет агрегатной суммы внутри функции выполняется быстрее, чем вычисление функции над каждой строкой до суммирования СУММА(ЕСТЬNULL(Поле, 0)).

Способ 4. Фильтрация на уровне настроек СКД

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

Переходим на вкладку Настройки в схеме компоновки данных.
Выбираем группировку Номенклатура или корневой элемент Отчет.
На вкладке Отбор добавляем группу условий ИЛИ:
1. Поступило Не равно 0
2. Реализовали Не равно 0
3. Украли Не равно 0
4. Потеряли Не равно 0

СКД автоматически применит фильтрацию к рассчитанным ресурсам после выполнения запроса, скрыв все пустые строки из итогового табличного документа.