Как сравнить показатели продаж и рентабельности за два разных периода в СКД 1С?

← На главную

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

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

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

---

Анализ проблемы: почему стандартное соединение запросов не работает

Рассмотрим ситуацию, с которой столкнулся автор темы. Он создал два идентичных запроса к регистрам накопления Продажи.Обороты и ПродажиСебестоимость: первый за период ДатаНач1-ДатаКон1, второй — за ДатаНач2-ДатаКон2. Затем попытался объединить их с помощью конструкции ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN) по полям Покупатель и Номенклатура.

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

Попытка заменить внутреннее соединение на ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN) решает проблему лишь частично: в отчет попадут все данные за первый период, но новые продажи из второго периода все равно будут утеряны. Использование ПОЛНОЕ СОЕДИНЕНИЕ (FULL JOIN) теоретически решает задачу, но в СУБД при работе с большими объемами данных полные соединения без жестких условий по индексам могут вызывать колоссальные просадки производительности и приводить к взаимоблокировкам.

---

Разбор «костыльного» решения: UNION ALL с заполнением нулями

В итоге обсуждения на форуме автор пришел к решению использовать конструкцию ОБЪЕДИНИТЬ ВСЕ (UNION ALL), искусственно разделяя колонки для первого и второго периодов. Посмотрим на структуру его итогового запроса:


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

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

ВЫБРАТЬ
    0,
    0,
    0,
    0,
    ...
    0,
    ВложенныйЗапрос2.Подразделение,
    ВложенныйЗапрос2.Покупатель,
    ВложенныйЗапрос2.Номенклатура,
    ...
    ЕСТЬNULL(ВложенныйЗапрос2.Количество, 0),
    ЕСТЬNULL(ВложенныйЗапрос2.Стоимость, 0)
ИЗ
    (...) КАК ВложенныйЗапрос2

Проанализируем эту ситуацию. Почему такое решение является плохим тоном и содержит скрытые угрозы?

1. Нарушение строгой типизации (Смешение типов)
Обратите внимание на конструкцию 0 КАК Покупатель1 в первом запросе и соответствующее ей поле ВложенныйЗапрос2.Покупатель во втором запросе. В системе 1С поле покупателя имеет тип ссылки (например, СправочникСсылка.Контрагенты). Присваивая ему числовое значение 0 в одной из частей объединения, мы заставляем СУБД и СКД создавать поле составного типа (Число, СправочникСсылка). Это приводит к следующим неприятным последствиям:

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

Как это исправить в рамках объединения: вместо числового нуля 0 необходимо использовать значение NULL или пустую ссылку соответствующего типа:

ЗНАЧЕНИЕ(Справочник.Контрагенты.ПустаяСсылка) КАК Покупатель1

2. Огромный нечитаемый код
Запрос раздувается до нескольких сотен строк. Поддерживать такой код, вносить изменения в формулы расчета себестоимости или рентабельности становится крайне тяжело. Любая опечатка в порядке следования колонок при ОБЪЕДИНИТЬ ВСЕ приведет к тому, что данные перемешаются.

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

---

Метод №1: Кросс-таблица в настройках СКД (Самый простой и эффективный способ)

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

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

Рассмотрим пошагово реализацию этого метода:

Шаг 1. Пишем лаконичный запрос в схеме компоновки
Нам нужно выбрать продажи, ограничив период условием «Период 1 ИЛИ Период 2». Текст запроса будет выглядеть следующим образом:


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

Шаг 2. Создаем вычисляемое поле в СКД
Переходим на вкладку «Вычисляемые поля» нашей схемы компоновки данных. Добавляем новое поле:

Путь к данным: ПериодДляСравнения
Выражение: Выбор Когда Период Между &ДатаНач1 И &ДатаКон1 Тогда "Период 1" Иначе "Период 2" Конец
Заголовок: Сравниваемый период

Шаг 3. Настраиваем структуру отчета
Переходим на вкладку «Настройки»:

Удаляем стандартную группировку «Детальные записи».
Добавляем новую структуру типа «Таблица».
В Строки нашей таблицы добавляем группировки: Покупатель, а внутрь нее — Номенклатура.
В Колонки таблицы добавляем наше вычисляемое поле: ПериодДляСравнения.
В Выбранные поля отчета (или самой таблицы) перетаскиваем наши ресурсы: Количество, Стоимость, ВаловаяПрибыль.

Результат работы СКД:
Система компоновки данных самостоятельно сгруппирует строки по покупателям и товарам. В колонках отчета автоматически появятся две секции: «Период 1» и «Период 2». При этом СКД выполнит полноценное сведение данных: если товар продавался только в первом периоде, в колонке второго периода по этому товару будут пустые значения (или нули), и наоборот. Никаких пустых ссылок или составных типов данных при этом не образуется.

---

Метод №2: Использование Временных Таблиц и Полного Соединения

Если специфика отчета не позволяет использовать кросс-таблицу (например, если колонки периодов должны быть жестко зашиты в плоскую таблицу с уникальным оформлением для каждой), мы можем использовать полное соединение на уровне SQL-запроса. Но делать это нужно правильно, через временные таблицы, чтобы избежать дублирования записей и просадки производительности.

Разберем этот классический паттерн построения запроса.

Шаг 1. Получаем данные за первый период во временную таблицу


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

Шаг 2. Получаем данные за второй период во временную таблицу


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

Шаг 3. Создаем таблицу уникальной аналитики
Для того чтобы выполнить корректное полное соединение без дублей и пропусков, мы должны сначала собрать все уникальные пары «Покупатель — Номенклатура», которые встречались хотя бы в одном из периодов. Для этого идеально подходит операция ОБЪЕДИНИТЬ (UNION), которая автоматически убирает дубликаты.


ВЫБРАТЬ Покупатель, Номенклатура ПОМЕСТИТЬ ВТ_Аналитика ИЗ ВТ_Период1
ОБЪЕДИНИТЬ
ВЫБРАТЬ Покупатель, Номенклатура ИЗ ВТ_Период2
;

Шаг 4. Финальное объединение данных через ЛЕВОЕ СОЕДИНЕНИЕ
Теперь мы берем нашу полную таблицу уникальных аналитик ВТ_Аналитика и левым соединением подключаем к ней показатели из первого и второго периодов. Это гарантирует 100% сохранение типов данных и высокую скорость работы запроса.


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

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

---

Метод №3: Связи наборов данных в СКД

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

Создаем два независимых набора данных - запроса: НаборДанных1 (для первого периода) и НаборДанных2 (для второго).
Создаем третий набор данных — НаборДанныхАналитика, который выбирает список всех контрагентов и товаров.
Переходим на вкладку «Связи наборов данных».
Настраиваем две связи:
- Источником связи выступает НаборДанныхАналитика, приемником — НаборДанных1. Указываем условия связи по полям Покупатель и Номенклатура.
- Аналогично связываем НаборДанныхАналитика с НаборДанных2.

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

---

Как в СКД рассчитывать отклонение (дельту) показателей эффективности

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

Ни в коем случае не следует рассчитывать проценты отклонений и рентабельности непосредственно в SQL-запросе! Это классическая ошибка новичков. Если рассчитать рентабельность в запросе, то при группировке данных по покупателям (или в общих итогах) СКД просто сложит эти проценты между собой, получив бессмысленные тысячи процентов.

Правильный путь — расчет процентов в **Ресурсах СКД**.

Разберем, как настроить расчет отклонений для Метада №2 (Временные таблицы):

Шаг 1. Определяем ресурсы
На вкладке «Ресурсы» добавляем базовые суммовые показатели с выражением Сумма(...):

Количество1, Стоимость1, Себестоимость1
Количество2, Стоимость2, Себестоимость2

Шаг 2. Добавляем вычисляемые поля для маржинальных показателей
На вкладке «Вычисляемые поля» добавим формулы для Валовой прибыли за оба периода:

Поле ВаловаяПрибыль1, Выражение: Стоимость1 - Себестоимость1
Поле ВаловаяПрибыль2, Выражение: Стоимость2 - Себестоимость2

Обязательно добавляем их в ресурсы с выражением Сумма(ВаловаяПрибыль1) и Сумма(ВаловаяПрибыль2).

Шаг 3. Рассчитываем рентабельность и отклонение в Ресурсах
Для расчета рентабельности и эффективности в процентах пишем формулы прямо на вкладке «Ресурсы» в колонке «Выражение»:

Рентабельность1: Выбор Когда Сумма(Стоимость1) = 0 Тогда 0 Иначе (Сумма(ВаловаяПрибыль1) / Сумма(Стоимость1)) * 100 Конец
Рентабельность2: Выбор Когда Сумма(Стоимость2) = 0 Тогда 0 Иначе (Сумма(ВаловаяПрибыль2) / Сумма(Стоимость2)) * 100 Конец
ОтклонениеПродажРуб (Дельта): Сумма(Стоимость2) - Сумма(Стоимость1)
ОтклонениеРентабельностиПроцент: [Рентабельность2] - [Рентабельность1]

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

---