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

← На главную

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

В этой статье мы подробно разберем, почему возникает эта проблема на примере конфигурации «Штрих-М: Торговое предприятие 5.2» (и аналогичных решений на платформе 1С, таких как «Управление торговлей»), проанализируем внутренние механизмы работы СУБД и Системы компоновки данных (СКД), а также рассмотрим правильные архитектурные варианты решения этой задачи.

Анализ исходной проблемы и структуры запроса

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


ВЫБРАТЬ
    Обороты.Фирма,
    Обороты.Договор.Владелец КАК Контрагент,
    Обороты.Договор КАК Договор,
    Обороты.ДокументКредита КАК Документ,
    Остатки.СуммаОстаток КАК СуммаОстаток,
    РАЗНОСТЬДАТ(Обороты.Период, &ДатаОтчета, ДЕНЬ) КАК ДнейСОтгрузки,
    Обороты.СуммаРасход КАК СуммаОтгрузки,
    Обороты.Договор.ДопустимоеЧислоДнейЗадолженности КАК Отсрочка,
    ВЫРАЗИТЬ(ДОБАВИТЬКДАТЕ(Обороты.Период, ДЕНЬ, Обороты.Договор.ДопустимоеЧислоДнейЗадолженности) КАК ДАТА) КАК ДатаОплаты,
    РАЗНОСТЬДАТ(Обороты.Период, &ДатаОтчета, ДЕНЬ) - Обороты.Договор.ДопустимоеЧислоДнейЗадолженности КАК СрокДолга,
    ВЫБОР
        КОГДА РАЗНОСТЬДАТ(Обороты.Период, &ДатаОтчета, ДЕНЬ) - Обороты.Договор.ДопустимоеЧислоДнейЗадолженности > 60 ТОГДА 60
        КОГДА РАЗНОСТЬДАТ(Обороты.Период, &ДатаОтчета, ДЕНЬ) - Обороты.Договор.ДопустимоеЧислоДнейЗадолженности > 45 ТОГДА 45
        КОГДА РАЗНОСТЬДАТ(Обороты.Период, &ДатаОтчета, ДЕНЬ) - Обороты.Договор.ДопустимоеЧислоДнейЗадолженности > 30 ТОГДА 30
        ИНАЧЕ 0
    КОНЕЦ КАК ГруппаЗадолженности,
    Обороты.Период КАК Период,
    Обороты.Договор.ВалютаВзаиморасчетов КАК Валюта
ИЗ
    (ВЫБРАТЬ
        ОстаткиПоВзаиморасчетам.Договор КАК Договор,
        ОстаткиПоВзаиморасчетам.СуммаОстаток КАК СуммаОстаток
    ИЗ
        РегистрНакопления.Взаиморасчеты.Остатки(&ДатаОтчета, Договор.ВидДоговораБУ = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровКонтрагентов.СПоставщиком)) КАК ОстаткиПоВзаиморасчетам
    ГДЕ
        ОстаткиПоВзаиморасчетам.СуммаОстаток <> 0) КАК Остатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Взаиморасчеты.Обороты(, &ДатаОтчета, Регистратор, ) КАК Обороты
    ПО Остатки.Договор = Обороты.Договор
{ГДЕ
    Остатки.Договор.Фирма.* КАК Фирма,
    Остатки.Договор.Владелец.* КАК Контрагент,
    Обороты.Фирма.*,
    Обороты.Договор.Владелец.*,
    Обороты.Договор.*}

УПОРЯДОЧИТЬ ПО
    Период УБЫВ
ИТОГИ ПО
    Договор

При выполнении этого запроса по некоторым контрагентам выводятся строки, в которых заполнено только поле Остатки.СуммаОстаток, а все остальные поля (Фирма, Контрагент, Договор, Документ, СрокДолга) содержат значение NULL (визуально отображаются как пустые).

Давайте разберем причины этого поведения по шагам.

Причина №1. Механизм оптимизации СКД (Table Elimination)

Автор замечает странную особенность: «Если комментирую Обороты.СуммаРасход КАК СуммаОтгрузки, пустые строки уходят». Это классическое проявление работы оптимизатора Системы компоновки данных (СКД).

Когда вы выполняете запрос внутри СКД, платформа анализирует, какие поля из результирующего набора данных реально выводятся в отчет (в выбранные поля, группировки или участвуют в расчете ресурсов). Если вы закомментировали поле Обороты.СуммаРасход, и при этом в настройках отчета больше не выводятся другие поля из правой таблицы (Обороты), СКД применяет механизм оптимизации: она полностью удаляет секцию LEFT JOIN из результирующего SQL-запроса, так как данные из правой таблицы фактически не нужны для построения отчета.

Как только левое соединение вырезается, запрос превращается в простую выборку из одной таблицы остатков. Нет соединения — нет и пустых строк (значений NULL). Но стоит вам раскомментировать хотя бы одно поле из таблицы оборотов, СКД возвращает LEFT JOIN на место, и пустые строки снова появляются.

Причина №2. Неверное условие соединения и игнорирование измерений регистра

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

Фирма
Договор
ДокументКредита (документ, сформировавший задолженность)

В подзапросе Остатки автор выбирает только Договор и СуммаОстаток, искусственно сворачивая (агрегируя) остатки долга, игнорируя конкретные документы отгрузки и фирмы. Затем он делает ЛЕВОЕ СОЕДИНЕНИЕ с таблицей оборотов только по одному полю — договору:

ПО Остатки.Договор = Обороты.Договор

К чему это приводит на практике:

Появление пустых строк (NULL): Если по договору есть остаток долга (например, перенесенный вводом начальных остатков или возникший из-за ручных корректировок), но за выбранный период (в данном случае — за всю историю до даты отчета) по этому договору не было зарегистрировано оборотов в таблице Обороты, левое соединение не найдет соответствия. В результате все поля из таблицы Обороты примут значение NULL. А так как поля Фирма, Договор, Контрагент автор выбирает именно из таблицы Обороты, то и вся строка, кроме суммы остатка, оказывается пустой!
Дублирование данных (Декартово произведение): Если по договору было 50 отгрузок (оборотов) за всю историю, а остаток висит только по одной конкретной накладной, левое соединение только по полю Договор свяжет этот единственный остаток со всеми 50-ю документами оборотов. Сумма остатка продублируется 50 раз в детальных записях, что приведет к колоссальному искажению итогов в отчете.

Как правильно решить проблему?

Проанализируем ситуацию и выберем оптимальные пути решения задачи. Существует два правильных подхода к реализации такого отчета.

Вариант А. Запрос без использования таблицы оборотов (рекомендуемый)

Поскольку ДокументКредита (документ отгрузки) является измерением регистра накопления, он уже присутствует в виртуальной таблице остатков! Нам вообще не нужно соединяться с таблицей оборотов, чтобы получить дату отгрузки и рассчитать срок долга. Мы можем получить все данные напрямую из таблицы остатков, обратившись к дате документа через точку. Это в десятки раз ускорит выполнение запроса и исключит любые пустые строки.

Рассмотрим пример правильного запроса:


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

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

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

Вариант Б. Корректное левое соединение по всем измерениям

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

Также важно выбирать ключевые поля (Фирма, Контрагент, Договор, Документ) из левой таблицы (таблицы остатков), так как при левом соединении они гарантированно заполнены. Для полей из правой таблицы, которые могут вернуть NULL, обязательно используем функцию ЕСТЬNULL.

Для оптимизации структуры запроса и облегчения работы планировщика СУБД откажемся от вложенных запросов в пользу временных таблиц:


// Помещаем остатки во временную таблицу с сохранением всей аналитики
ВЫБРАТЬ
    ВзаиморасчетыОстатки.Фирма КАК Фирма,
    ВзаиморасчетыОстатки.Договор КАК Договор,
    ВзаиморасчетыОстатки.ДокументКредита КАК ДокументКредита,
    ВзаиморасчетыОстатки.СуммаОстаток КАК СуммаОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.Взаиморасчеты.Остатки(&ДатаОтчета, Договор.ВидДоговораБУ = ЗНАЧЕНИЕ(Перечисление.ВидыДоговоровКонтрагентов.СПоставщиком)) КАК ВзаиморасчетыОстатки
ГДЕ
    ВзаиморасчетыОстатки.СуммаОстаток <> 0

ИНДЕКСИРОВАТЬ ПО
    Фирма,
    Договор,
    ДокументКредита
;

// Соединяем временную таблицу остатков с оборотами по всем измерениям
ВЫБРАТЬ
    Остатки.Фирма КАК Фирма,
    Остатки.Договор.Владелец КАК Контрагент,
    Остатки.Договор КАК Договор,
    Остатки.ДокументКредита КАК Документ,
    Остатки.СуммаОстаток КАК СуммаОстаток,
    ЕСТЬNULL(Обороты.СуммаРасход, 0) КАК СуммаОтгрузки,
    РАЗНОСТЬДАТ(Остатки.ДокументКредита.Дата, &ДатаОтчета, ДЕНЬ) КАК ДнейСОтгрузки,
    Остатки.Договор.ДопустимоеЧислоДнейЗадолженности КАК Отсрочка,
    ДОБАВИТЬКДАТЕ(Остатки.ДокументКредита.Дата, ДЕНЬ, Остатки.Договор.ДопустимоеЧислоДнейЗадолженности) КАК ДатаОплаты,
    РАЗНОСТЬДАТ(Остатки.ДокументКредита.Дата, &ДатаОтчета, ДЕНЬ) - Остатки.Договор.ДопустимоеЧислоДнейЗадолженности КАК СрокДолга,
    ВЫБОР
        КОГДА РАЗНОСТЬДАТ(Остатки.ДокументКредита.Дата, &ДатаОтчета, ДЕНЬ) - Остатки.Договор.ДопустимоеЧислоДнейЗадолженности > 60 ТОГДА 60
        КОГДА РАЗНОСТЬДАТ(Остатки.ДокументКредита.Дата, &ДатаОтчета, ДЕНЬ) - Остатки.Договор.ДопустимоеЧислоДнейЗадолженности > 45 ТОГДА 45
        КОГДА РАЗНОСТЬДАТ(Остатки.ДокументКредита.Дата, &ДатаОтчета, ДЕНЬ) - Остатки.Договор.ДопустимоеЧислоДнейЗадолженности > 30 ТОГДА 30
        ИНАЧЕ 0
    КОНЕЦ КАК ГруппаЗадолженности,
    Остатки.Договор.ВалютаВзаиморасчетов КАК Валюта
ИЗ
    ВТ_Остатки КАК Остатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Взаиморасчеты.Обороты(, &ДатаОтчета, Регистратор, ) КАК Обороты
        ПО Остатки.Фирма = Обороты.Фирма
        И Остатки.Договор = Обороты.Договор
        И Остатки.ДокументКредита = Обороты.ДокументКредита

Что изменилось в этом решении:

Мы гарантировали, что поля Фирма, Договор, Контрагент берутся из левой таблицы остатков. Даже если по документу не было оборотов в выбранном интервале, строка больше не будет пустой.
Использование функции ЕСТЬNULL(Обороты.СуммаРасход, 0) заменяет пустое значение оборота на законный ноль, предотвращая некорректное отображение числовых полей в отчете.
Соединение по полям Фирма, Договор и ДокументКредита исключает декартово произведение таблиц и дублирование сумм остатка.
Использование временной таблицы ВТ_Остатки с индексацией позволяет СУБД построить оптимальный план запроса, исключая зависания базы данных при больших объемах информации в регистрах.