Почему в кросс-таблице СКД неправильно считаются или задваиваются суммы и количество при объединении запросов?

← На главную

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

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

Выясняем причину: почему СКД «размножает» данные в таблице?

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


ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(Продажи.Количество) КАК Количество,
    СУММА(Продажи.Стоимость) КАК Сумма,
    Продажи.Период КАК Период,
    Продажи.Регистратор КАК Регистратор,
    NULL КАК Ссылка,
    NULL КАК КоличествоПоступления,
    NULL КАК СуммаПоступления,
    NULL КАК ЦенаП2
ИЗ
    РегистрНакопления.Продажи КАК Продажи
ГДЕ
    Продажи.Period МЕЖДУ &Начало И &Конец
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура,
    Продажи.Период,
    Продажи.Регистратор

ОБЪЕДИНИТЬ ВСЕ

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

Когда этот запрос выполняется, СУБД возвращает плоскую таблицу, в которой строки продаж содержат пустые значения (NULL) в полях поступлений, и наоборот. На уровне линейного списка СКД просто выводит эти строки одну за другой. Проблемы начинаются, когда мы настраиваем структуру отчета в виде таблицы, где строками является Номенклатура, а колонками — Период (например, по месяцам).

При построении кросс-таблицы СКД выполняет следующие действия:

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

Если в схеме компоновки не настроены роли полей или используются некорректные выражения ресурсов, СКД не может правильно сопоставить строки, содержащие NULL в ключевых полях связей. Механизм компоновки начинает дублировать детальные записи для смежных периодов, из-за чего показатели «умножаются» пропорционально количеству документов в группировке. Например, если в январе было три документа поступления с одинаковой ценой 42.60, то при обычном суммировании вместо точной цены 42.60 система выдаст результат 127.80.

Проанализируем детально методы решения этой проблемы.

Способ 1. Группировка во временных таблицах (Рекомендуемый подход)

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

Перепишем исходный запрос с использованием временных таблиц:


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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Продажи.Номенклатура КАК Номенклатура,
    ВТ_Продажи.Период КАК Период,
    ВТ_Продажи.Регистратор КАК Регистратор,
    ВТ_Продажи.Количество КАК Количество,
    ВТ_Продажи.Сумма КАК Сумма,
    NULL КАК Ссылка,
    NULL КАК КоличествоПоступления,
    NULL КАК СуммаПоступления,
    NULL КАК ЦенаП2
ИЗ
    ВТ_Продажи КАК ВТ_Продажи

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВТ_Поступления.Номенклатура,
    ВТ_Поступления.Период,
    NULL,
    NULL,
    NULL,
    ВТ_Поступления.Регистратор,
    ВТ_Поступления.КоличествоПоступления,
    ВТ_Поступления.СуммаПоступления,
    ВТ_Поступления.ЦенаП2
ИЗ
    ВТ_Поступления КАК ВТ_Поступления

Благодаря этому подходу мы передаем в схему СКД строго структурированные и уже сгруппированные наборы данных. Это исключает дублирование строк СУБД на этапе их первоначального извлечения.

Способ 2. Правильная настройка ролей полей в СКД

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

Выполним по шагам следующую настройку:

Перейдем на вкладку Наборы данных в схеме компоновки данных.
Найдем поле Период. В колонке Роль нажмем на кнопку изменения параметров роли.
Установим флаг Период и обязательно укажем его дополнительный тип (например, ПериодСекунда или ПериодДень в зависимости от точности ваших данных). Это подскажет СКД, как правильно группировать и выравнивать периоды по горизонтали таблицы.
Для поля Номенклатура убедимся, что установлена роль Измерение.
В параметрах роли для полей связей (таких как Регистратор или Ссылка) установим свойство Игнорировать NULL. Это предотвратит создание пустых паразитных колонок и строк на пересечении измерений в кросс-таблице.

Способ 3. Использование выражений для неаддитивных ресурсов (Цена)

Такой показатель, как цена номенклатуры, является неаддитивным ресурсом. Его нельзя складывать напрямую. Если мы оставим для поля ЦенаП2 стандартный ресурс Сумма(ЦенаП2), система неизбежно сложит цены всех документов за период.

Рассмотрим варианты правильной настройки ресурса на вкладке Ресурсы:

Вариант А. Вывод точной максимальной или средней цены.
Вместо формулы Сумма(ЦенаП2) укажем:

Максимум(ЦенаП2)

Или:

Среднее(ЦенаП2)

Вариант Б. Использование функции ВычислитьВыражениеСГруппировкойМассив.
Если нам необходимо получить корректное среднее или максимальное значение цены на уровне группировки по номенклатуре, агрегируя детальные документы, применим специальную функцию СКД. В поле выражения ресурса запишем:


Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(ЦенаП2)", "Номенклатура, Регистратор"))

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

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


Выбор Когда Сумма(КоличествоПоступления) = 0 Тогда 0 Иначе Сумма(СуммаПоступления) / Сумма(КоличествоПоступления) Конец

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

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

Еще один профессиональный метод избавиться от дублирования строк — разделить один сложный запрос с ОБЪЕДИНИТЬ ВСЕ на два независимых набора данных внутри схемы компоновки:

НаборДанныхПродажи (запрос только к продажам)
НаборДанныхПоступления (запрос только к поступлениям)

Свяжем их на вкладке Связи наборов данных:

Укажем в качестве источника связи НаборДанныхПродажи, а в качестве приемника — НаборДанныхПоступления.
В качестве условия связи выберем поля Номенклатура и Период.

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

Важное замечание: Чтобы при связи наборов данных не пропали товары, по которым были только поступления, но не было продаж (или наоборот), рекомендуется использовать третий связующий набор данных — например, простой запрос-справочник по всей Номенклатуре, к которому левым соединением будут подключаться оба рабочих набора данных.