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

← На главную

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

Начинающие разработчики часто пытаются решить эту задачу «в лоб» с помощью простого условия в секции ГДЕ, например: ГДЕ Справочник.ТаблЧасть.Признак = ИСТИНА. Однако такой подход неверен. Подобный запрос вернет родительский объект, если у него в табличной части найдется хотя бы одна строка со значением ИСТИНА, даже если все остальные строки содержат ЛОЖЬ. Нам же требуется строгое соответствие: либо все строки содержат только одно значение, либо в табличной части нет разнородных данных.

В этой статье мы подробно разберем несколько эффективных способов решения этой задачи с помощью языка запросов 1С, проанализируем их плюсы и минусы, оценим производительность решений на больших базах данных и выясним, как правильно обрабатывать объекты с пустыми табличными частями.

Способ №1. Использование агрегатных функций МАКСИМУМ и МИНИМУМ

Рассмотрим первый элегантный способ, основанный на математической логике. Если все строки табличной части содержат одно и то же значение реквизита, то минимальное значение этого реквизита среди всех строк должно быть равно его максимальному значению.

Если в табличной части для конкретной ссылки есть строки со значениями [ИСТИНА, ИСТИНА, ИСТИНА], то МИНИМУМ будет равен ИСТИНА, а МАКСИМУМ — также ИСТИНА. Они равны. Если же в табличной части окажется хотя бы одна строка со значением ЛОЖЬ, то набор данных станет [ИСТИНА, ЛОЖЬ], где МИНИМУМ вернет ЛОЖЬ, а МАКСИМУМ — ИСТИНА. В этом случае равенство нарушится, и такой элемент будет отсечен фильтром.

Давайте разберем, как выглядит этот запрос в коде 1С:


ВЫБРАТЬ
    ТЧ.Ссылка КАК Ссылка,
    МИНИМУМ(ТЧ.Признак) КАК ОднородноеЗначение
ИЗ
    Справочник.Номенклатура.ДополнительныеРеквизиты КАК ТЧ
СГРУППИРОВАТЬ ПО
    ТЧ.Ссылка
ИМЕЮЩИЕ
    МИНИМУМ(ТЧ.Признак) = МАКСИМУМ(ТЧ.Признак)

Преимущества этого подхода:

Простота и универсальность: Данное решение подходит для реквизитов любого типа (не только булевых, но и числовых, строковых, ссылочных).
Возвращаемое значение: В результате запроса мы сразу можем получить то самое единственное значение реквизита, которое заполнено во всех строках (через поле выборки МИНИМУМ(ТЧ.Признак)).

Однако, как мы разберем далее, этот метод имеет свои особенности при обработке объектов без строк в табличной части.

Способ №2. Использование функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ)

Второй классический подход — это подсчет количества уникальных (различных) значений реквизита для каждого родительского объекта. Если во всех строках ТЧ записано строго одно и то же значение, то количество уникальных значений будет равно единице.

Посмотрим на пример практической реализации данного запроса:


ВЫБРАТЬ
    ТЧ.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура.ДополнительныеРеквизиты КАК ТЧ
СГРУППИРОВАТЬ ПО
    ТЧ.Ссылка
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТЧ.Признак) = 1

Анализ работы запроса:

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

Если у элемента справочника в ТЧ содержатся строки со значениями [ИСТИНА, ЛОЖЬ], количество различных значений будет равно 2, и элемент не попадет в итоговую выборку. Если все строки имеют значение ЛОЖЬ, количество различных значений будет равно 1 (значение ЛОЖЬ), и элемент успешно попадет в результат.

Нюанс: Что делать с пустыми табличными частями?

В процессе обсуждения этой задачи на форумах часто возникает важный вопрос бизнес-логики: как система должна обрабатывать элементы, у которых табличная часть пуста?

Давайте проанализируем поведение первых двух способов в этой ситуации:

При использовании группировки по табличной части (ИЗ Справочник...ТаблЧасть) элементы с пустой ТЧ вообще не попадут в промежуточную выборку СУБД, так как в физической таблице базы данных для них просто нет записей. Следовательно, они будут полностью исключены из результата.
Если вы используете левое соединение справочника с его табличной частью, для пустой ТЧ количество различных значений реквизита (с учетом NULL) может интерпретироваться СУБД по-разному, но чаще всего результат также будет отсекаться условием равенства единице.

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

Способ №3. Оптимизация производительности: Логическое отрицание (EXISTS)

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

Выясним, как оптимизировать этот алгоритм. Мы можем перевернуть логику условия. Условие «все строки ТЧ имеют значение ИСТИНА» логически полностью эквивалентно утверждению: «в табличной части нет ни одной строки со значением ЛОЖЬ».

В языке запросов 1С обращение к реквизитам табличной части через основную таблицу объекта с логическим отрицанием автоматически транслируется СУБД в высокоэффективный подзапрос NOT EXISTS (anti-semi-join). Посмотрим на пример такого запроса:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    НЕ (Номенклатура.ДополнительныеРеквизиты.Признак = ЛОЖЬ)

Почему этот запрос будет работать во много раз быстрее на больших объемах данных?

Отсутствие группировок: СУБД не тратит ресурсы на группировку всей таблицы по ссылке и подсчет агрегатных функций.
Быстрый выход (Early Exit): При выполнении операции NOT EXISTS СУБД сканирует строки табличной части для конкретной ссылки и останавливает поиск сразу же, как только встречает первую строку со значением ЛОЖЬ. Ей нет необходимости просматривать все оставшиеся строки ТЧ для данного элемента.
Обработка пустых ТЧ: Этот запрос автоматически и корректно вернет элементы с пустой табличной частью! Поскольку строк в ТЧ нет, условие ДополнительныеРеквизиты.Признак = ЛОЖЬ вернет значение NULL (которое интерпретируется как Ложь), а внешнее отрицание НЕ превратит его в Истину, включив элемент в итоговую выборку.

Если же вам нужно отобрать элементы, у которых все строки содержат только значение ЛОЖЬ, запрос строится аналогично, но с проверкой на ИСТИНА:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    НЕ (Номенклатура.ДополнительныеРеквизиты.Признак = ИСТИНА)

Способ №4. Надежное ЛЕВОЕ СОЕДИНЕНИЕ с проверкой на NULL

Если вам требуется решить задачу максимально стандартным способом, который гарантированно эффективно отработает на любых СУБД (MS SQL Server, PostgreSQL, IBM DB2 или Oracle) и задействует существующие индексы, мы рекомендуем использовать паттерн Left Join Exclusion (Исключающее левое соединение).

Разберем этот метод по шагам:

Во временную таблицу мы отбираем все ссылки элементов, которые гарантированно НЕ удовлетворяют нашему условию (то есть те, у которых в ТЧ есть хотя бы одна строка с «неправильным» значением реквизита).
В основном запросе мы выбираем все элементы из справочника.
Выполняем левое соединение справочника с нашей временной таблицей по полю Ссылка.
В секции ГДЕ оставляем только те записи, для которых не нашлось соответствия во временной таблице (условие ВТ_ЭлементыСНарушением.Ссылка ЕСТЬ NULL).

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


// Шаг 1: Отбираем во временную таблицу все элементы, нарушающие однородность
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ТЧ.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_ЭлементыСНарушением
ИЗ
    Справочник.Номенклатура.ДополнительныеРеквизиты КАК ТЧ
ГДЕ
    ТЧ.Признак = ЛОЖЬ

ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

// Шаг 2: Получаем финальный список «чистых» элементов
ВЫБРАТЬ
    Номенклатура.Ссылка КАК Ссылка
ИЗ
    Справочник.Номенклатура КАК Номенклатура
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЭлементыСНарушением КАК ВТ_ЭлементыСНарушением
    ПО Номенклатура.Ссылка = ВТ_ЭлементыСНарушением.Ссылка
ГДЕ
    ВТ_ЭлементыСНарушением.Ссылка ЕСТЬ NULL

Преимущества этого решения:

Максимальное быстродействие: Выборка во временную таблицу по простому условию ГДЕ ТЧ.Признак = ЛОЖЬ мгновенно отрабатывает по индексу табличной части. Использование индексации временной таблицы обеспечивает быструю операцию слияния (Merge Join или Hash Match) при левом соединении в основном запросе.
Прозрачность логики: Этот запрос легко читать, отлаживать и поддерживать другим разработчикам. Вы всегда можете открыть консоль запросов и посмотреть содержимое временной таблицы ВТ_ЭлементыСНарушением, чтобы понять, какие именно элементы не прошли проверку.

Сводная таблица для выбора оптимального метода

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

Метод	Сложность кода	Производительность	Что делает с пустой ТЧ?	Тип реквизита ТЧ
МИНИМУМ = МАКСИМУМ	Низкая	Средняя (требует полной группировки)	Исключает из выборки	Любой тип данных
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) = 1	Низкая	Низкая (тяжелая сортировка уникальных)	Исключает из выборки	Любой тип данных
Отрицание (NOT EXISTS)	Средняя	Очень высокая	Включает в выборку	Только булево / конкретное значение
ЛЕВОЕ СОЕДИНЕНИЕ на NULL	Высокая (нужна ВТ)	Высокая (стабильный план SQL)	Включает в выборку	Любой тип данных

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