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

← На главную

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

Разберем, как правильно реализовать условный подсчет уникальных значений внутри самой агрегатной функции с помощью конструкции ВЫБОР и особенности работы со значением NULL.

Принцип работы агрегатных функций со значением NULL

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

Конструкция условного оператора ВЫБОР в языке запросов 1С по умолчанию возвращает NULL, если ни одно из условий КОГДА не выполнилось и ветка ИНАЧЕ явно не указана (либо в ней явно прописано NULL).

Решение задачи: использование конструкции ВЫБОР внутри КОЛИЧЕСТВО(РАЗЛИЧНЫЕ)

Рассмотрим базовый синтаксис решения. Чтобы подсчитать количество уникальных значений поля Номенклатура только для проведенных документов или определенных строк, обернем поле в конструкцию ВЫБОР:


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

Проанализируем этот пример:

В первом выражении для операций продажи возвращается ссылка на номенклатуру, а для всех остальных — NULL. Функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) подсчитывает только уникальные ссылки, пропуская NULL.
Во втором выражении ветка ИНАЧЕ NULL опущена. Поведение запроса остается точно таким же, так как при отсутствии ветки ИНАЧЕ возвращается значение NULL.
В третьем выражении мы параллельно получаем общее число строк по всей таблице без применения условий.

Важные ограничения языка запросов при подсчете различных значений

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

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

Как подсчитать уникальные комбинации нескольких полей с условием

Если требуется получить количество уникальных комбинаций нескольких полей (например, уникальных пар «Цвет» и «Фрукт»), которые удовлетворяют условию, прямая агрегатная функция не подойдет. В этом случае применим один из двух подходов:

Вариант 1: Использование временной таблицы или вложенного запроса

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


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Таблица.Цвет КАК Цвет,
    Таблица.Фрукт КАК Фрукт
ПОМЕСТИТЬ ВТ_УникальныеПары
ИЗ
    Справочник.Номенклатура КАК Таблица
ГДЕ
    Таблица.ПометкаУдаления = ЛОЖЬ
;

ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК ВсегоУникальныхКомбинаций
ИЗ
    ВТ_УникальныеПары КАК ВТ_УникальныеПары

Вариант 2: Конкатенация строк внутри ВЫБОР

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


ВЫБРАТЬ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВЫБОР
            КОГДА Таблица.Активен = ИСТИНА
                ТОГДА Таблица.Цвет + "_" + Таблица.Фрукт
        КОНЕЦ) КАК КоличествоУникальныхПар
ИЗ
    Справочник.Номенклатура КАК Таблица

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

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