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

← На главную

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

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

Анализ исходной проблемы: почему показатели «множатся»?

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


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

Проанализируем условие соединения в данном запросе:

ПО ЗаказыНаПроизводство.ДокЗаказ.ПодЗаказ.Ссылка = ЗаказПокупателяТЧ.Ссылка

Здесь кроется фундаментальная ошибка проектирования реляционных запросов. Мы связываем многострочную часть документа (табличную часть) с многострочной таблицей движений регистра только по одному общему полю — ссылке на документ-основание. На уровне СУБД такое действие порождает частичное декартово произведение (Cartesian product) строк в рамках каждого конкретного документа.

Представим простой пример:

В документе Заказ покупателя содержится 5 строк с разной номенклатурой (например, Стол, Стул, Шкаф, Тумба, Полка).
В регистре накопления Заказы на производство по этому заказу было сделано 3 записи (движения по производству стола, стула и шкафа).

Поскольку в условии соединения указано только равенство ссылок на документы, СУБД обязана сопоставить каждую строку из табличной части документа с каждой строкой из регистра. В результате для этого документа мы получим в выборке не 5 и не 3 строки, а 15 строк (5 строк ТЧ * 3 строки регистра). Показатели сумм и цен из заказа покупателя продублируются трижды для каждой номенклатуры, исказив конечный результат отчета.

Решение 1. Добавление условий связи по аналитике (Первый шаг)

Чтобы СУБД понимала, какая именно строка табличной части соответствует конкретной строке регистра накопления, нам необходимо связать их по ключевой аналитике — номенклатуре и характеристике (в исходном регистре она называется вариантом исполнения).

Давайте скорректируем условие связи в запросе:


ПО ЗаказыНаПроизводство.ДокЗаказ.ПодЗаказ = ЗаказПокупателяТЧ.Ссылка
    И ЗаказыНаПроизводство.Номенклатура = ЗаказПокупателяТЧ.Номенклатура
    И ЗаказыНаПроизводство.ВариантИсполнения = ЗаказПокупателяТЧ.Характеристика

Примечание: Обратите внимание, что обращение к свойству .Ссылка у поля, которое уже является ссылочным (как в ПодЗаказ.Ссылка), избыточно. Платформа 1С делает лишнюю работу по разыменованию ссылки. Пишите напрямую: ПодЗаказ.

После добавления этих условий СУБД соединит только те строки, где совпадают и документ, и сама номенклатура. На первый взгляд, это решает проблему. Однако на практике даже такое соединение может приводить к дублированию данных.

Почему простое соединение "в лоб" все еще может двоить данные?

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

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

Решение 2. Использование временных таблиц и предварительной группировки (Лучшая практика)

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

Разберем этот алгоритм по шагам:

Шаг 1. Подготовка и группировка данных документа

Сначала выберем данные из табличной части заказа покупателя. Обязательно сгруппируем их по ключевым полям (Ссылка, Номенклатура, Характеристика), а числовые показатели просуммируем. Это гарантирует, что для связки «Один заказ + Одна номенклатура» у нас останется строго одна уникальная строка.


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

Шаг 2. Подготовка и группировка данных регистра

Аналогично поступим с регистром накопления. Выберем нужные записи, сгруппируем их по документу-основанию, номенклатуре и характеристике, просуммировав ресурсы регистра.


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

Шаг 3. Итоговое левое соединение свернутых таблиц

Теперь, когда обе таблицы гарантированно уникальны в разрезе связки «Документ + Номенклатура + Характеристика», мы можем безопасно соединить их. Никакого умножения строк и дублирования сумм произойти физически не может.


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

Важный аспект оптимизации производительности: разыменование полей через точку

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

ЗаказыНаПроизводство.ДокЗаказ.ПодЗаказ.Автор

ЗаказыНаПроизводство.ДокЗаказ.ПодЗаказ.Ссылка

Использование нескольких точек («разыменование полей через точку» или Dot-Walking) в запросах 1С приводит к созданию СУБД скрытых неявных левых соединений (LEFT JOIN) со всеми таблицами метаданных, которые могут быть записаны в этих полях. Ситуация усугубляется, если реквизит имеет составной тип данных. В таком случае SQL-запрос превращается в огромную «колбасу» из соединений, парализуя работу сервера.

Как правильно поступать в таких случаях?

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

Резюме

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

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