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