При построении аналитических отчетов в Системе компоновки данных (СКД) 1С программисты часто сталкиваются с необходимостью вывода сложных промежуточных итогов. Типичный пример — кросс-таблица, в которой по горизонтали выводятся дни, сгруппированные по неделям, а на пересечении строк и колонок необходимо показать детальные дневные суммы и промежуточный итог за каждую неделю. При этом промежуточный итог должен рассчитываться как среднее значение за неделю, исключая дни с нулевыми показателями.
В этой статье мы подробно и пошагово разберем, как решить эту задачу стандартными средствами СКД без создания громоздких временных таблиц в запросе. Мы научимся управлять расчетом ресурсов на разных уровнях группировок, использовать особенности логики NULL для корректного вычисления среднего значения и правильно настраивать структуру колонок для красивого визуального отображения результатов.
Представим, что у нас есть ежедневные данные по количеству продаж некоторого товара:
Нам требуется вывести эти данные в виде кросс-таблицы, где колонками выступают дни, сгруппированные по неделям, а в конце каждой недели выводится колонка «Среднее за неделю». Главная сложность заключается в правильном расчете среднего. Для второй недели (с 11.11 по 15.11) у нас есть 5 рабочих дней, но в один из них (14.11) количество равно 0. Нам нужно разделить общую сумму продаж за неделю (450) не на 5 дней, а на 4, так как день с нулевыми продажами должен быть проигнорирован. Итоговое среднее должно составить 112.5 вместо 90.
Давайте разберем, как реализовать такую логику, решив три подзадачи: исключение нулей, разделение формул расчета по группировкам и хронологическое отображение колонок.
Стандартная агрегатная функция СУБД и СКД СРЕДНЕЕ() (или AVG на уровне SQL) работает по математическим правилам: она суммирует все значения в выборке и делит полученную сумму на количество записей. Значение 0 (ноль) воспринимается системой как полноценное число. Из-за этого знаменатель увеличивается, а итоговый результат занижается.
Однако агрегатные функции математически полностью игнорируют значения типа NULL. Они не суммируются в числителе и не увеличивают счетчик количества записей в знаменателе. Мы можем использовать этот «обман» системы в своих интересах. Нам достаточно подменить все нулевые показатели на значение NULL на этапе расчета ресурса.
Реализовать это можно двумя способами:
ВЫБОР
КОГДА Продажи.Количество = 0 ТОГДА NULL
ИНАЧЕ Продажи.Количество
КОНЕЦ КАК КоличествоДляСреднего
КоличествоБезНулей) на вкладке «Вычисляемые поля» со следующим выражением:
Выбор Когда Количество = 0 Тогда NULL Иначе Количество Конец
При таком подходе в детальных записях нулевые продажи превратятся в пустоту, и агрегатная функция расчета среднего корректно их проигнорирует.
Теперь проанализируем, как заставить СКД считать сумму в детальных записях по дням, но вычислять среднее значение в итогах по неделям. Для этого мы воспользуемся возможностью СКД назначать разные выражения для одного и того же ресурса в зависимости от уровня группировки.
Перейдем на вкладку «Ресурсы» в конструкторе схемы компоновки данных и добавим наше поле дважды с разными настройками:
КоличествоСумма(Количество)ПериодДень (или ту группировку, которая отвечает за вывод конкретного дня).КоличествоСреднее(Выбор Когда Количество = 0 Тогда NULL Иначе Количество Конец)ПериодНеделя.Благодаря этому СКД при выводе колонок-дней будет применять первую формулу (простое суммирование), а при расчете промежуточного итога за неделю автоматически переключится на вторую формулу, вычисляющую среднее значение с исключением нулей.
Выясним, как правильно расположить колонки в отчете, чтобы промежуточное среднее выводилось строго после дней соответствующей недели. Для этого нам необходимо настроить иерархическую структуру в настройках варианта отчета.
Перейдем на вкладку «Настройки» в СКД и выполним следующие действия по шагам:
Товар или Организация).ПериодНеделя.ПериодДень.По умолчанию СКД выводит итоги группировок либо в начале, либо руководствуется системными настройками. Чтобы колонка со средним значением за неделю выводилась строго справа от списка дней этой недели, выделим группировку ПериодНеделя в структуре колонок, перейдем в окно «Другие настройки» (в нижней части экрана) и для параметра «Расположение итогов» установим значение «Конец».
Теперь визуальная структура колонок примет правильный вид:
[Неделя 1] [День 1.1] [День 1.2] ... [Итог (Среднее) за Неделю 1] -> [Неделя 2] [День 2.1] ... [Итог (Среднее) за Неделю 2]
Иногда перед разработчиком встает более сложная задача: что выводить в Общих итогах в самом конце таблицы? Если мы хотим вывести там не просто среднее по всем дням периода, а, например, сумму еженедельных средних значений, стандартное агрегатное выражение не сработает напрямую. СКД запрещает вкладывать агрегатные функции друг в друга (конструкция вида Сумма(Среднее(...)) вызовет ошибку компоновки).
В этом случае на помощь приходит мощная системная функция СКД — ВычислитьВыражениеСГруппировкойМассив(). Она позволяет сначала рассчитать показатели на уровне детальной группировки, собрать их в массив, а затем применить к этому массиву внешнюю агрегатную функцию.
Давайте посмотрим на пример выражения ресурса для расчета Общих итогов по нашей колонке:
Сумма(ВычислитьВыражениеСГруппировкойМассив("Среднее(Выбор Когда Количество = 0 Тогда NULL Иначе Количество Конец)", "ПериодНеделя"))
Как работает это выражение? Система сначала обходит каждую неделю в периоде отчета, рассчитывает для нее среднее значение продаж (игнорируя нули), помещает эти средние значения в массив, а затем функция Сумма складывает элементы этого массива для вывода в финальной колонке общих итогов.
Рассмотрим важный нюанс: что произойдет, если в какой-то день недели продаж не было вообще и записи по этому дню отсутствуют в физической таблице базы данных? Например, в субботу и воскресенье магазин был закрыт, и транзакций нет.
Если в базе данных физически нет строки за этот день, СУБД не вернет его даже со значением 0. Для расчета среднего за неделю СКД увидит, например, только 5 рабочих дней. Если бизнес-логика требует делить сумму продаж на все календарные дни недели (то есть делить строго на 7), отсутствие записей исказит среднее значение.
Для решения этой проблемы мы рекомендуем использовать механизм дополнения периодов в СКД:
ПериодДень.&НачалоПериода и &КонецПериода).В результате СКД принудительно сгенерирует колонки для всех недостающих дней недели в кросс-таблице. Так как реальных данных по этим дням в базе нет, они придут со значением NULL, что позволит сохранить математическую точность расчета среднего значения по выбранной вами методике.