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

← На главную

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

В этой статье мы подробно и по шагам разберем, почему возникает эта проблема, проанализируем различные подходы к ее решению, сравним их производительность и рассмотрим лучшие практики разработки по стандартам фирмы «1С».

Выясняем причину: почему происходит задвоение строк?

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


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

С точки зрения реляционной алгебры, связь между продажами номенклатуры и ее упаковками классифицируется как отношение «один ко многим». У одного товара (владельца) может быть создано несколько вариантов упаковок (например: «Коробка 10 шт.», «Паллета 100 шт.», «Упаковка 5 шт.»). При выполнении левого соединения СУБД сопоставляет каждую строку продаж со всеми подходящими строками из справочника упаковок. В результате мы получаем столько дублей исходной строки продаж, сколько упаковок зарегистрировано для данной номенклатуры.

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

Решение 1. Сворачивание правой таблицы во вложенном запросе

Рассмотрим самый простой и очевидный способ, который часто применяется для быстрого решения задачи на форумах. Если нам не важно, какую именно упаковку выбрать (нужно взять «первую попавшуюся»), мы можем заранее сгруппировать правую таблицу по полю связи и вычислить агрегатную функцию (например, МАКСИМУМ или МИНИМУМ) по ключевому полю.

Давайте посмотрим на пример реализации этого метода:


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

Как это работает: во вложенном запросе мы обращаемся к справочнику упаковок, группируем все записи по полю Владелец и для каждого владельца выбираем максимальную ссылку (уникальный идентификатор GUID). Поскольку GUID каждой ссылки уникален, функция МАКСИМУМ гарантирует получение ровно одной уникальной записи для каждого товара. Затем мы соединяем полученную виртуальную таблицу с продажами.

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

Решение 2. Оптимизация производительности через временные таблицы (Стандарт 1С)

Чтобы соответствовать официальным стандартам разработки «1С:Предприятия» и гарантировать высокую скорость работы системы при любых объемах данных, мы должны переписать наш запрос с использованием механизма временных таблиц и их индексации.

Разберем этот процесс по шагам:

Создаем первый запрос пакета, в котором получаем уникальные пары «Владелец — Упаковка».
Помещаем этот результат во временную таблицу.
Обязательно индексируем временную таблицу по полю связи.
Во втором запросе пакета выполняем левое соединение основной таблицы продаж с подготовленной временной таблицей.

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


ВЫБРАТЬ
    Упаковки.Владелец КАК Владелец,
    МАКСИМУМ(Упаковки.Ссылка) КАК Упаковка
ПОМЕСТИТЬ ВТ_ОднозначныеУпаковки
ИЗ
    Справочник.УпаковкиНоменклатуры КАК Упаковки
СГРУППИРОВАТЬ ПО
    Упаковки.Владелец

ИНДЕКСИРОВАТЬ ПО
    Владелец
;

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

Благодаря созданию индекса по полю Владелец во временной таблице ВТ_ОднозначныеУпаковки, СУБД выполнит соединение мгновенно, используя эффективные алгоритмы поиска (например, Index Seek или Merge Join), что полностью исключает просадки производительности.

Решение 3. Двойное левое соединение для выбора конкретной (целевой) записи

Ситуация усложняется, если бизнес-логика требует выбрать не случайную («первую попавшуюся») упаковку, а строго определенную по заданному критерию. Например, нам нужно выбрать упаковку с максимальным коэффициентом вместимости, чтобы минимизировать объем логистических затрат при заказе.

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

В первой временной таблице мы группируем справочник упаковок по владельцу и находим максимальное значение критерия (например, МАКСИМУМ(Коэффициент)).
Во второй временной таблице мы соединяем исходный справочник упаковок с первой временной таблицей по двум полям: по владельцу и по значению коэффициента. Из этого соединения мы получаем саму ссылку на упаковку.
В финальном запросе пакета мы соединяем наши продажи с подготовленной чистой таблицей «Владелец — Упаковка».

Проанализируем пример кода для реализации этой схемы:


ВЫБРАТЬ
    Упаковки.Владелец КАК Владелец,
    МАКСИМУМ(Упаковки.Коэффициент) КАК МаксКоэффициент
ПОМЕСТИТЬ ВТ_МаксимальныеКоэффициенты
ИЗ
    Справочник.УпаковкиНоменклатуры КАК Упаковки
СГРУППИРОВАТЬ ПО
    Упаковки.Владелец

ИНДЕКСИРОВАТЬ ПО
    Владелец,
    МаксКоэффициент
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Упаковки.Владелец КАК Владелец,
    МАКСИМУМ(Упаковки.Ссылка) КАК Упаковка
ПОМЕСТИТЬ ВТ_ЦелевыеУпаковки
ИЗ
    ВТ_МаксимальныеКоэффициенты КАК МаксКоэф
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.УпаковкиНоменклатуры КАК Упаковки
        ПО МаксКоэф.Владелец = Упаковки.Владелец
        И МаксКоэф.МаксКоэффициент = Упаковки.Коэффициент
СГРУППИРОВАТЬ ПО
    Упаковки.Владелец

ИНДЕКСИРОВАТЬ ПО
    Владелец
;

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

Обратите внимание, что во втором запросе пакета мы повторно использовали группировку СГРУППИРОВАТЬ ПО Упаковки.Владелец и функцию МАКСИМУМ(Упаковки.Ссылка). Это необходимо для защиты от редких ситуаций, когда у одного товара создано несколько упаковок с абсолютно одинаковым (максимальным) коэффициентом. Такой подход гарантирует стопроцентное отсутствие дублей на выходе.

Решение 4. Использование Системы компоновки данных (СКД)

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

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

Создайте Набор данных - запрос (например, НаборДанныхПродажи), в котором выберите только информацию о продажах (без каких-либо соединений со справочником упаковок).
Создайте второй Набор данных - запрос (например, НаборДанныхУпаковки), который выбирает владельца и упаковку из справочника. Чтобы избежать избыточности, во втором запросе можно наложить фильтр по используемым товарам.
Перейдите на вкладку «Связи наборов данных». Добавьте новую строку связи.
Укажите НаборДанныхПродажи в качестве источника связи, а НаборДанныхУпаковки — в качестве приемника. Укажите поле связи (Номенклатура -> Владелец).
В колонке «Параметры связи» можно задать передачу списка номенклатуры в качестве параметра во второй запрос для оптимизации выборки.

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

Решение 5. Фильтрация на уровне условий связи (бизнес-логика конфигурации)

Иногда дублирования можно избежать, более детально изучив метаданные используемой конфигурации (например, «ERP Управление предприятием», «Управление торговлей» или «Розница»). Зачастую в самой номенклатуре уже содержатся ссылки на предопределенные или наиболее важные единицы измерения.

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


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

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

Ограничения платформы 1С: Почему нет оконных функций?

Разработчики, пришедшие в 1С из классического веб-девелопмента или администрирования баз данных (MS SQL, PostgreSQL, Oracle), часто задаются вопросом: почему нельзя использовать стандартную аналитическую конструкцию ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) с последующей фильтрацией по индексу строки?

Ответ кроется в архитектуре платформы. Язык запросов 1С (SDBL) является кроссплатформенным транслятором, задача которого — обеспечивать одинаковую работу бизнес-логики на различных СУБД (включая файловые базы данных, в которых оконные функции физически не поддерживаются). Из-за необходимости поддерживать единый стандарт SDBL не содержит в себе оконных функций. Поэтому описанные выше обходные пути с агрегацией во временных таблицах или использованием механизмов СКД остаются единственными надежными инструментами в арсенале 1С-разработчика.

Подведем итоги

Мы рассмотрели различные методы борьбы с дублированием строк при левых соединениях:

Вложенный запрос с группировкой подходит для простых и быстрых отладочных задач, но не рекомендован для промышленных решений из-за снижения производительности СУБД.
Использование временных таблиц с индексацией — золотой стандарт разработки в 1С, гарантирующий оптимальный план запроса и высокую скорость выполнения.
Двойное левое соединение позволяет извлекать записи по строго заданным бизнес-критериям (максимальный коэффициент, минимальный приоритет и т.д.).
Связи наборов данных в СКД идеально подходят для построения сложных отчетов с сохранением корректности числовых ресурсов.