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