Как рассчитать остаток в запросе 1С через ОБЪЕДИНИТЬ ВСЕ и почему фильтр ГДЕ ломает расчет?

← На главную

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

Рассмотрим реальную ситуацию: разработчик пытается получить разницу между товарами в транзитном перемещении и уже отгруженными товарами. Он объединяет две выборки, но итоговый расчет количества получается неверным. Выясним причину и найдем правильное решение.

Анализ первоначального запроса и его логические ошибки

Давайте проанализируем исходную идею автора. Он создает вложенный запрос, в котором с помощью ОБЪЕДИНИТЬ ВСЕ "склеивает" две таблицы:

Данные из уже отгруженных перемещений (ПеремещениеТоваровОтгружено).
Данные из текущего, "транзитного" перемещения (ПеремещениеТоваровТранзитное).

В первой выборке он создает поле КоличествоОтгружено, а во второй — КоличествоОсталосьОтгрузить. Во внешнем запросе он пытается вычесть одно из другого: ВложенныйЗапрос.КоличествоОсталосьОтгрузить - ВложенныйЗапрос.КоличествоОтгружено.


// Фрагмент неверной логики из начального запроса
ВЫБРАТЬ
    ...
    ВложенныйЗапрос.КоличествоОсталосьОтгрузить - ВложенныйЗапрос.КоличествоОтгружено КАК Количество
ИЗ
    (ВЫБРАТЬ
        ...
        ПеремещениеТоваровОтгружено.Количество КАК КоличествоОтгружено,
        NULL КАК КоличествоОсталосьОтгрузить // или 0
    ИЗ
        ...
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        ...
        0 КАК КоличествоОтгружено,
        ПеремещениеТоваровТранзитное.Количество КАК КоличествоОсталосьОтгрузить
    ИЗ
        ...) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО ...

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

Правильный подход: метод "Плюс-Минус"

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

В обеих частях объединяемого запроса создается поле с одинаковым именем, например, Количество.
Данные, которые должны быть "приходом" (в нашем случае это плановое количество к отгрузке), выбираются с положительным знаком.
Данные, которые должны быть "расходом" (уже отгруженное количество), выбираются с отрицательным знаком (просто ставим минус перед полем).
Во внешнем запросе мы группируем данные по нужной аналитике (например, по номенклатуре) и применяем агрегатную функцию СУММА() к нашему полю Количество.

Система просуммирует все положительные и отрицательные значения для каждой номенклатуры, и в итоге мы получим корректный остаток. Давайте посмотрим, как это выглядит в коде:


ВЫБРАТЬ
    ВложенныйЗапрос.Номенклатура,
    СУММА(ВложенныйЗапрос.Количество) КАК КоличествоОстаток
ИЗ
    (ВЫБРАТЬ
        ПеремещениеТоваровОтгружено.Номенклатура КАК Номенклатура,
        -ПеремещениеТоваровОтгружено.Количество КАК Количество // Берем количество со знаком "минус"
    ИЗ
        Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровОтгружено
    ГДЕ
        ...

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

    ВЫБРАТЬ
        ПеремещениеТоваровТранзитное.Номенклатура,
        ПеремещениеТоваровТранзитное.Количество // Здесь количество остается с "плюсом"
    ИЗ
        Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТранзитное
    ГДЕ
        ...) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура

Этот подход является каноническим и самым производительным для подобных задач.

Ключевая ошибка: фильтрация с помощью ГДЕ вместо ИМЕЮЩИЕ

После применения метода "плюс-минус" автор столкнулся с новой проблемой. Запрос по-прежнему работал некорректно. Чтобы отладить его, он упростил код, оставив только номенклатуру и количество, и добавил условие, чтобы отсечь позиции, которые полностью отгружены (т.е. где остаток равен нулю).

Именно в этом условии и скрывалась главная ошибка:


// Неправильный запрос с фильтрацией
ВЫБРАТЬ
    ВложенныйЗапрос.Номенклатура КАК Номенклатура,
    СУММА(ВложенныйЗапрос.Количество) КАК Количество
ИЗ
    ( ... объединенный запрос с плюсами и минусами ... ) КАК ВложенныйЗапрос
ГДЕ
    ВложенныйЗапрос.Количество > 0 // <-- КРИТИЧЕСКАЯ ОШИБКА
СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура

Чтобы понять, почему это не работает, нужно четко различать порядок выполнения операций в запросе и назначение ключевых слов ГДЕ (WHERE) и ИМЕЮЩИЕ (HAVING).

ГДЕ — этот оператор применяется к строкам ДО того, как они будут сгруппированы. Он работает с исходными данными вложенного запроса. В нашем случае, условие ГДЕ ВложенныйЗапрос.Количество > 0 просто отфильтровывало все строки, где количество было отрицательным (т.е. все строки с отгрузками). В итоге, до группировки и функции СУММА() доходили только строки с положительным количеством, и никакого вычитания не происходило.
ИМЕЮЩИЕ — этот оператор применяется к результатам ПОСЛЕ выполнения группировки и вычисления агрегатных функций (СУММА, КОЛИЧЕСТВО, МАКСИМУМ и т.д.). Он фильтрует уже сгруппированные строки.

Финальное решение

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

Вот как выглядит итоговый, корректно работающий запрос:


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

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

    ВЫБРАТЬ
        ПеремещениеТоваровТранзитное.Номенклатура,
        ПеремещениеТоваровТранзитное.Количество
    ИЗ
        Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТранзитное
    ГДЕ
        ПеремещениеТоваровТранзитное.Ссылка = &ЭтотДокумент
        И ПеремещениеТоваровТранзитное.Ссылка.Проведен = ИСТИНА
    ) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура
ИМЕЮЩИЕ
    СУММА(ВложенныйЗапрос.Количество) <> 0 // <-- ПРАВИЛЬНО! Фильтруем итоговый остаток.

Важный момент: если нужно показать только недопоставленный товар, условие будет СУММА(ВложенныйЗапрос.Количество) > 0.

Дополнительные советы по отладке и оптимизации

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

Таким образом, для корректного расчета итогов по объединенным таблицам используйте метод "плюс-минус", а для фильтрации сгруппированных результатов — всегда применяйте оператор ИМЕЮЩИЕ.