Как в запросе 1С выполнить арифметические действия с результатами агрегатных функций?

← На главную

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

Анализ проблемы: почему простые операции могут не работать

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

Проанализируем некорректный подход:


ВЫБРАТЬ
    ВЫБОР КОГДА Анкета.Проведен ТОГДА КОЛИЧЕСТВО(Анкета.Ссылка) КОНЕЦ -
    ВЫБОР КОГДА НЕ Анкета.Проведен ТОГДА КОЛИЧЕСТВО(Анкета.Ссылка) КОНЕЦ КАК Разница
ИЗ
    Документ.Анкета КАК Анкета
СГРУППИРОВАТЬ ПО
    Анкета.Проведен

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

Способ №1: Использование функции СУММА с условием ВЫБОР

Это наиболее универсальный и надежный метод. Мы переносим условие проверки внутрь агрегатной функции СУММА. Если условие выполняется (документ проведен), мы прибавляем единицу, если нет — ноль. Таким образом, мы получаем числовые значения для обоих показателей в рамках одной строки результата.

Рассмотрим пример реализации:


ВЫБРАТЬ
    СУММА(ВЫБОР
            КОГДА Анкета.Проведен
                ТОГДА 1
            ИНАЧЕ 0
        КОНЕЦ) КАК КолвоПроведенных,
    СУММА(ВЫБОР
            КОГДА НЕ Анкета.Проведен
                ТОГДА 1
            ИНАЧЕ 0
        КОНЕЦ) КАК КолвоНеПроведенных,
    СУММА(ВЫБОР
            КОГДА Анкета.Проведен
                ТОГДА 1
            ИНАЧЕ 0
        КОНЕЦ) - СУММА(ВЫБОР
            КОГДА НЕ Анкета.Проведен
                ТОГДА 1
            ИНАЧЕ 0
        КОНЕЦ) КАК Разность
ИЗ
    Документ.Анкета КАК Анкета

В данном случае нам не требуется секция СГРУППИРОВАТЬ ПО, если мы хотим получить общий итог по всей таблице. Все вычисления производятся над одной виртуальной строкой итогов.

Способ №2: Использование функции КОЛИЧЕСТВО

Функция КОЛИЧЕСТВО в языке запросов 1С обладает важной особенностью: она игнорирует значения NULL. Мы можем использовать это, чтобы подсчитать только те строки, которые соответствуют нашему условию.

Выясним, как это выглядит в коде:


ВЫБРАТЬ
    КОЛИЧЕСТВО(ВЫБОР
            КОГДА Анкета.Проведен
                ТОГДА Анкета.Ссылка
            ИНАЧЕ NULL
        КОНЕЦ) КАК Пров,
    КОЛИЧЕСТВО(ВЫБОР
            КОГДА НЕ Анкета.Проведен
                ТОГДА Анкета.Ссылка
            ИНАЧЕ NULL
        КОНЕЦ) КАК НеПров,
    КОЛИЧЕСТВО(ВЫБОР
            КОГДА Анкета.Проведен
                ТОГДА Анкета.Ссылка
            ИНАЧЕ NULL
        КОНЕЦ) - КОЛИЧЕСТВО(ВЫБОР
            КОГДА НЕ Анкета.Проведен
                ТОГДА Анкета.Ссылка
            ИНАЧЕ NULL
        КОНЕЦ) КАК Разница
ИЗ
    Документ.Анкета КАК Анкета

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

Обработка значений NULL в арифметике

Разберем ситуацию, когда в базе данных нет ни одного непроведенного документа. В этом случае агрегатная функция СУММА или КОЛИЧЕСТВО может вернуть NULL (хотя СУММА обычно возвращает 0 при отсутствии строк, в сложных выражениях или при использовании внешних соединений NULL — частый гость). Чтобы запрос не «сломался» и не вернул пустое значение всей разности, каждое слагаемое следует оборачивать в функцию ЕСТЬNULL.

Посмотрим на более безопасный вариант кода:


ВЫБРАТЬ
    ЕСТЬNULL(СУММА(ВЫБОР КОГДА Анкета.Проведен ТОГДА 1 ИНАЧЕ 0 КОНЕЦ), 0) - 
    ЕСТЬNULL(СУММА(ВЫБОР КОГДА НЕ Анкета.Проведен ТОГДА 1 ИНАЧЕ 0 КОНЕЦ), 0) КАК ИтоговаяРазность
ИЗ
    Документ.Анкета КАК Анкета

Оптимизация обращения к полям (Разыменование ссылок)

Проанализируем часто встречающуюся ошибку новичков — обращение к полям через точку от ссылки, например: Анкета.Ссылка.Проведен. С точки зрения производительности это крайне нежелательно. Каждая такая «точка» заставляет систему выполнять неявное соединение (LEFT JOIN) с таблицей документа. Поскольку поле Проведен является стандартным реквизитом и уже присутствует в основной таблице Документ.Анкета, следует писать просто Анкета.Проведен. Это значительно ускоряет выполнение запроса, особенно на больших объемах данных.

Использование вложенных запросов для читаемости

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

Рассмотрим по шагам:

Создаем вложенный запрос, который считает количество проведенных и непроведенных документов.
Во внешнем запросе просто вычитаем одно поле из другого.


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

Особенности работы в Системе компоновки данных (СКД)

Если ваша задача реализуется в рамках отчета на СКД, то выполнять арифметические действия прямо в тексте запроса не всегда обязательно. Рассмотрим альтернативные варианты:

Вычисляемые поля: Вы можете определить поля Пров и НеПров в запросе, а разность между ними вычислить на закладке «Вычисляемые поля».
Ресурсы: В СКД можно задать выражение для ресурса. Например: Сумма(Пров) - Сумма(НеПров). Платформа сама позаботится о корректном расчете итогов по всем группировкам и иерархиям, что гораздо сложнее реализовать в чистом запросе через ИТОГИ.

Таким образом, для простых расчетов в запросе используйте СУММА(ВЫБОР...), а для сложных отчетов со множеством группировок отдавайте предпочтение механизмам СКД.