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

← На главную

Рассмотрим классическую задачу, с которой часто сталкиваются разработчики при доработке типовых отчетов в 1С. Предположим, у нас есть отчет «Валовая прибыль», построенный на регистре продаж. Поступает требование: необходимо вывести дополнительную колонку с суммами закупок у поставщиков, чтобы пользователь мог видеть товары, которые были закуплены, но по каким-то причинам не продаются.

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

Причина потери записей в СКД при полном соединении

Проанализируем ситуацию детально. Когда мы выполняем полное соединение двух таблиц (например, таблицы продаж и таблицы закупок) по ключевым измерениям (Организация, Партнер, Номенклатура), СУБД сопоставляет записи. Если в таблице продаж нет данных по конкретной номенклатуре, то во всех полях, относящихся к таблице продаж, будет возвращено значение NULL.

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

Отсутствие ключевых полей для группировки. Группировать записи по значению NULL некорректно, отчет "теряет" представление аналитики.
Влияние пользовательских отборов. Если пользователь или программист устанавливает фильтр в СКД (например, "Номенклатура в группе 'Товары'"), этот отбор применяется к результату соединения. Значение NULL проверку отбором не проходит, и строка закономерно отсекается движком СКД.

Решение 1: Правильная обработка NULL в измерениях и временные таблицы

Чтобы избежать описанной проблемы, мы должны явно указать системе: если измерение из первой таблицы равно NULL, нужно взять измерение из второй таблицы. Рассмотрим подробнее правильный синтаксис для полей выборки:


ВЫБРАТЬ
    ВЫБОР 
        КОГДА Таблица1.Измерение1 ЕСТЬ NULL ТОГДА Таблица2.Измерение1 
        ИНАЧЕ Таблица1.Измерение1 
    КОНЕЦ КАК Измерение1

Или можно использовать более краткий аналог — функцию ЕСТЬNULL. Посмотрим на практический пример того, как это должно выглядеть при соединении продаж и закупок. Заодно применим важное правило разработки на платформе 1С: строго не рекомендуется использовать полное соединение на виртуальных таблицах напрямую. Сначала мы выгружаем данные в временные таблицы, а уже потом соединяем их.


// Шаг 1. Готовим данные по продажам
ВЫБРАТЬ
    Продажи.АналитикаУчетаПоПартнерам.Организация КАК Организация,
    Продажи.АналитикаУчетаПартий.Поставщик КАК ПартнерПоставщик,
    Продажи.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    СУММА(ЕСТЬNULL(Продажи.СуммаВыручкиОборот, 0)) КАК Выручка
ПОМЕСТИТЬ ВТПродажи
ИЗ
    РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты({(&НачалоПериода)}, {(&ОкончаниеПериода)}) КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.АналитикаУчетаПоПартнерам.Организация,
    Продажи.АналитикаУчетаПартий.Поставщик,
    Продажи.АналитикаУчетаНоменклатуры.Номенклатура;

// Шаг 2. Готовим данные по закупкам
ВЫБРАТЬ
    ЗакупкиОбороты.Организация КАК Организация,
    ЗакупкиОбороты.Партнер КАК ПартнерПоставщик,
    ЗакупкиОбороты.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    ЕСТЬNULL(ЗакупкиОбороты.СуммаОборот, 0) КАК СуммаЗакупки
ПОМЕСТИТЬ ВТЗакупки
ИЗ
    РегистрНакопления.Закупки.Обороты({(&НачалоПериода)}, {(&ОкончаниеПериода)}) КАК ЗакупкиОбороты;

// Шаг 3. Соединяем временные таблицы, правильно обрабатывая измерения
ВЫБРАТЬ
    ЕСТЬNULL(ВТЗакупки.Организация, ВТПродажи.Организация) КАК Организация,
    ЕСТЬNULL(ВТЗакупки.ПартнерПоставщик, ВТПродажи.ПартнерПоставщик) КАК ПартнерПоставщик,
    ЕСТЬNULL(ВТЗакупки.Номенклатура, ВТПродажи.Номенклатура) КАК Номенклатура,
    ЕСТЬNULL(ВТПродажи.Выручка, 0) КАК Выручка,
    ЕСТЬNULL(ВТЗакупки.СуммаЗакупки, 0) КАК СуммаЗакупки
ИЗ
    ВТПродажи КАК ВТПродажи
        ПОЛНОЕ СОЕДИНЕНИЕ ВТЗакупки КАК ВТЗакупки
        ПО ВТПродажи.Организация = ВТЗакупки.Организация
            И ВТПродажи.ПартнерПоставщик = ВТЗакупки.ПартнерПоставщик
            И ВТПродажи.Номенклатура = ВТЗакупки.Номенклатура

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

Решение 2: Использование "ОБЪЕДИНИТЬ ВСЕ" (Идеальный паттерн 1С)

Рассмотрим альтернативный метод. При сборке оборотной информации из разных регистров в 1С наиболее отказоустойчивым и быстрым паттерном считается не соединение, а применение оператора ОБЪЕДИНИТЬ ВСЕ с последующей группировкой записей. Этот подход полностью избавляет нас от необходимости писать громоздкие проверки ЕСТЬNULL для каждого измерения.

Суть метода заключается в следующем:

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

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


ВЫБРАТЬ
    ОбъединенныеДанные.Организация КАК Организация,
    ОбъединенныеДанные.Номенклатура КАК Номенклатура,
    СУММА(ОбъединенныеДанные.Выручка) КАК Выручка,
    СУММА(ОбъединенныеДанные.СуммаЗакупки) КАК СуммаЗакупки
ИЗ
    (ВЫБРАТЬ
        Продажи.АналитикаУчетаПоПартнерам.Организация КАК Организация,
        Продажи.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
        Продажи.СуммаВыручкиОборот КАК Выручка,
        0 КАК СуммаЗакупки
    ИЗ
        РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты({(&НачалоПериода)}, {(&ОкончаниеПериода)}) КАК Продажи

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

    ВЫБРАТЬ
        ЗакупкиОбороты.Организация,
        ЗакупкиОбороты.АналитикаУчетаНоменклатуры.Номенклатура,
        0,
        ЗакупкиОбороты.СуммаОборот
    ИЗ
        РегистрНакопления.Закупки.Обороты({(&НачалоПериода)}, {(&ОкончаниеПериода)}) КАК ЗакупкиОбороты) КАК ОбъединенныеДанные
СГРУППИРОВАТЬ ПО
    ОбъединенныеДанные.Организация,
    ОбъединенныеДанные.Номенклатура

Этот метод гарантирует, что ни одна запись не потеряется из-за сбоя условий соединения. Кроме того, оптимизатор СУБД часто обрабатывает объединения быстрее, чем полные соединения больших таблиц.

Решение 3: Объединение наборов данных средствами самой СКД

Иногда программисты пытаются использовать возможности вкладки "Связи наборов данных" в схеме компоновки, добавляя два независимых набора. Однако здесь кроется важный нюанс: СКД всегда связывает наборы данных только левым соединением.

Если мы сделаем первый набор (Продажи) источником связи, а второй набор (Закупки) — приемником, то мы получим классическое левое соединение. Те товары, по которым были только закупки, но не было продаж, в итоговый отчет не попадут.

Если мы все же хотим сымитировать полное соединение на уровне наборов данных СКД, нам потребуется создать отдельный (третий) "ведущий" набор данных. Этот набор должен содержать полный перечень ключей аналитики (например, объединение ключей продаж и закупок или просто справочник номенклатуры). И уже к этому третьему ведущему набору данных мы будем привязывать оба регистра продаж и закупок с помощью связей (которые отработают как левые соединения к полному списку ключей).

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