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

← На главную

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

Давайте вместе разберем эту задачу на реальном примере. У нас есть данные по выдаче товаров с двух складов: «Основной Ростов» и «Основной Шахты». Нам нужно вывести номенклатуру, сравнить количество, себестоимость, цену, прибыль и вычислить расхождения. Проанализируем несколько путей решения: от того, к которому пришел автор первоначального кода, до современных, архитектурно правильных подходов.

Вариант 1: Рабочий, но тяжеловесный способ — Соединение вложенных запросов

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

Рассмотрим код, который решает задачу "в лоб". Здесь формируются два огромных вложенных запроса (один по первому складу, другой по второму), после чего они соединяются через ПОЛНОЕ СОЕДИНЕНИЕ по номенклатуре товара.


Макет = ЭтотОбъект.ПолучитьМакет("Макет");
Запрос = Новый Запрос("ВЫБРАТЬ
|    ИтоговаяВыдача.ЗакупкаРостов КАК ЗакупкаРостов,
|    ИтоговаяВыдача.ЦенаРостов КАК ЦенаРостов,
|    ИтоговаяВыдача.ПрибыльРостов КАК ПрибыльРостов,
|    ИтоговаяВыдача.ЗакупкаШахты КАК ЗакупкаШахты,
|    ИтоговаяВыдача.ЦенаШахты КАК ЦенаШахты,
|    ИтоговаяВыдача.ПрибыльШахты КАК ПрибыльШахты,
|    ИтоговаяВыдача.Отклонение КАК Отклонение,
|    ИтоговаяВыдача.ПроцентОтклонения КАК ПроцентОтклонения,
|    Товары.Ссылка КАК Товар,
|    ИтоговаяВыдача.КоличествоШахты КАК КоличествоШахты,
|    ИтоговаяВыдача.КоличествоРостов КАК КоличествоРостов
|ИЗ
|    (ВЫБРАТЬ
|        ВыдачаРостов.ЗакупкаРостов КАК ЗакупкаРостов,
|        ВыдачаРостов.ЦенаРостов КАК ЦенаРостов,
|        ВыдачаРостов.ПрибыльРостов КАК ПрибыльРостов,
|        ВыдачаШахты.ЗакупкаШахты КАК ЗакупкаШахты,
|        ВыдачаШахты.ЦенаШахты КАК ЦенаШахты,
|        ВыдачаШахты.ПрибыльШахты КАК ПрибыльШахты,
|        ВЫБОР
|            КОГДА ВыдачаШахты.ТоварШахты ЕСТЬ NULL
|                ТОГДА ВыдачаРостов.ТоварРостов
|            ИНАЧЕ ВыдачаШахты.ТоварШахты
|        КОНЕЦ КАК Товар,
|        ВыдачаШахты.ПрибыльШахты - ВыдачаРостов.ПрибыльРостов КАК Отклонение,
|        (ВыдачаШахты.ПрибыльШахты - ВыдачаРостов.ПрибыльРостов) / ВыдачаРостов.ПрибыльРостов * 100 КАК ПроцентОтклонения,
|        ВыдачаШахты.КоличествоШахты КАК КоличествоШахты,
|        ВыдачаРостов.КоличествоРостов КАК КоличествоРостов
|    ИЗ
|        (ВЫБРАТЬ
|            ВыдачаТовараТабличнаяЧасть1.Товар КАК ТоварШахты,
|            СУММА(ВыдачаТовараТабличнаяЧасть1.Количество) КАК КоличествоШахты,
|            СРЕДНЕЕ(ВыдачаТовараТабличнаяЧасть1.Закупка) КАК ЗакупкаШахты,
|            СРЕДНЕЕ(ВыдачаТовараТабличнаяЧасть1.Цена) КАК ЦенаШахты,
|            СУММА(ВыдачаТовараТабличнаяЧасть1.Цена * ВыдачаТовараТабличнаяЧасть1.Количество - ВыдачаТовараТабличнаяЧасть1.Закупка * ВыдачаТовараТабличнаяЧасть1.Количество) КАК ПрибыльШахты
|        ИЗ
|            Документ.ВыдачаТовара.ТабличнаяЧасть1 КАК ВыдачаТовараТабличнаяЧасть1
|        ГДЕ
|            ВыдачаТовараТабличнаяЧасть1.Ссылка.Дата МЕЖДУ &НачПериода И &КонПериода
|            И ВыдачаТовараТабличнаяЧасть1.Ссылка.Проведен = ИСТИНА
|            И ВыдачаТовараТабличнаяЧасть1.Ссылка.Склад.Код = ""00001""
|        СГРУППИРОВАТЬ ПО
|            ВыдачаТовараТабличнаяЧасть1.Товар) КАК ВыдачаШахты
|        ПОЛНОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
|            ВыдачаТовараТабличнаяЧасть1.Товар КАК ТоварРостов,
|            СУММА(ВыдачаТовараТабличнаяЧасть1.Количество) КАК КоличествоРостов,
|            СРЕДНЕЕ(ВыдачаТовараТабличнаяЧасть1.Закупка) КАК ЗакупкаРостов,
|            СРЕДНЕЕ(ВыдачаТовараТабличнаяЧасть1.Цена) КАК ЦенаРостов,
|            СУММА(ВыдачаТовараТабличнаяЧасть1.Цена * ВыдачаТовараТабличнаяЧасть1.Количество - ВыдачаТовараТабличнаяЧасть1.Закупка * ВыдачаТовараТабличнаяЧасть1.Количество) КАК ПрибыльРостов
|        ИЗ
|            Документ.ВыдачаТовара.ТабличнаяЧасть1 КАК ВыдачаТовараТабличнаяЧасть1
|        ГДЕ
|            ВыдачаТовараТабличнаяЧасть1.Ссылка.Дата МЕЖДУ &НачПериода И &КонПериода
|            И ВыдачаТовараТабличнаяЧасть1.Ссылка.Проведен = ИСТИНА
|            И ВыдачаТовараТабличнаяЧасть1.Ссылка.Склад.Код = ""00008""
|        СГРУППИРОВАТЬ ПО
|            ВыдачаТовараТабличнаяЧасть1.Товар) КАК ВыдачаРостов
|        ПО ВыдачаШахты.ТоварШахты = ВыдачаРостов.ТоварРостов) КАК ИтоговаяВыдача
|    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Товары КАК Товары
|    ПО ИтоговаяВыдача.Товар = Товары.Ссылка
|ИТОГИ
|    СУММА(ПрибыльРостов),
|    СУММА(ПрибыльШахты)
|ПО
|    Товар ИЕРАРХИЯ
|АВТОУПОРЯДОЧИВАНИЕ");

Какие проблемы есть в данном подходе?

Математика с типами NULL. Обратите внимание на строку ВыдачаШахты.ПрибыльШахты - ВыдачаРостов.ПрибыльРостов. При полном соединении, если товар продавался только в Ростове, но не в Шахтах, поле ПрибыльШахты вернет значение NULL. Любая математическая операция с NULL в 1С возвращает NULL. Из-за этого расчет отклонений просто не сработает для уникальных строк каждого склада. Эту проблему обязательно нужно решать с помощью функции ЕСТЬNULL(Поле, 0).
Производительность на больших базах. Платформа 1С:Предприятие транслирует этот код в запрос к СУБД (например, MS SQL или PostgreSQL). Оптимизаторы СУБД очень плохо справляются с полным соединением тяжелых вложенных запросов. План запроса строится неэффективно, что может привести к зависанию отчета на долгие минуты.
Жесткое кодирование условий (Хардкод). Использование конструкций вида Склад.Код = "00008" или Склад.Наименование ПОДОБНО "Основной Ростов" категорически не рекомендуется. Коды и наименования могут быть изменены пользователями. Такие вещи всегда нужно передавать через параметры запроса.

Вариант 2: Использование Временных Таблиц (ВТ)

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

Посмотрим, как выглядит структура такого решения:


Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;

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

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

// Шаг 3: Соединяем временные таблицы и вычисляем разницу
Запрос.Текст = 
"ВЫБРАТЬ
|    ЕСТЬNULL(ВТ_Шахты.Товар, ВТ_Ростов.Товар) КАК Товар,
|    ЕСТЬNULL(ВТ_Шахты.КоличествоШахты, 0) КАК КоличествоШахты,
|    ЕСТЬNULL(ВТ_Ростов.КоличествоРостов, 0) КАК КоличествоРостов,
|    ЕСТЬNULL(ВТ_Шахты.СуммаШахты, 0) КАК СуммаШахты,
|    ЕСТЬNULL(ВТ_Ростов.СуммаРостов, 0) КАК СуммаРостов,
|    ЕСТЬNULL(ВТ_Шахты.КоличествоШахты, 0) - ЕСТЬNULL(ВТ_Ростов.КоличествоРостов, 0) КАК ОтклонениеКоличество
|ИЗ
|    ВТ_Шахты КАК ВТ_Шахты
|        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Ростов КАК ВТ_Ростов
|        ПО ВТ_Шахты.Товар = ВТ_Ростов.Товар";

РезультатЗапроса = Запрос.Выполнить();

Здесь мы избавились от "хардкода", передавая конкретные ссылки на элементы справочника через параметры &СкладШахты и &СкладРостов. Также мы внедрили функцию ЕСТЬNULL, которая корректно обрабатывает пустые значения, заменяя их на ноль перед выполнением математических операций. СУБД выполнит такой код мгновенно, так как таблицы легко индексируются.

Вариант 3: Оптимальное решение — Условная агрегация в одном запросе

Несмотря на то, что использование временных таблиц является отличной практикой, в нашем конкретном случае данные берутся из одной и той же физической таблицы (табличной части документов). Разделять чтение одной базы на два прохода, а потом склеивать их обратно — избыточно.

Рассмотрим самый элегантный и производительный способ решения подобных задач — условную агрегацию. Суть подхода заключается в том, что мы читаем таблицу всего один раз, фильтруем ее сразу по двум складам, а распределение значений по нужным колонкам производим "на лету" с помощью конструкции ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ ... КОНЕЦ внутри агрегатных функций.

Посмотрим на пример такого запроса:


Запрос = Новый Запрос;
Запрос.Текст = 
"ВЫБРАТЬ
|    Выдача.Товар КАК Товар,
|    
|    // Показатели для Шахт
|    СУММА(ВЫБОР КОГДА Выдача.Ссылка.Склад = &СкладШахты ТОГДА Выдача.Количество ИНАЧЕ 0 КОНЕЦ) КАК КоличествоШахты,
|    СУММА(ВЫБОР КОГДА Выдача.Ссылка.Склад = &СкладШахты ТОГДА (Выдача.Цена - Выдача.Закупка) * Выдача.Количество ИНАЧЕ 0 КОНЕЦ) КАК ПрибыльШахты,
|    
|    // Показатели для Ростова
|    СУММА(ВЫБОР КОГДА Выдача.Ссылка.Склад = &СкладРостов ТОГДА Выдача.Количество ИНАЧЕ 0 КОНЕЦ) КАК КоличествоРостов,
|    СУММА(ВЫБОР КОГДА Выдача.Ссылка.Склад = &СкладРостов ТОГДА (Выдача.Цена - Выдача.Закупка) * Выдача.Количество ИНАЧЕ 0 КОНЕЦ) КАК ПрибыльРостов
|ПОМЕСТИТЬ ВТ_Агрегация
|ИЗ
|    Документ.ВыдачаТовара.ТабличнаяЧасть1 КАК Выдача
|ГДЕ
|    Выдача.Ссылка.Дата МЕЖДУ &НачПериода И &КонПериода
|    И Выдача.Ссылка.Проведен = ИСТИНА
|    // Берем данные сразу по двум складам
|    И (Выдача.Ссылка.Склад = &СкладШахты ИЛИ Выдача.Ссылка.Склад = &СкладРостов) 
|СГРУППИРОВАТЬ ПО
|    Выдача.Товар
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
|    ВТ_Агрегация.Товар КАК Товар,
|    ВТ_Агрегация.КоличествоШахты КАК КоличествоШахты,
|    ВТ_Агрегация.ПрибыльШахты КАК ПрибыльШахты,
|    ВТ_Агрегация.КоличествоРостов КАК КоличествоРостов,
|    ВТ_Агрегация.ПрибыльРостов КАК ПрибыльРостов,
|    
|    // Вычисляем отклонения просто отнимая готовые колонки
|    ВТ_Агрегация.ПрибыльШахты - ВТ_Агрегация.ПрибыльРостов КАК ОтклонениеСумма,
|    ВЫБОР 
|        КОГДА ВТ_Агрегация.ПрибыльРостов = 0 ТОГДА 0 
|        ИНАЧЕ (ВТ_Агрегация.ПрибыльШахты - ВТ_Агрегация.ПрибыльРостов) / ВТ_Агрегация.ПрибыльРостов * 100 
|    КОНЕЦ КАК ОтклонениеПроцент
|ИЗ
|    ВТ_Агрегация КАК ВТ_Агрегация";

Запрос.УстановитьПараметр("НачПериода", НачПериода);
Запрос.УстановитьПараметр("КонПериода", КонецДня(КонПериода));
Запрос.УстановитьПараметр("СкладШахты", СкладШахты); // Ссылка на склад
Запрос.УстановитьПараметр("СкладРостов", СкладРостов); // Ссылка на склад

В чем преимущества этого метода?

Колоссальная скорость работы. База данных сканируется только один раз.
Нет проблем с NULL значениями, так как конструкция ИНАЧЕ 0 гарантирует возвращение числового типа.
Код становится в несколько раз короче и гораздо понятнее для поддержки в будущем.
Безопасность вычислений: мы предусмотрели проверку деления на ноль при расчете процентов (КОГДА ВТ_Агрегация.ПрибыльРостов = 0 ТОГДА 0). В платформе 1С попытка разделить число на ноль вызовет фатальную ошибку и отчет не сформируется.

Шаг в сторону современных технологий: Использование СКД

В приведенных примерах для визуализации результатов применялся ручной вывод областей в табличный документ через конструкции вроде Макет.ПолучитьОбласть("Детали"), циклы Пока ВыборкаДетальныеЗаписи.Следующий() Цикл и ручной расчет группировок через НачатьАвтогруппировкуСтрок().

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

Разберем, почему СКД значительно упростит вам жизнь при создании подобного отчета:

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

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