При построении аналитических отчетов часто возникает задача вывода данных не просто за календарный месяц, а в формате «скользящего окна». Например, когда в колонке за март 2024 года нужно увидеть сумму оборотов за период с апреля 2023 по март 2024 включительно. Ситуация усложняется, если в одном отчете нужно выводить обычные обороты для одних статей и «годовые» (скользящие) для других.
В этой статье мы подробно разберем несколько подходов к решению этой задачи, проанализируем примеры кода и выясним, как оптимизировать такие запросы для работы с большими объемами данных.
Этот метод основан на создании таблицы всех месяцев, входящих в выбранный пользователем период, и последующем соединении этой таблицы с реальными данными регистра по условию «МЕЖДУ». Разберем этот алгоритм по шагам.
Сначала нам необходимо получить список всех месяцев. Для этого удобно использовать производственный календарь или системную таблицу периодов. Рассмотрим пример запроса, который формирует такую таблицу и соединяет её с оборотами:
ВЫБРАТЬ РАЗЛИЧНЫЕ
НАЧАЛОПЕРИОДА(РПК.ДатаКалендаря, МЕСЯЦ) КАК Месяц
ПОМЕСТИТЬ Месяцы
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РПК
ГДЕ
РПК.ДатаКалендаря МЕЖДУ &НачалоПериода И &КонецПериода;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Месяцы.Месяц КАК ПериодОтчета,
Статьи.Ссылка КАК Статья,
СУММА(Рег.СуммаОборот) КАК СуммаЗаГод
ИЗ
Месяцы КАК Месяцы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ПрочиеРасходы.Обороты(
ДОБАВИТЬКДАТЕ(&НачалоПериода, МЕСЯЦ, -11),
&КонецПериода,
Авто,
) КАК Рег
ПО (Рег.Период МЕЖДУ ДОБАВИТЬКДАТЕ(Месяцы.Месяц, МЕСЯЦ, -11) И Месяцы.Месяц)
СГРУППИРОВАТЬ ПО
Месяцы.Месяц,
Статьи.Ссылка
Важные нюансы этого решения:
ПО (Рег.Период МЕЖДУ ДОБАВИТЬКДАТЕ(Месяцы.Месяц, МЕСЯЦ, -11) И Месяцы.Месяц). Это заставляет СУБД для каждой записи «эталонного» месяца найти все записи из регистра, которые попадают в 12-месячный интервал «назад».МЕЖДУ в соединении может быть тяжелым для СУБД при очень большом количестве записей в регистре, так как это исключает использование некоторых видов оптимизации индексов (Hash Join).Если мы не хотим усложнять текст запроса соединениями, можно переложить расчет скользящего итога на систему компоновки данных. Рассмотрим, как это сделать через ресурсы.
В схеме компоновки данных мы можем оставить простой запрос, выбирающий обороты помесячно. А в поле «Ресурсы» прописать специальное выражение. Выясним, как выглядит конструкция функции ВычислитьВыражение для расчета суммы за предыдущие периоды:
Сумма(ВычислитьВыражение("СуммаОборот", "Период", , "Предыдущая(11)", "Текущая"))
Разберем параметры функции:
"СуммаОборот" — имя поля, которое мы суммируем."Период" — группировка, по которой идет расчет (наш месяц)."Предыдущая(11)" — указывает системе взять 11 предыдущих записей группировки."Текущая" — указывает включить текущую запись в расчет.Преимущества: Этот метод работает очень быстро, так как СКД делает один простой запрос к базе, а суммирование происходит уже в памяти при выводе отчета.
Ограничения: Для корректной работы «хвоста» в первом месяце отчета в запросе всё равно должны присутствовать данные за 11 месяцев до начала периода отчета, даже если они не будут выводиться в финальную таблицу.
Проанализируем ситуацию, когда нам нужно обеспечить максимальную производительность. Метод соединения МЕЖДУ иногда уступает методу объединения. Суть заключается в том, чтобы «размножить» каждую запись регистра 12 раз с разными периодами.
Пример структуры такого запроса:
ВЫБРАТЬ
Рег.Период КАК Период,
Рег.СуммаОборот КАК Сумма
ИЗ
РегистрНакопления.Данные.Обороты(&Начало, &Конец, Месяц) КАК Рег
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(Рег.Период, МЕСЯЦ, 1),
Рег.СуммаОборот
ИЗ
РегистрНакопления.Данные.Обороты(&Начало, &Конец, Месяц) КАК Рег
// ... и так далее до +11 месяцев
После этого итоговая таблица группируется по периоду. В итоге оборот за январь попадет в результат для января, февраля, марта и так далее до декабря. Это позволяет избежать сложных условий соединения, заменяя их простым суммированием по индексу.
В СКД можно создать два набора данных. Первый набор — «СписокМесяцев» (простой запрос к календарю). Второй набор — «ДанныеОборотов» (запрос к регистру с параметрами &НачалоПериода и &КонецПериода).
В настройках «Связи наборов данных» мы связываем первый набор со вторым по периоду. Главная хитрость здесь в параметрах связи:
&НачалоПериода для второго набора мы передаем выражение: ДобавитьКДате(МесяцИзПервогоНабора, "Месяц", -11).&КонецПериода передаем сам МесяцИзПервогоНабора.В этом случае СКД для каждой строки из первого набора (для каждого месяца) выполнит отдельный запрос к регистру за нужный интервал. Это очень удобно для разработки, так как логика «сдвига» инкапсулирована в настройках связи.
Вернемся к условию задачи: скользящий итог нужен только для определенных статей. Чтобы отчет выводил корректные данные в одной колонке, мы рекомендуем следующий подход в запросе:
Проанализируем конструкцию ВЫБОР, которую следует использовать в ресурсах СКД или в конечном запросе:
ВЫБРАТЬ
Данные.Статья,
Данные.Период,
ВЫБОР
КОГДА Данные.Статья.ЗаГод ТОГДА Данные.СуммаСкользящая
ИНАЧЕ Данные.СуммаЗаМесяц
КОНЕЦ КАК РезультатСумма
ИЗ
...
Для реализации этого в одном запросе удобно подготовить две колонки: одну с обычным оборотом за месяц, вторую — со скользящим итогом (рассчитанным любым из методов выше). Затем в СКД в поле «Путь к данным» для ресурса прописать логику выбора нужного значения в зависимости от реквизита статьи.
Мы рассмотрели несколько способов реализации сложных периодов. Выбор конкретного метода зависит от ваших приоритетов:
ВычислитьВыражение в СКД.ОБЪЕДИНИТЬ ВСЕ.Не забывайте про обработку пустых значений. Если в каком-то месяце не было оборотов ни по одной статье, этот месяц может исчезнуть из отчета. Чтобы этого избежать, всегда делайте ЛЕВОЕ СОЕДИНЕНИЕ от таблицы периодов (календаря) к данным регистра и используйте настройку «Дополнять периоды» в СКД.