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

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

Проанализируем механику задачи. Обычное условие В(&Список) фильтрует отдельные строки, но не исключает документ, если в нем присутствуют «лишние» товары. Условие отсутствия расхождений через левые соединения часто отсекает документы с неполным набором, но спотыкается, когда список выбран целиком. Рассмотрим несколько способов решения этой задачи на языке запросов 1С: от алгоритмов на основе реляционных соединений до оптимального варианта с агрегатными функциями.

Способ 1. Сопоставление через полное соединение и проверку отсутствия NULL

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

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

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

Рассмотрим логику работы этого пакета запросов по шагам:

1. ВТДокументы: Сначала выбираются ссылки только тех документов, где встречается хотя бы один товар из списка, и для них выгружается полный состав табличной части (без ограничения по номенклатуре). Это критически важно, так как фильтрация строк табличной части на первом шаге не позволила бы отследить «лишние» товары.
2. ВТДокументыСНоменклатурой: Формируется эталонное декартово произведение документов и элементов отбора. Здесь генерируется список идеального состава: каждый отобранный документ со всеми позициями из &Номенклатура.
3. Полное соединение и группировка: Таблицы соединяются по документу и номенклатуре. Если в документе не хватает позиции из списка, в поле ВТДокументы.Номенклатура появится NULL. Если в документе присутствует лишняя позиция, NULL возникнет в ВТДокументыСНоменклатурой.Номенклатура. Конструкция в секции ИМЕЮЩИЕ гарантирует, что ни с одной из сторон не появилось значений NULL.

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

Способ 2. Оптимальное решение через группировку и реляционное деление

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

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

Посмотрим на пример запроса:

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

Проанализируем, почему этот метод работает надежно и быстро:

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

Автоматическое заполнение параметра &КоличествоВСписке в СКД

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

1. Перейдем на закладку Параметры в СКД.
2. Для служебного параметра &КоличествоВСписке установим признак Ограничение доступности, чтобы скрыть его из пользовательских настроек.
3. В колонке Выражение укажем стандартное обращение к коллекции: &СписокНоменклатуры.Количество(). При компоновке макета платформа автоматически вычислит точное число переданных позиций.
4. Для основного параметра &СписокНоменклатуры обязательно установим флажок Запрет незаполненных значений, чтобы исключить формирование невалидных условий при пустом отборе.

В случае, если пользователь может передать список значений с неуникальными записями, расчет эталонного количества надежнее выполнить в первом пакете запроса, выбрав различные элементы из справочника Номенклатура с фильтром В (&СписокНоменклатуры), после чего соединить полученное число с основным условием ИМЕЮЩИЕ.