При работе с Системой Компоновки Данных (СКД) в 1С мы часто сталкиваемся с необходимостью объединять данные из различных источников. Одним из распространенных сценариев является вывод информации о продажах (или других оборотах) вместе со свойствами объектов, например, контрагентов. Однако, когда у одного объекта (контрагента) имеется несколько значений свойств, а мы используем левое соединение (LEFT JOIN), это может привести к задвоению или даже учетверению ресурсов в итоговом отчете. Давайте разберем эту проблему подробнее и найдем эффективные решения.
Представим ситуацию: нам нужно сформировать отчет по продажам, и в этом отчете мы хотим видеть не только стандартные показатели (количество, стоимость, НДС), но и некоторые свойства контрагентов, например, "Доставка" или "Вид деятельности". Если у контрагента задано несколько таких свойств, отчет может начать показывать некорректные, завышенные суммы.
Рассмотрим типовой запрос, который приводит к этой проблеме. Изначальный запрос пользователя выглядел следующим образом:
ВЫБРАТЬ
ЗначенияСвойствОбъектов.Объект,
ЗначенияСвойствОбъектов.Свойство,
ЗначенияСвойствОбъектов1.Ссылка
ПОМЕСТИТЬ ВТ_Свойства
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов
ЛЕВОЕ СОЕДИНЕНИЕ ПланВидовХарактеристик.СвойстваОбъектов КАК СвойстваОбъектов
ПО ЗначенияСвойствОбъектов.Свойство = СвойстваОбъектов.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ЗначенияСвойствОбъектов КАК ЗначенияСвойствОбъектов1
ПО ЗначенияСвойствОбъектов.Значение = ЗначенияСвойствОбъектов1.Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПродажиОбороты.Контрагент,
ПродажиОбороты.Номенклатура,
ПродажиОбороты.СтоимостьОборот КАК Стоимость,
ПродажиОбороты.КоличествоОборот КАК Количество,
ПродажиОбороты.НДСОборот КАК НДС,
ПродажиОбороты.Организация,
ПродажиОбороты.Регистратор,
ВТ_Свойства.Объект,
ВТ_Свойства.Свойство,
ВТ_Свойства.Ссылка,
ПродажиОбороты.Регистратор.Ссылка
ИЗ
РегистрНакопления.Продажи.Обороты(, , Регистратор, ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Свойства КАК ВТ_Свойства
ПО ПродажиОбороты.Контрагент = ВТ_Свойства.Объект
В этом запросе мы видим, что сначала формируется временная таблица ВТ_Свойства, которая содержит объект (контрагента), его свойство и значение свойства. Затем эта временная таблица соединяется с оборотами по продажам с помощью левого соединения (ЛЕВОЕ СОЕДИНЕНИЕ) по полю ПродажиОбороты.Контрагент = ВТ_Свойства.Объект.
Ключевой момент здесь — это наличие у контрагента нескольких свойств. Например, у Контрагента А есть свойство "Доставка" и свойство "Вид деятельности". Когда мы выполняем левое соединение, для каждой строки из регистра накопления "ПродажиОбороты" (например, одна строка по продаже Номенклатуры 1 Контрагенту А), будет найдено две соответствующие строки в ВТ_Свойства (одна для "Доставки", другая для "Вида деятельности"). В результате объединения, исходная строка по продажам будет продублирована столько раз, сколько свойств найдено для данного контрагента.
При агрегации данных (например, суммировании СтоимостьОборот или КоличествоОборот в СКД) каждая продублированная строка будет участвовать в суммировании, что и приводит к завышению итоговых значений. Если у контрагента 2 свойства, сумма задвоится; если 4 свойства — учетверится, как и было замечено в исходном сообщении.
Для того чтобы корректно посчитать ресурсы, нам нужно убедиться, что каждая запись по продажам участвует в суммировании только один раз, или что суммирование происходит только для определенных условий. Наиболее эффективный способ решения этой проблемы в СКД — это использование условных выражений (ВЫБОР КОГДА ТОГДА) непосредственно в выражениях для ресурсов.
Давайте пошагово разберем, как это реализовать:
Определите, какие свойства вам нужны в отчете для раздельного учета. В нашем примере это "Доставка" и "Вид деятельности".
Для каждого такого свойства создайте отдельный ресурс или модифицируйте существующие. Вместо прямого суммирования поля оборотов (например, ПродажиОбороты.КоличествоОборот), мы будем использовать условное выражение.
Используйте конструкцию ВЫБОР КОГДА ТОГДА внутри агрегатной функции СУММА(). Это позволит нам суммировать значение ресурса только тогда, когда условие по свойству истинно. Если условие ложно, мы будем добавлять 0, чтобы избежать влияния на итоговую сумму.
Предположим, мы хотим получить количество только для тех строк, где свойство контрагента связано с "Доставкой". Мы можем создать новый ресурс, например, "Количество по доставке", и задать для него следующее выражение:
СУММА(ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка ТОГДА ПродажиОбороты.КоличествоОборот ИНАЧЕ 0 КОНЕЦ)
Разберем этот код:
СУММА(...): Это агрегатная функция, которая суммирует значения, переданные ей.ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка: Это условие проверяет, является ли текущее свойство из временной таблицы ВТ_Свойства тем свойством, которое мы ищем (в данном случае, "Доставка"). Обратите внимание на использование параметра &СвойствоДоставка. Мы можем передать сюда ссылку на элемент плана видов характеристик "Свойства объектов", соответствующий "Доставке".ТОГДА ПродажиОбороты.КоличествоОборот: Если условие истинно (свойство равно "Доставка"), то мы берем значение КоличествоОборот из текущей строки.ИНАЧЕ 0 КОНЕЦ: Если условие ложно (свойство не равно "Доставка"), мы вместо значения количества подставляем 0. Это критически важно, так как 0 не повлияет на общую сумму, а строка при этом не будет исключена из рассмотрения другими ресурсами.Аналогичным образом мы можем модифицировать выражения для других ресурсов, таких как "Стоимость" и "НДС".
Применяя этот подход к основным ресурсам из вашего запроса (Стоимость, Количество, НДС), часть запроса, отвечающая за выбор ресурсов, будет выглядеть так:
ВЫБРАТЬ
ПродажиОбороты.Контрагент,
ПродажиОбороты.Номенклатура,
СУММА(ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка ТОГДА ПродажиОбороты.СтоимостьОборот ИНАЧЕ 0 КОНЕЦ) КАК СтоимостьПоДоставке,
СУММА(ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка ТОГДА ПродажиОбороты.КоличествоОборот ИНАЧЕ 0 КОНЕЦ) КАК КоличествоПоДоставке,
СУММА(ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка ТОГДА ПродажиОбороты.НДСОборот ИНАЧЕ 0 КОНЕЦ) КАК НДСПоДоставке,
ПродажиОбороты.Организация,
ПродажиОбороты.Регистратор,
// ... остальные поля
ИЗ
РегистрНакопления.Продажи.Обороты(, , Регистратор, ) КАК ПродажиОбороты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Свойства КАК ВТ_Свойства
ПО ПродажиОбороты.Контрагент = ВТ_Свойства.Объект
ГДЕ
// Здесь могут быть ваши отборы
СГРУППИРОВАТЬ ПО
ПродажиОбороты.Контрагент,
ПродажиОбороты.Номенклатура,
// ... другие поля группировки
Важно: При использовании агрегатных функций (СУММА) в основном запросе СКД, необходимо также добавить все неагрегированные поля в секцию СГРУППИРОВАТЬ ПО. Если вы формируете ресурсы в схеме СКД (на вкладке "Ресурсы"), то агрегатные функции добавлять в запрос не нужно, их укажет сама СКД. В схеме СКД вы просто укажете выражение для ресурса:
ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка ТОГДА ПродажиОбороты.СтоимостьОборот ИНАЧЕ 0 КОНЕЦ
СУММА(), если ресурс настроен на суммирование.
Для каждого типа свойства (например, "Доставка" и "Вид деятельности") вы можете создать отдельный ресурс с таким условным выражением. Например:
СУММА(ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоДоставка ТОГДА ПродажиОбороты.КоличествоОборот ИНАЧЕ 0 КОНЕЦ)СУММА(ВЫБОР КОГДА ВТ_Свойства.Свойство = &СвойствоВидДеятельности ТОГДА ПродажиОбороты.КоличествоОборот ИНАЧЕ 0 КОНЕЦ)Таким образом, мы получим отдельные, корректные суммы для каждого интересующего нас свойства, избегая задвоения.
Настройка параметров СКД: Параметры &СвойствоДоставка и &СвойствоВидДеятельности должны быть определены в схеме компоновки данных на вкладке "Параметры" и затем заполнены (например, через выбор значения из справочника "ПланВидовХарактеристик.СвойстваОбъектов") при формировании отчета.
Использование временных таблиц для предварительной агрегации: В более сложных случаях, когда множество соединений или большое количество свойств приводит к неэффективному выполнению запроса, можно сначала агрегировать данные в одной временной таблице, а затем соединять ее с другими. Это может быть полезно для оптимизации.
Разделение наборов данных: Если логика получения свойств сильно отличается или требуется несколько независимых агрегаций, можно создать несколько наборов данных в СКД и связать их через "Связи наборов данных". Это позволяет более гибко управлять структурой отчета.
Свойство "Рассчитывать по..." для ресурсов: В СКД для каждого ресурса есть свойство "Рассчитывать по...". Это позволяет указать, для каких группировок должен рассчитываться ресурс. Например, вы можете выводить общую сумму продаж только для группировки "Контрагент", а не для каждой его характеристики. Однако это не решает проблему задвоения на уровне исходного набора данных, а лишь влияет на отображение итогов.
Проблема задвоения ресурсов в СКД-отчетах при использовании левого соединения с таблицей, содержащей множественные свойства для одного объекта, является распространенной. Мы выяснили, что основная причина кроется в дублировании строк базового набора данных. Наиболее эффективным и гибким решением является применение условных выражений (ВЫБОР КОГДА ТОГДА) внутри агрегатных функций для каждого ресурса. Этот подход позволяет нам точно контролировать, какие значения должны быть включены в сумму, и для каких условий, тем самым обеспечивая корректность итоговых данных в отчете.
Всегда помните, что глубокое понимание структуры вашего запроса и принципов работы СКД является ключом к созданию эффективных и точных отчетов.