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

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

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

Рассмотрим типовой ошибочный запрос, с которого началось обсуждение:

ВЫБРАТЬ
    ТоварыКПолучениюНаСкладыОстаткиИОбороты.ДокументПолучения,
    ТоварыКПолучениюНаСкладыОстаткиИОбороты.Номенклатура,
    ТоварыКПолучениюНаСкладыОстаткиИОбороты.ХарактеристикаНоменклатуры,
    ТоварыКПолучениюНаСкладыОстаткиИОбороты.СерияНоменклатуры,
    ТоварыКПолучениюНаСкладыОстаткиИОбороты.КоличествоКонечныйОстаток,
    РасходныйОрдерНаТоварыТовары.Ссылка,
    РасходныйОрдерНаТоварыТовары.Количество,
    РасходныйОрдерНаТоварыТовары.ЕдиницаИзмерения,
    РасходныйОрдерНаТоварыТовары.Коэффициент
ИЗ
    Документ.РасходныйОрдерНаТовары.Товары КАК РасходныйОрдерНаТоварыТовары
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыКПолучениюНаСклады.ОстаткиИОбороты(&НачДата, &КонДата, Регистратор, , ) КАК ТоварыКПолучениюНаСкладыОстаткиИОбороты
        ПО РасходныйОрдерНаТоварыТовары.Ссылка = ТоварыКПолучениюНаСкладыОстаткиИОбороты.Регистратор
ГДЕ
    РасходныйОрдерНаТоварыТовары.Ссылка.ДокументПередачи = &ПеремещениеТоваров

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

1. Неполный состав полей соединения (декартово произведение). Соединение выполняется только по условию Ссылка = Регистратор. Если в регистре учет ведется с дополнительной аналитикой (например, по характеристикам, сериям или складам), одна строка табличной части соединяется со всеми движениями документа в регистре, порождая кратное умножение строк.
2. Использование периодичности Регистратор в таблице ОстаткиИОбороты. При такой периодичности виртуальная таблица возвращает движения в разрезе каждого регистратора. Если один и тот же документ породил несколько движений по разной аналитике, на один документ сформируется несколько записей.
3. Прямое соединение табличной части документа с виртуальной таблицей. Такое соединение не только приводит к логическим ошибкам, но и крайне негативно сказывается на производительности СУБД, так как СУБД не может эффективно использовать индексы.

Способ 1. Получение данных только из регистра остатков (рекомендуемый подход)

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

Часто разработчики обращаются к табличной части только ради полей ЕдиницаИзмерения и Коэффициент. Однако эти данные являются условно-постоянными и привязаны к номенклатуре. Единица хранения остатков и ее коэффициент (всегда равный 1) берутся из реквизитов справочника Номенклатура.

Посмотрим на пример правильного запроса к виртуальной таблице Остатки без обращения к документам:

ВЫБРАТЬ
    ТоварыКПолучениюОстатки.ДокументПолучения КАК ДокументПолучения,
    ТоварыКПолучениюОстатки.Номенклатура КАК Номенклатура,
    ТоварыКПолучениюОстатки.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ТоварыКПолучениюОстатки.Номенклатура.ЕдиницаХраненияОстатков КАК ЕдиницаИзмерения,
    1 КАК Коэффициент,
    ТоварыКПолучениюОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыКПолучениюНаСклады.Остатки(
        &Период,
        ДокументПолучения = &ПеремещениеТоваров
    ) КАК ТоварыКПолучениюОстатки

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

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

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

1. Сначала поместим данные табличной части во временную таблицу и выполним обязательную группировку по ключевым измерениям с суммированием количеств.
2. Передадим фильтр по номенклатуре в параметры виртуальной таблицы регистра.
3. Соединим временную таблицу с регистром по полному набору измерений (Номенклатура, ХарактеристикаНоменклатуры и т.д.).

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

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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ХарактеристикаНоменклатуры
;

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

Рекомендации по оптимизации запросов

Для предотвращения ошибок и обеспечения высокой производительности базы данных придерживаемся следующих правил:

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