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

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

Рассмотрим подробнее, почему возникают подобные ошибки при полном соединении таблиц и как правильно организовать обработку значений NULL в языке запросов 1С.

Выясним причину: как работает полное соединение и NULL

Проанализируем ситуацию на примере пакета запросов, в котором собираются две временные таблицы:

1. МинМАКС — содержит список номенклатуры, максимальное и минимальное количество продаж, а также частоту покупок.
2. Возвр — содержит список номенклатуры и суммарное количество возвратов.

При соединении этих таблиц используется конструкция ПОЛНОЕ СОЕДИНЕНИЕ:

ВЫБРАТЬ
    МинМАКС.Номенклатура,
    МинМАКС.МаксКолРеал - Возвр.Возвраты КАК МаксКолРеал,
    МинМАКС.МинКолРеал - Возвр.Возвраты КАК МинКолРеал,
    МинМАКС.ЧастотаПокупок,
    Возвр.Возвраты
ИЗ
    МинМАКС КАК МинМАКС
    ПОЛНОЕ СОЕДИНЕНИЕ Возвр КАК Возвр
    ПО МинМАКС.Номенклатура = Возвр.Номенклатура

При использовании ПОЛНОЕ СОЕДИНЕНИЕ возможны три сценария развития событий для конкретной позиции номенклатуры:

1. По номенклатуре были и продажи, и возвраты. В обоих полях присутствуют числовые значения, арифметическая операция выполняются корректно.
2. По номенклатуре были только продажи. В таком случае в поле Возвр.Возвраты попадает значение NULL. Любое арифметическое действие со значением NULL (например, 10 - NULL) возвращает NULL.
3. По номенклатуре были только возвраты. В этом случае все поля таблицы МинМАКС (включая МинМАКС.МаксКолРеал) содержат NULL. Операция NULL - 5 также даст в результате NULL.

Ошибка неполной обработки значений NULL

Часто разработчики пытаются решить проблему, оборачивая в функцию ЕСТЬNULL только правое поле соединения:

МинМАКС.МаксКолРеал - ЕСТЬNULL(Возвр.Возвраты, 0) КАК МаксКолРеал

Разберем, почему эта конструкция не всегда работает. Для товаров, по которым не было продаж (но были возвраты), значение МинМАКС.МаксКолРеал равно NULL. В итоге вычисление принимает вид: NULL - 0, что снова приводит к значению NULL. Поле остается незаполненным, а строки могут пропадать или группироваться некорректно.

Решение проблемы: комплексное использование функции ЕСТЬNULL

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

Рассмотрим пошагово, как правильно составить итоговую выборку:

1. Обработка ключевого поля: так как при полном соединении поле номенклатуры из одной из таблиц может быть равным NULL, ключевое поле выбирается через комбинацию ЕСТЬNULL(МинМАКС.Номенклатура, Возвр.Номенклатура).
2. Обработка операндов вычисления: каждый операнд в математических выражениях должен быть обернут в ЕСТЬNULL(<Поле>, 0).

Посмотрим на исправленный итоговый запрос:

ВЫБРАТЬ
    ЕСТЬNULL(МинМАКС.Номенклатура, Возвр.Номенклатура) КАК Номенклатура,
    ЕСТЬNULL(МинМАКС.МаксКолРеал, 0) - ЕСТЬNULL(Возвр.Возвраты, 0) КАК МаксКолРеал,
    ЕСТЬNULL(МинМАКС.МинКолРеал, 0) - ЕСТЬNULL(Возвр.Возвраты, 0) КАК МинКолРеал,
    ЕСТЬNULL(МинМАКС.ЧастотаПокупок, 0) КАК ЧастотаПокупок,
    ЕСТЬNULL(Возвр.Возвраты, 0) КАК Возвраты
ИЗ
    МинМАКС КАК МинМАКС
        ПОЛНОЕ СОЕДИНЕНИЕ Возвр КАК Возвр
        ПО МинМАКС.Номенклатура = Возвр.Номенклатура

Рекомендации по оптимизации запросов и работы с СКД

При построении отчетов в СКД следует также учитывать особенности платформы 1С:

• Использование виртуальных таблиц: вместо прямого обращения к физической таблице регистра накопления (РегистрНакопления.РеализацияТМЗ) рекомендуется использовать виртуальную таблицу оборотов РегистрНакопления.РеализацияТМЗ.Обороты(). Это ускоряет выборку и снижает нагрузку на СУБД.
• Наложение отборов: отборы по дате, складу и контрагенту лучше передавать в параметры виртуальной таблицы, а не в секцию ГДЕ, чтобы отсекать лишние данные до их обработки в запросе.
• Использование нескольких наборов данных в СКД: альтернативным вариантом реализации является разделение запроса на два отдельных набора данных («Продажи» и «Возвраты») в схеме компоновки данных с последующей настройкой связей между ними на вкладке «Связи наборов данных». В этом случае СКД автоматически обработает отсутствующие записи без необходимости вручную прописывать ЕСТЬNULL.