Как объединить поле Номенклатура из двух и более таблиц в запросе 1С без дублирования данных?

← На главную

При разработке сложных отчетов в системе 1С (особенно для конфигураций уровня ERP, Комплексная автоматизация или Управление торговлей) программисты часто сталкиваются с задачей сопоставления данных из разных источников. Типичный пример: необходимо вывести в одну строку информацию по Заявкам на перемещение, связанным с ними Заказам поставщикам и фактическим Поступлениям (Закупкам).

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

Метод 1. Полное соединение и выборка через конструкцию ВЫБОР КОГДА

Если нам нужно сопоставить две таблицы (например, Заявки и Заказы), и мы точно знаем, что связь между ними строится по уникальным ключам, мы можем применить ПОЛНОЕ СОЕДИНЕНИЕ. Чтобы получить единую колонку номенклатуры, применим условную конструкцию ВЫБОР КОГДА.

Разберем этот подход на примере следующего запроса:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    вт_Зяавки.Заявка КАК Заявка,
    вт_Зяавки.Склад КАК Склад,
    вт_Зяавки.Количество КАК КоличествоЗаявка,
    вт_Заказы.Заказ КАК Заказ,
    вт_Заказы.Количество КАК КоличествоЗаказ,
    // Объединяем номенклатуру в одно поле
    ВЫБОР
        КОГДА вт_Зяавки.Номенклатура ЕСТЬ NULL ТОГДА вт_Заказы.Номенклатура
        ИНАЧЕ вт_Зяавки.Номенклатура
    КОНЕЦ КАК Номенклатура,
    // Объединяем характеристику в одно поле
    ВЫБОР
        КОГДА вт_Зяавки.Характеристика ЕСТЬ NULL ТОГДА вт_Заказы.Характеристика
        ИНАЧЕ вт_Зяавки.Характеристика
    КОНЕЦ КАК Характеристика
ИЗ
    вт_Зяавки КАК вт_Зяавки
    ПОЛНОЕ СОЕДИНЕНИЕ вт_Заказы КАК вт_Заказы
    ПО вт_Зяавки.Заявка = вт_Заказы.Заявка
    И вт_Зяавки.Номенклатура = вт_Заказы.Номенклатура
    И вт_Зяавки.Характеристика = вт_Заказы.Характеристика

Важный нюанс: Обратите внимание на условие связи ПО. Мы обязательно должны связывать таблицы не только по документам (Заявкам), но и по Номенклатуре и Характеристике. Если этого не сделать, строки с разной номенклатурой внутри одного документа перепутаются, и СУБД выдаст неадекватное декартово произведение строк.

Метод 2. Объединение таблиц (ОБЪЕДИНИТЬ ВСЕ) с последующей группировкой

Хотя первый метод с полным соединением интуитивно понятен, он имеет серьезные недостатки с точки зрения производительности СУБД (особенно на платформе PostgreSQL). Стандарты разработки фирмы "1С" рекомендуют заменять полные соединения конструкцией ОБЪЕДИНИТЬ ВСЕ с последующей группировкой во временной таблице.

Давайте посмотрим, как изящно решить задачу этим способом:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    вз_Итог.Заявка КАК Заявка,
    вз_Итог.Склад КАК Склад,
    вз_Итог.Заказ КАК Заказ,
    вз_Итог.Номенклатура КАК Номенклатура,
    вз_Итог.Характеристика КАК Характеристика,
    СУММА(вз_Итог.КоличествоЗаявка) КАК КоличествоЗаявка,
    СУММА(вз_Итог.КоличествоЗаказ) КАК КоличествоЗаказ
ПОМЕСТИТЬ вт_Итог
ИЗ
    (ВЫБРАТЬ
        вт_Зяавки.Заявка КАК Заявка,
        вт_Зяавки.Склад КАК Склад,
        НЕОПРЕДЕЛЕНО КАК Заказ,
        вт_Зяавки.Номенклатура КАК Номенклатура,
        вт_Зяавки.Характеристика КАК Характеристика,
        вт_Зяавки.Количество КАК КоличествоЗаявка,
        0 КАК КоличествоЗаказ
    ИЗ
        вт_Зяавки КАК вт_Зяавки

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

    ВЫБРАТЬ
        вт_Заказы.Заявка КАК Заявка,
        вт_Заказы.Склад КАК Склад,
        вт_Заказы.Заказ КАК Заказ,
        вт_Заказы.Номенклатура КАК Номенклатура,
        вт_Заказы.Характеристика КАК Характеристика,
        0 КАК КоличествоЗаявка,
        вт_Заказы.Количество КАК КоличествоЗаказ
    ИЗ
        вт_Заказы КАК вт_Заказы) КАК вз_Итог

СГРУППИРОВАТЬ ПО
    вз_Итог.Заявка,
    вз_Итог.Склад,
    вз_Итог.Заказ,
    вз_Итог.Номенклатура,
    вз_Итог.Характеристика

При таком подходе поле Номенклатура изначально находится в одной колонке в обеих частях объединения. Нам не нужно писать сложные условия проверки на NULL, а СУБД моментально "схлопывает" одинаковые строки при помощи секции СГРУППИРОВАТЬ ПО.

Проблема подключения третьей таблицы (Закупки) и борьба с дублями

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

Выясним причину: если по одному заказу было несколько поступлений, то при обычном соединении строка заказа размножится на количество строк поступлений. Соответственно, количество по заявке и заказу увеличится в несколько раз.

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

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

Выделяем поступления, привязанные к заказам, во временную таблицу вт_Поступления_ПоЗаказам.
Выделяем "сиротские" поступления (без заказов) в таблицу вт_Поступления_БезЗаказов.
К основной таблице итогов (Заявки + Заказы) присоединяем первую таблицу поступлений через ЛЕВОЕ СОЕДИНЕНИЕ по жесткой связке ключей: Номенклатура + Характеристика + Заказ. Это исключит дублирование, так как связь идет по конкретному заказу.
Добавляем поступления без заказов через ОБЪЕДИНИТЬ ВСЕ.

Реализуем эту логику на практике:


// 1. Поступления по заказам
ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Закупки.Заказ КАК Заказ,
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика КАК Характеристика,
    СУММА(Закупки.Количество) КАК Количество
ПОМЕСТИТЬ вт_Поступления_ПоЗаказам
ИЗ
    РегистрНакопления.Закупки КАК Закупки
ГДЕ
    Закупки.Регистратор ССЫЛКА Документ.ПриобретениеТоваровУслуг
    И НЕ Закупки.Заказ = ЗНАЧЕНИЕ(Документ.ЗаказПоставщику.ПустаяСсылка)
СГРУППИРОВАТЬ ПО
    Закупки.Заказ,
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика
;

// 2. Поступления БЕЗ заказов
ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Закупки.Регистратор КАК Поступление,
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика КАК Характеристика,
    СУММА(Закупки.Количество) КАК Количество
ПОМЕСТИТЬ вт_Поступления_БезЗаказов
ИЗ
    РегистрНакопления.Закупки КАК Закупки
ГДЕ
    Закупки.Регистратор ССЫЛКА Документ.ПриобретениеТоваровУслуг
    И Закупки.Заказ = ЗНАЧЕНИЕ(Документ.ЗаказПоставщику.ПустаяСсылка)
СГРУППИРОВАТЬ ПО
    Закупки.Регистратор,
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика
;

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

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

ВЫБРАТЬ
    NULL,
    NULL,
    NULL,
    ПоступленияБезЗаказов.Номенклатура,
    ПоступленияБезЗаказов.Характеристика,
    0,
    0,
    ПоступленияБезЗаказов.Количество
ИЗ
    вт_Поступления_БезЗаказов КАК ПоступленияБезЗаказов

Важные правила работы со значениями NULL и 0

Обратите внимание на то, как мы инициализируем числовые поля. Вместо передачи значения NULL для отсутствующих показателей мы явно пишем 0 (либо используем функцию ЕСТЬNULL(Поле, 0)).

Почему это важно? В языке запросов 1С и СУБД при операциях сложения или вычитания полей, если хотя бы одно из них содержит значение NULL, результатом всей операции станет NULL (например, 5 + NULL = NULL). Чтобы избежать потери данных при дальнейших расчетах или агрегации в СКД, всегда заменяйте пустые числовые значения нулями.

Решение задачи на уровне СКД (Система компоновки данных)

Если этот отчет создается на базе СКД, мы можем полностью отказаться от громоздких и тяжелых запросов с полными соединениями. Нам на помощь приходит механизм Набор данных - Объединение.

Выполним следующие шаги в конструкторе схемы компоновки данных:

Создадим три независимых набора данных (запроса): НаборДанныхЗаявки, НаборДанныхЗаказы и НаборДанныхЗакупки.
В каждом из этих запросов выберем только свои показатели, но поля Номенклатура и Характеристика назовем абсолютно одинаково.
Добавим новый набор данных с типом Набор данных - Объединение и перетащим в него все три созданных запроса.
На вкладке "Настройки" добавим группировку по полю Номенклатура.

Платформа СКД автоматически спроецирует все три источника на общие поля связи, выполнит корректное сложение ресурсов и выведет красивый плоский отчет без дублей и зависаний СУБД.