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

← На главную

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

Разбор проблемы: почему данные «размножаются»

Проанализируем ситуацию, когда мы обращаемся к виртуальной таблице Обороты регистра накопления ВыручкаИСебестоимостьПродаж. Если в одном запросе мы пытаемся соединить данные, сгруппированные по разным измерениям или периодам, СУБД создает так называемое декартово произведение. Например, если для одной номенклатуры в левой таблице есть три записи по месяцам, а в правой — одна общая запись с оборотом, то после соединения общая запись приклеится к каждой из трех строк. При последующем суммировании в итогах мы получим утроенное значение оборота.

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

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

Шаг 1. Оптимизация получения данных о себестоимости

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


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

Обратите внимание: Мы изменили периодичность виртуальной таблицы с Авто на Месяц. Это критически важно для производительности. При значении Авто система пытается вытащить данные с детальностью до регистратора, что при соединении с документами расчетов вызывает лавинообразный рост количества строк. Использование Месяц позволяет СУБД обращаться к заранее подготовленным итогам.

Шаг 2. Расчет общих оборотов без привязки к периоду

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


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

Шаг 3. Правильное соединение в итоговой выборке

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


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

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

Решение через механизмы СКД (Связи наборов данных)

Если запрос получается слишком тяжелым и «жрет» память, как жаловался автор темы, лучшим решением будет использование двух разных наборов данных в схеме компоновки данных. Рассмотрим этот алгоритм:

Создаем НаборДанных1 (Продажи): Здесь пишем простой запрос к оборотам с периодичностью «Месяц» и параметрами &НачалоПериода, &КонецПериода.
Создаем НаборДанных2 (Общие показатели): Пишем запрос, который получает итоги без разреза по периодам (или за более широкий интервал).
Настраиваем вкладку «Связи наборов данных»:
- Источник связи: НаборДанных1, Приемник: НаборДанных2.
- Выражение связи: Номенклатура = Номенклатура.
- Важно: Не связывайте их по периоду, если вам нужно вывести общие данные рядом с месячными.

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

Советы по работе с аналитикой учета номенклатуры

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

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

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