Как в СКД вычислить процент прироста между первым и последним значением в группировке?

← На главную

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

При этом логика расчета на разных уровнях группировки должна отличаться:

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

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

---

Почему не работает стандартный подход «в лоб»?

Часто разработчики пытаются решить задачу на вкладке «Ресурсы», прописав для группировки «Должность» стандартную формулу с использованием функции ВычислитьВыражение:


(ВычислитьВыражение("Сумма(Оклад)", , , "Последняя", "Последняя") - ВычислитьВыражение("Сумма(Оклад)", , , "Первая", "Первая")) 
/ 
ВычислитьВыражение("Сумма(Оклад)", , , "Первая", "Первая") * 100

При выполнении отчета такая формула либо возвращает пустой результат, либо рассчитывает неверные данные. Давайте выясним причину этого поведения.

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

---

Решение №1: Использование двойного агрегирования в СКД

Для того чтобы обойти ограничение контекста группировок, в СКД применяется мощная комбинация функций ВычислитьВыражениеСГруппировкойМассив и ВычислитьВыражение. Этот метод позволяет принудительно вычислить значения на уровне детальных записей (месяцев), собрать их в массив и передать наверх — в родительскую группировку «Должность».

Рассмотрим по шагам, как получить оклад за первый и за последний месяцы на уровне должности.

Шаг 1. Получение значения за первый месяц

Для извлечения оклада за самый первый хронологический месяц внутри текущей должности мы используем следующую формулу:


Максимум(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(Оклад)"", , , ""Первая"", ""Первая"")", "Месяц"))

Проанализируем ситуацию и разберем, как именно работает эта конструкция:

Внутреннее выражение ВычислитьВыражение(""Сумма(Оклад)"", , , ""Первая"", ""Первая"") принудительно выполняется в контексте подчиненной группировки «Месяц». Обратите внимание на использование двойных кавычек — это необходимо, так как выражение передается в качестве строкового параметра во внешнюю функцию.
Функция ВычислитьВыражениеСГруппировкойМассив(..., "Месяц") производит обход всех группировок «Месяц», относящихся к текущей должности, вычисляет для каждой из них внутреннее выражение и собирает результаты в массив. Поскольку внутреннее выражение на каждом шаге ищет именно первую запись, результатом работы функции станет массив, заполненный одинаковыми значениями оклада за первый месяц (например, [50000, 50000, 50000]).
Внешняя агрегатная функция Максимум (также можно использовать Минимум или Среднее) извлекает из этого массива единственное числовое значение первого оклада.

Шаг 2. Получение значения за последний месяц

Аналогичным образом мы получаем значение оклада за последний месяц периода:


Максимум(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(Оклад)"", , , ""Последняя"", ""Последняя"")", "Месяц"))

Эта формула соберет массив значений, соответствующих окладу последнего месяца в группировке (например, [85000, 85000, 85000]), и функция Максимум вернет нам число 85000.

Шаг 3. Итоговая формула ресурса для группировки «Должность»

Теперь мы можем объединить эти две конструкции в одну общую формулу расчета процента прироста. Обязательно предусмотрим защиту от деления на ноль, если оклад за первый месяц вдруг окажется незаполненным или равным нулю:


(
    Максимум(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(Оклад)"", , , ""Последняя"", ""Последняя"")", "Месяц")) 
    - 
    Максимум(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(Оклад)"", , , ""First"", ""First"")", "Месяц"))
) 
/ 
Выбор 
    Когда Максимум(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(Оклад)"", , , ""Первая"", ""Первая"")", "Месяц")) = 0 Тогда 1 
    Иначе Максимум(ВычислитьВыражениеСГруппировкойМассив("ВычислитьВыражение(""Сумма(Оклад)"", , , ""Первая"", ""Первая"")", "Месяц")) 
Конец 
* 100

---

Разделение расчета итогов по уровням группировок

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

Выполним следующую настройку:

Строка 1 (для месяцев):

Путь к данным: Прирост_gross
Рассчитывать по...: Выберите группировку Месяц
Выражение: Здесь мы рассчитываем процент изменения текущего месяца относительно предыдущего:


(Сумма(Оклад) - ВычислитьВыражение("Сумма(Оклад)", , , "Предыдущая", "Предыдущая")) 
/ 
Выбор 
    Когда ВычислитьВыражение("Сумма(Оклад)", , , "Предыдущая", "Предыдущая") = 0 Тогда 1 
    Иначе ВычислитьВыражение("Сумма(Оклад)", , , "Предыдущая", "Предыдущая") 
Конец * 100

Строка 2 (для должностей):
- Путь к данным: Прирост_gross
- Рассчитывать по...: Выберите группировку Должность
- Выражение: Пропишите формулу двойного агрегирования, которую мы составили на предыдущем шаге (с использованием ВычислитьВыражениеСГруппировкойМассив).

Благодаря такому разделению СКД автоматически применит нужную математическую логику на каждом из уровней иерархии отчета.

---

Решение №2: Расчет данных на уровне запроса

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

Рассмотрим, как реализовать это с помощью временных таблиц в языке запросов 1С.

Допустим, у нас есть исходная временная таблица ВТ_Данные, содержащая поля Должность, Период (дата начала месяца) и Оклад. Напишем пакетный запрос для получения показателей:


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

////////////////////////////////////////////////////////////////////////////////
// Шаг 2. Получаем оклады, соответствующие первому и последнему периодам
ВЫБРАТЬ
    Границы.Должность КАК Должность,
    ПервыйОклад.Оклад КАК ОкладНачало,
    ПоследнийОклад.Оклад КАК ОкладКонец
ПОМЕСТИТЬ ВТ_ОкладыГраниц
ИЗ
    ВТ_ГраницыПериодов КАК Границы
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК ПервыйОклад
        ПО Границы.Должность = ПервыйОклад.Должность
        И Границы.ПервыйПериод = ПервыйОклад.Период
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК ПоследнийОклад
        ПО Границы.Должность = ПоследнийОклад.Должность
        И Границы.ПоследнийПериод = ПоследнийОклад.Период
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 3. Соединяем полученные результаты с основным выводом
ВЫБРАТЬ
    Данные.Должность КАК Должность,
    Данные.Период КАК Период,
    Данные.Оклад КАК Оклад,
    ОкладыГраниц.ОкладНачало КАК ПервыйОкладПоДолжности,
    ОкладыГраниц.ОкладКонец КАК ПоследнийОкладПоДолжности
ИЗ
    ВТ_Данные КАК Данные
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОкладыГраниц КАК ОкладыГраниц
        ПО Данные.Должность = ОкладыГраниц.Должность

После того как мы передадим этот запрос в СКД в качестве источника данных, у нас на уровне каждой детальной записи будут доступны поля ПервыйОкладПоДолжности и ПоследнийОкладПоДолжности.

Теперь на вкладке «Ресурсы» для поля Прирост_gross при расчете по группировке Должность достаточно написать очень простую и понятную формулу:


(Максимум(ПоследнийОкладПоДолжности) - Максимум(ПервыйОкладПоДолжности)) 
/ 
Выбор 
    Когда Максимум(ПервыйОкладПоДолжности) = 0 Тогда 1 
    Иначе Максимум(ПервыйОкладПоДолжности) 
Конец * 100

Этот способ является более производительным и прозрачным для отладки, поскольку сложные вычисления перекладываются на СУБД, а в СКД приходят уже подготовленные числовые показатели.

Выбор конкретного метода зависит от архитектуры вашего отчета. Если схема компоновки строится на базе простого запроса к виртуальным таблицам и важна динамическая фильтрация периодов пользователем, отлично подойдет Решение №1. Если же отчет содержит сложные пакетные запросы, целесообразно применить Решение №2.