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

← На главную

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

Метод 1. Использование виртуальной таблицы ОстаткиИОбороты с периодичностью

Самый простой и очевидный способ — обратиться к виртуальной таблице ОстаткиИОбороты. Платформа позволяет указать периодичность получения данных прямо в параметрах таблицы. Рассмотрим, как это выглядит в коде запроса.


ВЫБРАТЬ
    РегистрОстатки.Период КАК Месяц,
    РегистрОстатки.Номенклатура КАК Товар,
    РегистрОстатки.КоличествоКонечныйОстаток КАК Остаток
ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Месяц, , ) КАК РегистрОстатки

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

Метод 2. Генерация календарной шкалы (сетки периодов)

Чтобы "проявить" пустые месяцы, нам понадобится эталонный список всех месяцев в выбранном диапазоне. Если в вашей конфигурации есть регистр сведений Календарь или ПроизводственныйКалендарь, задачу решить проще. Но что делать, если такого регистра нет? Мы можем создать календарь динамически прямо в запросе.

Разберем пример создания временной таблицы с числами, которые потом преобразуем в даты:


ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ Цифры ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 
;
ВЫБРАТЬ
    ДОБАВИТЬКДАТЕ(&НачалоПериода, МЕСЯЦ, Т1.Цифра + Т2.Цифра * 5) КАК Месяц
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
    Цифры КАК Т1,
    Цифры КАК Т2
ГДЕ
    ДОБАВИТЬКДАТЕ(&НачалоПериода, МЕСЯЦ, Т1.Цифра + Т2.Цифра * 5) <= &КонецПериода

В данном примере мы создаем сетку из 25 месяцев (5х5). После того как у нас есть ВТ_Календарь, мы можем соединить её с нашими остатками. Дополнительные варианты построения числовых и календарных диапазонов рассмотрены в статье с примерами генерации дат и периодических остатков в запросах.

Метод 3. Метод развертки (Cross Join) и расчет нарастающего итога

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

Формируем временную таблицу уникальных товаров, которые нас интересуют.
Делаем полное соединение (декартово произведение) таблицы товаров и таблицы календаря. Так мы получим "сетку": каждый товар на каждый месяц.
Левым соединением подтягиваем обороты за эти месяцы.
Рассчитываем остаток как начальный остаток на дату старта + сумма всех оборотов до текущего месяца включительно.

Посмотрим на пример эмуляции оконной функции (Running Total) для расчета остатка:


ВЫБРАТЬ
    Т1.Товар,
    Т1.Месяц,
    СУММА(ЕСТЬNULL(Т2.Оборот, 0)) + &НачальныйОстаток КАК ОстатокНаКонец
ИЗ
    ВТ_Сетка КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОборотыПоМесяцам КАК Т2
    ПО Т1.Товар = Т2.Товар
    И Т1.Месяц >= Т2.Месяц
СГРУППИРОВАТЬ ПО
    Т1.Товар,
    Т1.Месяц

Важный нюанс: соединение таблицы самой с собой по условию Т1.Месяц >= Т2.Месяц является ресурсоемким (квадратичная зависимость). Мы рекомендуем использовать этот метод только на предварительно агрегированных данных во временных таблицах, чтобы не замедлять работу базы данных.

Метод 4. Постобработка в таблице значений

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

Рассмотрим алгоритм совместной работы:

Запросом получаем начальный остаток на &НачалоПериода.
Тем же запросом получаем обороты, сгруппированные по месяцам (где они были).
Выгружаем результат в ТаблицаЗначений.
В цикле обходим нужный период дат, создаем недостающие строки и заполняем остаток по формуле: ОстатокТекущий = ОстатокПредыдущий + ОборотТекущий.


// Пример логики обхода:
ТекущийОстаток = НачальноеЗначение;
Для Каждого МесяцИзСписка Из МассивМесяцев Цикл
    СтрокаДанных = ТЗРезультат.Найти(МесяцИзСписка, "Период");
    Если СтрокаДанных = Неопределено Тогда
        НоваяСтрока = ТЗРезультат.Добавить();
        НоваяСтрока.Период = МесяцИзСписка;
        НоваяСтрока.Остаток = ТекущийОстаток;
    Иначе
        СтрокаДанных.Остаток = ТекущийОстаток + СтрокаДанных.Оборот;
        ТекущийОстаток = СтрокаДанных.Остаток;
    КонецЕсли;
КонецЦикла;

Метод 5. Использование СКД (Системы компоновки данных)

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

Проанализируем, что происходит "под капотом":

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

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