Как правильно настроить ПОЛНОЕ СОЕДИНЕНИЕ в запросах 1С, чтобы избежать двоения строк?

← На главную

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

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

Выясняем причину: почему ПОЛНОЕ СОЕДИНЕНИЕ "раздваивает" строки?

Рассмотрим ситуацию на примере. У нас есть две временные таблицы: ПотребностиИРазмеры (план) и ОстаткиНазначениеИДатаСдачи (факт). Мы пытаемся связать их по двум полям: физическому лицу (ФизЛицо) и номенклатуре (Номенклатура).

Когда мы пишем условие связи:


ПО ПотребностиИРазмеры.ФизЛицо = ОстаткиНазначениеИДатаСдачи.СотрудникФизлицо
И ПотребностиИРазмеры.Номенклатура = ОстаткиНазначениеИДатаСдачи.Номенклатура

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

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

Решение №1. Объединение полей сопоставления через ЕСТЬNULL

Самый простой и быстрый способ исправить логику работы полного соединения без изменения структуры запроса — это объединить ключевые поля из обеих таблиц в единые результирующие поля с помощью функции ЕСТЬNULL. Разберем этот прием по шагам.

Вместо того чтобы выбирать поля отдельно, мы должны сказать системе: «Возьми значение из первой таблицы, а если там пусто (то есть NULL), возьми значение из второй таблицы». Это правило обязательно нужно применить как для физического лица, так и для сопоставляемой номенклатуры.

Посмотрим на пример правильной выборки в финальном запросе пакета:


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

Важный нюанс: обратите внимание, что мы сгруппировали результат именно по объединенным выражениям ЕСТЬNULL(...). Теперь СУБД понимает, что записи по одному и тому же сотруднику и одной и той же номенклатуре из разных источников должны "схлопнуться" в одну результирующую строку, даже если в одном из источников данные отсутствовали.

Решение №2. Оптимальный подход через ОБЪЕДИНИТЬ ВСЕ (Рекомендация по производительности)

Хотя первый вариант полностью решает проблему визуального двоения, с точки зрения стандартов разработки фирмы «1С» использование конструкции ПОЛНОЕ СОЕДИНЕНИЕ крайне нежелательно. На СУБД PostgreSQL такие запросы работают медленно и могут приводить к построению неоптимальных планов выполнения СУБД, особенно на крупных базах данных с тысячами сотрудников.

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

Алгоритм выглядит следующим образом: мы помещаем записи о потребностях и фактической выдаче в одну временную таблицу через объединение, приводя их к общему составу полей. На втором шаге мы просто группируем эту таблицу по сотруднику и номенклатуре, агрегируя остальные показатели максимумом.

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


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

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

ВЫБРАТЬ
    Выдача.СотрудникФизлицо,
    Выдача.Номенклатура,
    NULL,
    NULL,
    NULL,
    Выдача.Должность,
    Выдача.НазначениеИспользования,
    Выдача.Дата,
    Выдача.ОриентировочнаяДатаСдачи
ИЗ
    ОстаткиНазначениеИДатаСдачи КАК Выдача
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Финальная группировка и получение итоговых данных
ВЫБРАТЬ
    ВсеДанные.ФизЛицо КАК ФизЛицо,
    ВсеДанные.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ВсеДанные.РазмерОбуви) КАК РазмерОбуви,
    МАКСИМУМ(ВсеДанные.РазмерОдежды) КАК РазмерОдежды,
    МАКСИМУМ(ВсеДанные.Рост) КАК Рост,
    МАКСИМУМ(ВсеДанные.Должность) КАК Должность,
    МАКСИМУМ(ВсеДанные.НазначениеИспользования) КАК НазначениеИспользования,
    МАКСИМУМ(ВсеДанные.ДатаВыдачи) КАК ДатаВыдачи,
    МАКСИМУМ(ВсеДанные.ОриентировочнаяДатаСдачи) КАК ОриентировочнаяДатаСдачи
ИЗ
    ВсеДанные КАК ВсеДанные
СГРУППИРОВАТЬ ПО
    ВсеДанные.ФизЛицо,
    ВсеДанные.Номенклатура

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

Решение №3. Архитектурный подход в УПП 1.3 через Назначение использования

При сопоставлении плановой спецодежды и фактической выдачи часто возникает бизнес-проблема: по плану сотруднику положены, например, "Ботинки сварщика", а по факту на складе их не было, и ему выдали аналог — "Ботинки юфтевые". Если мы соединяем данные жестко по полям ФизЛицо и Номенклатура, то в отчете мы все равно получим две разные строки:

Строка с потребностью "Ботинки сварщика" (фактическая выдача будет пустой).
Строка с фактом выдачи "Ботинки юфтевые" (плановая потребность будет пустой).

Менеджер по снабжению решит, что сотруднику не выдали положенную обувь и при этом выдали лишнюю пару ботинок, хотя по факту норма выполнена.

Для корректного решения этой проблемы в конфигурации "Управление производственным предприятием 1.3" необходимо использовать аналитику Назначение использования:

В справочнике пакетов спецодежды (потребности) следует указывать не конкретные позиции номенклатуры, а категории потребности, завязанные на объект НазначениеИспользования.
При передаче материалов в эксплуатацию в документе обязательно заполняется реквизит НазначениеИспользования для каждой позиции.
Соединение плановых и фактических данных в запросе должно происходить по ключу: ФизЛицо + НазначениеИспользования (а не по номенклатуре).

При таком подходе запрос автоматически сопоставит выдачу аналогов. Мы сможем вывести в отчет плановое назначение использования, а рядом — фактически выданную номенклатуру из эксплуатации в одной строке.