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