Как дополнить период в запросе 1С с автозаполнением пропущенных месяцев предыдущими значениями?

← На главную

При построении аналитических отчетов и графиков разработчики часто сталкиваются с проблемой «проседания» показателей. Если в каком-то месяце у подразделения или контрагента не было движений по регистру, на графике образуется провал в ноль. Заказчики обычно хотят видеть непрерывную линию: если в текущем месяце данных нет, должно дублироваться последнее известное значение за прошлые периоды.

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

В чем сложность: почему «ломается» логика при переходе через год

Первоначальная идея автора темы заключалась в генерации простой таблицы чисел от 1 до 12, обозначающих номера месяцев. Затем к этим номерам левым соединением подключались обороты подразделений. Это отлично работает, пока мы находимся в рамках одного календарного года. Но как только отчет формируется, например, с июня 2023 по июнь 2024 года, простая нумерация месяцев теряет смысл. Число «6» начинает дублироваться (июнь 2023 и июнь 2024), а связь по номерам месяцев без учета года приводит к неверному объединению данных.

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

Решение 1. Генерация таблицы месяцев в запросе математическим методом

Рассмотрим метод, к которому пришел автор темы. Если в конфигурации нет производственного календаря или вы не хотите закладываться на сторонние таблицы, сетку периодов можно сгенерировать динамически прямо в запросе. Для этого используется декартово произведение вспомогательной таблицы цифр от 0 до 9 и функция ДОБАВИТЬКДАТЕ.

Проанализируем по шагам, как получить все месяцы в интервале между параметрами &НачалоПериода и &КонецПериода:


// Шаг 1. Создаем базовую таблицу с цифрами от 0 до 9
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ ВсеЦифры
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 5
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 6
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 9;

// Шаг 2. Генерируем непрерывную последовательность месяцев
ВЫБРАТЬ
    ДОБАВИТЬКДАТЕ(&НачалоПериода, МЕСЯЦ, Т3.Цифра * 100 + Т2.Цифра * 10 + Т1.Цифра) КАК ДатаМесяц
ПОМЕСТИТЬ ВсеМесяцыПериода
ИЗ
    ВсеЦифры КАК Т1
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВсеЦифры КАК Т2
        ПО (Т2.Цифра * 10 + Т1.Цифра <= РАЗНОСТЬДАТ(&НачалоПериода, КОНЕЦПЕРИОДА(&КонецПериода, МЕСЯЦ), МЕСЯЦ))
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВсеЦифры КАК Т3
        ПО (Т3.Цифра * 100 + Т2.Цифра * 10 + Т1.Цифра <= РАЗНОСТЬДАТ(&НачалоПериода, КОНЕЦПЕРИОДА(&КонецПериода, МЕСЯЦ), МЕСЯЦ))
;

ВЫБРАТЬ * ИЗ ВсеМесяцыПериода УПОРЯДОЧИТЬ ПО ДатаМесяц

Этот запрос формирует временную таблицу ВсеМесяцыПериода, содержащую точные даты начал месяцев (например, 01.06.2023, 01.07.2023 ... 01.06.2024). Данный метод полностью решает проблему перехода через год и не зависит от заполненности производственного календаря в базе.

Решение 2. Классический запрос «Срез последних на каждую дату» (без использования СКД)

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

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

Посмотрим на практическую реализацию этого алгоритма в коде запроса:


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

// 2. Получаем реальные данные, которые есть в базе
ВЫБРАТЬ
    НАЧАЛОПЕРИОДА(Обороты.Период, МЕСЯЦ) КАК Период,
    Обороты.Подразделение КАК Подразделение,
    Обороты.ПоказательОборот КАК Значение
ПОМЕСТИТЬ ВТ_РеальныеДанные
ИЗ
    РегистрНакопления.НекийРегистр.Обороты(&НачалоПериода, &КонецПериода, Месяц, ) КАК Обороты
ИНДЕКСИРОВАТЬ ПО Период, Подразделение;

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

// 4. Соединяем сетку с реальными данными по найденным датам
ВЫБРАТЬ
    Сетка.Период КАК Период,
    Сетка.Подразделение КАК Подразделение,
    ЕСТЬNULL(Данные.Значение, 0) КАК ПоказательОборот
ИЗ
    ВТ_Сетка КАК Сетка
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_СоответствиеДаты КАК СвязьДаты
        ПО Сетка.Период = СвязьДаты.ПериодСетки
        И Сетка.Подразделение = СвязьДаты.Подразделение
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РеальныеДанные КАК Данные
        ПО СвязьДаты.ПериодПоследнихДанных = Данные.Период
        И СвязьДаты.Подразделение = Данные.Подразделение
УПОРЯДОЧИТЬ ПО
    Период,
    Подразделение

Преимущество метода: Запрос работает на чистом СУБД-движке, он полностью универсален и может быть выполнен в любой обработке или динамическом списке без привязки к СКД.

Решение 3. Использование штатного дополнения периодов в СКД

Если ваш отчет разрабатывается на Системе компоновки данных, генерировать таблицы дат вручную в запросе — это избыточная и неоптимальная работа. Платформа 1С умеет дополнять периоды автоматически на уровне компоновщика.

Рассмотрим, как настроить автодополнение без усложнения текста запроса:

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

После этого СКД сама сгенерирует недостающие строки в выводе отчета. В тех месяцах, где у подразделения не было оборотов, выведутся пустые значения (NULL).

Решение 4. Автозаполнение пустых периодов предыдущим значением через «ВычислитьВыражение» в СКД

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

Выясним, как настроить такой расчет:

Перейдем на вкладку «Ресурсы» в СКД.
Для нашего поля ПоказательОборот в колонку «Выражение» запишем следующую конструкцию:


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

Эта формула рассчитывает показатель нарастающим итогом. Если же нам требуется имитировать поведение среза последних (то есть выводить именно последнее актуальное значение, «протягивая» его сквозь пустые периоды), мы можем использовать условное выражение со смещением назад:


ЕСТЬNULL(Сумма(ПоказательОборот), ВычислитьВыражение("ЕСТЬNULL(Сумма(ПоказательОборот), 0)", "ПериодМесяц", , "Предыдущая", "Предыдущая"))

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

Решение 5. Роли полей остатков в СКД

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

Проанализируем этот механизм:

В наборе данных СКД для числового поля остатка перейдем в колонку «Роль».
Настроим роль для начального остатка: укажем имя поля и установим тип НачальныйОстаток.
Аналогично настроим конечный остаток: укажем тип КонечныйОстаток.
Свяжем оба поля остатка с вашим полем периода (например, ПериодМесяц).

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