Связи и объединение наборов данных в СКД 1С без задвоения сумм

Как устроена закладка «Связи наборов данных»

Когда данные отчёта поступают из нескольких запросов или внешних таблиц значений, их соединение описывается на закладке «Связи наборов данных» конструктора схемы компоновки данных. Каждая строка этой таблицы представляет объект СвязьНаборовДанныхСхемыКомпоновкиДанных и связывает два набора по принципу ЛЕВОЕ СОЕДИНЕНИЕ (Left Outer Join): в результат попадают все записи из набора-источника и подходящие по ключу записи из набора-приёмника.

В отличие от соединения таблиц внутри запроса, которое выполняется на стороне СУБД, связь наборов данных в СКД 1С 8.3 обрабатывает сам ПроцессорКомпоновкиДанных в контуре выполнения компоновки. Не выводите связанные по бизнес-смыслу показатели без согласованного ключа и проверки результата.

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

// Программное добавление связи по двум полям (Контрагент И Договор)
СвязьКонтрагент = Схема.СвязиНаборовДанных.Добавить();
СвязьКонтрагент.НаборДанныхИсточник = "НаборПродажи";
СвязьКонтрагент.НаборДанныхПриемник = "НаборОплаты";
СвязьКонтрагент.ВыражениеИсточник   = "Контрагент";
СвязьКонтрагент.ВыражениеПриемник   = "Контрагент";
СвязьКонтрагент.Обязательная        = Истина;

СвязьДоговор = Схема.СвязиНаборовДанных.Добавить();
СвязьДоговор.НаборДанныхИсточник = "НаборПродажи";
СвязьДоговор.НаборДанныхПриемник = "НаборОплаты";
СвязьДоговор.ВыражениеИсточник   = "Договор";
СвязьДоговор.ВыражениеПриемник   = "Договор";
СвязьДоговор.Обязательная        = Истина;

Обратите внимание на флаг «Обязательная» (свойство Обязательная). По умолчанию он установлен в Истина: связь добавляется в макет компоновки всегда, когда в настройках отчёта задействовано хотя бы одно поле из набора-приёмника. Если снять этот флаг (Ложь), связь сработает только тогда, когда в отчёте явно используются поля из ВыражениеИсточник.

Параметры связи, условие связи и иерархия

Помимо базовых полей соединения, у связи наборов данных есть дополнительные настройки, которые управляют фильтрацией запроса приёмника, условным присоединением строк и обходом иерархии:

• Параметр связи (в конструкторе — «Параметр связи», в объектной модели — свойство Параметр) и флаг «Список параметров» (в конструкторе — «Параметры связи», в коде — РазрешенСписокПараметров). Без параметра связи ключи источника этим механизмом в запрос приёмника не передаются; его собственные условия, параметры периода и отборы продолжают действовать. Если указать имя параметра запроса приёмника в свойстве Параметр, процессор компоновки соберёт значения ВыражениеИсточник из первого набора и передаст их в запрос второго набора.
• При снятом флаге РазрешенСписокПараметров запрос приёмника выполняется в цикле отдельно для каждой строки источника (это необходимо, например, когда в параметр виртуальной таблицы СрезПоследних(&ПериодСреза, ...) нужно передавать одиночную дату из каждой строки левого набора). Если же в запросе приёмника используется условие ГДЕ Номенклатура В (&СписокНоменклатуры) и включён флаг РазрешенСписокПараметров = Истина, СКД передаёт массив ключей порциями и выполняет запрос приёмника пакетно, сокращая число обращений в подходящих сценариях; выигрыш проверяют измерением.
• Условие связи (свойство УсловиеСвязи) — выражение на языке выражений СКД, которое проверяется перед соединением строк. Справка определяет его как проверку выражения перед выполнением связи. Документированный пример — ЭтоГруппа для перехода к детям при самосвязи. Не считайте это универсальным аналогом SQL ON с одновременным доступом к полям обеих строк; интервальное сопоставление подготовьте в запросе. Пустое условие не ограничивает выполнение связи.
• Начальное выражение источника (в конструкторе — «Начальное выражение источника», в коде — НачальноеВыражение) — используется, когда набор данных связывается сам с собой для построения иерархии (например, связь поля Ссылка с полем Родитель). Свойство содержит выражение начального значения источника, например Значение(Справочник.Номенклатура.ПустаяСсылка), а не булево условие сравнения родителя.

// Текст запроса НаборДанныхПриемник (НаборЦены):
// ВЫБРАТЬ Номенклатура, Цена ИЗ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&Период, Номенклатура В (&СписокНоменклатуры))

Связь = Схема.СвязиНаборовДанных.Добавить();
Связь.НаборДанныхИсточник      = "НаборТовары";
Связь.НаборДанныхПриемник      = "НаборЦены";
Связь.ВыражениеИсточник        = "Номенклатура";
Связь.ВыражениеПриемник        = "Номенклатура";
Связь.Параметр                 = "СписокНоменклатуры";
Связь.РазрешенСписокПараметров = Истина;
Связь.УсловиеСвязи             = "Активен = Истина";

Дубли в запросе и связь наборов: разные причины итогов

Контрольный пример: первый набор содержит один заказ на 100, второй — три оплаты 30, 30 и 40. При связи этих отдельных наборов ресурс Сумма(СуммаЗаказа) не обязан становиться равным 300. На платформе 8.3.27.1989 проверен аналогичный набор: в трёх дочерних строках выводится 100, но общий итог равен 100. Складывать видимые повторения родительского показателя вручную нельзя.

Иначе работает соединение в самом запросе: если заказ соединён с тремя строками оплаты, запрос уже возвращает три исходные записи с суммой заказа 100. Устранение этой кардинальности — задача запроса. Проверьте полноту ключа, предварительную агрегацию и бизнес-смысл каждой строки; не подменяйте сумму максимумом без анализа.

Как выбрать связь, объединение или пакетный запрос

• Связь — для сопоставления наборов по согласованному ключу и вывода родительских и дочерних данных.
• Объединение — для сведения совместимых показателей из разных наборов в общий поток и группировки.
• Пакетный запрос — для подготовки данных внутри базы, когда необходимо управлять промежуточными результатами и ключами.

Ни один вариант не исправляет ошибочную детальность источника автоматически. Сверяйте каждый показатель с контрольным запросом.

Способ 1. Набор данных — объединение вместо связи

Если в отчёте нужно сопоставить обороты или остатки из двух независимых источников (например, «План продаж» и «Факт продаж», либо «Отгрузки из запроса» и «Оплаты из внешней ТаблицыЗначений»), вместо создания двух наборов верхнего уровня и связи между ними создайте Набор данных — объединение (тип НаборДанныхОбъединениеСхемыКомпоновкиДанных).

Внутри набора-объединения в коллекции Элементы размещаются подчинённые наборы данных любого типа: НаборДанныхЗапросСхемыКомпоновкиДанных, НаборДанныхОбъектСхемыКомпоновкиДанных или даже вложенное объединение. В отличие от конструкции ОБЪЕДИНИТЬ ВСЕ языка запросов 1С, в подчинённых наборах объединения СКД не требуется одинаковое количество и порядок колонок: значения сопоставляются по пути к данным. Свойство Поле указывает физическое поле источника, а ПутьКДанным — его логический путь; эти свойства не следует объявлять синонимами.

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

// Создаём корневой набор данных — объединение
НаборОбъединение = Схема.НаборыДанных.Добавить(
    Тип("НаборДанныхОбъединениеСхемыКомпоновкиДанных")
);
НаборОбъединение.Имя = "ПродажиИОплаты";

// 1-й подчинённый набор внутри объединения (Продажи)
НаборПродажи = НаборОбъединение.Элементы.Добавить(
    Тип("НаборДанныхЗапросСхемыКомпоновкиДанных")
);
НаборПродажи.Имя = "Продажи";
НаборПродажи.ИсточникДанных = "ИсточникДанных1";
НаборПродажи.АвтоЗаполнениеДоступныхПолей = Истина;
НаборПродажи.Запрос =
    "ВЫБРАТЬ
    |   ПродажиОбороты.Контрагент КАК Контрагент,
    |   ПродажиОбороты.Договор КАК Договор,
    |   ПродажиОбороты.СуммаОборот КАК СуммаПродажи,
    |   0 КАК СуммаОплаты
    |ИЗ
    |   РегистрНакопления.Продажи.Обороты(&НачалоПериода, &КонецПериода, , ) КАК ПродажиОбороты";

// 2-й подчинённый набор внутри объединения (Оплаты)
НаборОплаты = НаборОбъединение.Элементы.Добавить(
    Тип("НаборДанныхЗапросСхемыКомпоновкиДанных")
);
НаборОплаты.Имя = "Оплаты";
НаборОплаты.ИсточникДанных = "ИсточникДанных1";
НаборОплаты.АвтоЗаполнениеДоступныхПолей = Истина;
НаборОплаты.Запрос =
    "ВЫБРАТЬ
    |   РасчетыОбороты.Контрагент КАК Контрагент,
    |   РасчетыОбороты.Договор КАК Договор,
    |   РасчетыОбороты.СуммаВзаиморасчетовРасход КАК СуммаОплаты,
    |   0 КАК СуммаПродажи
    |ИЗ
    |   РегистрНакопления.ВзаиморасчетыСКонтрагентами.Обороты(&НачалоПериода, &КонецПериода, , ) КАК РасчетыОбороты";

Проверка объединения: порядок полей и контрольные строки

Для воспроизводимого примера задайте первому вложенному запросу ВЫБРАТЬ "A" КАК Код, "Первая" КАК Название, второму — ВЫБРАТЬ "Вторая" КАК Название, "B" КАК Код. На 8.3.27.1989 результат объединения при выборе Код/Название — A/Первая и B/Вторая: порядок колонок вложенного запроса не определяет сопоставление.

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

Колонка «Рассчитывать по» задаёт область формулы ресурса и её применение во вложенных группировках, а не удаляет дубли исходного запроса. Подробнее — ресурсы и уровни итогов.

Источник: руководство разработчика 8.3.27, §10.3.1.2.3, §10.3.1.5 и §10.3.5.5. Для самосвязи см. собственную иерархию.

Частые ошибки при связях наборов данных в СКД

• Связь двух регистров только по верхнему измерению («многие ко многим»). Если в первом наборе строки сгруппированы по Контрагент + ДокументРеализации, во втором — по Контрагент + ДокументОплаты, а связь задана только по полю Контрагент, вы можете получить не требуемое бизнесом сопоставление документов. Для независимых потоков рассмотрите НаборДанныхОбъединениеСхемыКомпоновкиДанных.
• Совпадающие имена числовых полей в разных наборах. Если и в источнике, и в приёмнике поле суммы названо одинаково (Сумма), нужно явно согласовать путь к данным и смысл показателя. Давайте числовым ресурсам разных наборов уникальные псевдонимы (СуммаЗаказа, СуммаОплаты), Для объединения наборов общие пути, включая числовые показатели, наоборот согласуют намеренно — как в примере выше.
• Указан «Параметр связи», но забыт флаг «Параметры связи» (Список параметров) при операторе В (&Параметр). Если в запросе приёмника написано ГДЕ Ссылка В (&СписокСсылок), а свойство РазрешенСписокПараметров оставлено в Ложь, СКД будет выполнять запрос приёмника по одному элементу столько раз, сколько строк вернул источник.
• Отбор по полю приёмника превращает левое соединение во внутреннее. Когда пользователь накладывает в настройках отчёта обычный отбор на поле набора-приёмника (например, СуммаОплаты > 0), строки источника, у которых в приёмнике оказалось NULL, пропадают из отчёта. Если нужно фильтровать только присоединяемые строки, сохраняя все записи источника, подготовьте отбор в запросе приёмника и отдельно проверьте сохранение записей источника.
• Ошибки при программном обращении к свойствам связи. В Синтаксис-помощнике имена свойств объекта СвязьНаборовДанныхСхемыКомпоновкиДанных отличаются от подписей колонок конструктора: вместо «Источник связи» в коде пишется НаборДанныхИсточник, вместо «Параметр связи» — Параметр, а вместо «Параметры связи» — РазрешенСписокПараметров.
• Не снят флаг «Обязательная» у вспомогательного набора. При установленном флаге Обязательная = Истина запрос приёмника будет выполняться всегда, когда его поля присутствуют в макете, даже если ключевое поле источника не выведено в структуру текущего варианта отчёта.

Частые вопросы по связям и объединению наборов в СКД