Получение остатков на каждый день периода — одна из наиболее часто встречающихся задач в разработке на платформе 1С:Предприятие. Она возникает при построении графиков остатков, выгрузке данных в сторонние аналитические системы (BI, CRM, ERP), а также при расчете оборачиваемости запасов. Основная сложность заключается в том, что в регистровых таблицах 1С записи присутствуют только на те даты, когда происходили реальные движения (приход или расход). Если движений по номенклатуре или контрагенту не было несколько дней, стандартная виртуальная таблица остатков и оборотов просто «пропустит» эти даты.
В этой статье мы подробно разберем, как решить эту проблему, проанализируем ошибки исходного запроса с форума и рассмотрим три наиболее эффективных и производительных метода решения задачи.
---
Рассмотрим подход, который изначально попытался применить автор темы. Он создал временную таблицу Дни на основе производственного календаря и попытался связать ее с виртуальной таблицей остатков и оборотов по условию неравенства:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СебестоимостьТоваров.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК Движения
ПО (Движения.Период <= ДНИ.Дата)
Этот подход имеет несколько критических недостатков, из-за которых его категорически не рекомендуется использовать в реальных рабочих базах данных:
<=) заставляет СУБД выполнять декартово произведение (Cartesian Product) или использовать крайне неэффективные алгоритмы соединения (например, Nested Loops). Если в таблице Дни будет 30 строк, а в таблице Движения — 10 000 строк, СУБД придется обработать огромное количество комбинаций. На больших базах такой запрос гарантированно приведет к зависанию сеанса или переполнению базы временных таблиц (TempDB).СУММА(ВЫБОР...) внутри тяжелого запроса часто приводит к логическим ошибкам, смещению дат на один день (вперед или назад) и некорректному учету начального остатка.Давайте разберем правильные, производительные и изящные способы решения этой задачи, которые используются профессиональными разработчиками.
---
Это самый простой, быстрый и штатный способ получения остатков на каждый день средствами платформы 1С, если результат запроса обрабатывается далее в коде на встроенном языке. Нам не нужно использовать производственные календари или генерировать таблицы дат вручную. Платформа все сделает сама.
Разберем этот метод на примере регистра накопления РасчетыСКлиентами. Нам нужно получить остаток долга партнеров на каждый день периода.
Шаг 1. Пишем запрос с секцией ИТОГИ:
ВЫБРАТЬ
Взаиморасчеты.Партнер КАК Партнер,
Взаиморасчеты.Период КАК Период,
СУММА(Взаиморасчеты.СуммаКонечныйОстаток) КАК СуммаОстатка
ИЗ
РегистрНакопления.ВзаиморасчетыСКлиентами.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК Взаиморасчеты
СГРУППИРОВАТЬ ПО
Взаиморасчеты.Партнер,
Взаиморасчеты.Период
ИТОГИ
СУММА(СуммаОстатка)
ПО
Партнер,
Период ПЕРИОДАМИ(ДЕНЬ, &НачалоПериода, &КонецПериода)
Шаг 2. Обходим результат запроса во встроенном языке с использованием специального параметра:
ВыборкаПартнеры = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Партнер");
Пока ВыборкаПартнеры.Следующий() Цикл
// Ключевой момент: передаем параметр "ВСЕ", чтобы дополнить периоды
ВыборкаПериоды = ВыборкаПартнеры.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Период", "ВСЕ");
Пока ВыборкаПериоды.Следующий() Цикл
// Здесь мы получим строчку на абсолютно каждый день периода,
// даже если в этот день не было движений.
// Платформа автоматически продублирует остаток предыдущего дня.
Сообщить("Партнер: " + ВыборкаПартнеры.Партнер +
" | Дата: " + Формат(ВыборкаПериоды.Период, "ДФ=dd.MM.yyyy") +
" | Остаток: " + ВыборкаПериоды.СуммаОстатка);
КонецЦикла;
КонецЦикла;
Преимущества метода:
Ограничения метода:
ИТОГИ, нельзя использовать внутри пакета запросов для помещения во временную таблицу (конструкция ПОМЕСТИТЬ выдаст синтаксическую ошибку). Поэтому этот метод подходит только тогда, когда выборка является финальным шагом алгоритма.---
Если вам нужно получить готовую ТаблицуЗначений с остатками на каждый день (например, для последующей выгрузки в XML, JSON или отправки по API), то идеальным решением будет использование Системы компоновки данных (СКД). Многие разработчики избегают СКД в фонового режиме, считая ее инструментом исключительно для визуальных отчетов. Это заблуждение.
В СКД встроен мощнейший механизм дополнения периодов, который работает на уровне СУБД и оптимизирован под любые объемы данных.
Рассмотрим по шагам, как настроить и программно выполнить такую схему:
Шаг 1. Настройка запроса в СКД
Создаем схему компоновки данных. В наборе данных - запрос пишем стандартное обращение к виртуальной таблице остатков и оборотов с периодичностью День:
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.Период КАК Период,
Остатки.КоличествоКонечныйОстаток КАК Количество
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК Остатки
Шаг 2. Настройка роли поля "Период"
Это важнейший шаг. В настройках набора данных для поля Период необходимо открыть свойства и указать:
&НачалоПериода и &КонецПериода).После этого СКД сама поймет, что при формировании результата нужно сгенерировать пустые дни и перенести на них остатки из предыдущих периодов.
Шаг 3. Программный вывод в Таблицу Значений
В модуле нашей обработки пишем следующий код для вывода результата СКД напрямую в коллекцию без вывода на экран:
// Получаем схему из макета обработки
СхемаКомпоновкиДанных = ПолучитьМакет("СхемаКомпоновкиДанных");
// Устанавливаем параметры
Настройки = СхемаКомпоновкиДанных.НастройкиПоУмолчанию;
ПараметрНачало = Настройки.ПараметрыДанных.Элементы.Найти("НачалоПериода");
ПараметрНачало.Значение = Объект.НачалоПериода;
ПараметрНачало.Использование = Истина;
ПараметрКонец = Настройки.ПараметрыДанных.Элементы.Найти("КонецПериода");
ПараметрКонец.Значение = КонецДня(Объект.КонецПериода);
ПараметрКонец.Использование = Истина;
// Инициализируем компоновщик макета
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки,,, Тип("ГенераторМакетаКомпоновкиДанныхДляКоллекцииЗначений"));
// Проводим компоновку данных
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки);
// Выгружаем результат в Таблицу Значений
ТаблицаОстатков = Новый ТаблицаЗначений;
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВКоллекциюЗначений;
ПроцессорВывода.УстановитьОбъект(ТаблицаОстатков);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
// ТаблицаОстатков теперь содержит полные данные на каждый день периода!
// Можно обходить ее и формировать XML или выполнять другие действия.
Преимущества метода:
---
Что делать, если вам нужно использовать полученную таблицу ежедневных остатков внутри сложного пакета запросов (например, соединить ее с ценами на каждый день, а затем с планами продаж)? Метод ИТОГИ здесь не сработает, а СКД потребует разбиения алгоритма на несколько этапов во встроенном языке.
В этом случае мы можем применить оптимизированный математический алгоритм расчета нарастающего итога без тяжелых соединений по неравенству. Идея алгоритма заключается в следующем:
НачальныйОстатокПериода + НарастающийОборот.Проанализируем пример такого запроса:
// Шаг 1: Генерируем числа от 0 до 999 для создания сетки дней
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ Цифры ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Генерируем таблицу всех дат периода
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, т1.Цифра + т2.Цифра * 10 + т3.Цифра * 100) КАК Дата
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
Цифры КАК т1
КРОСС СОЕДИНЕНИЕ Цифры КАК т2
КРОСС СОЕДИНЕНИЕ Цифры КАК т3
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, т1.Цифра + т2.Цифра * 10 + т3.Цифра * 100) <= &КонецПериода;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Получаем начальный остаток на начало периода в целом
ВЫБРАТЬ
Остатки.Номенклатура КАК Номенклатура,
Остатки.КоличествоОстаток КАК НачальныйОстаток
ПОМЕСТИТЬ ВТ_НачальныйОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, ) КАК Остатки;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Получаем обороты по дням
ВЫБРАТЬ
Обороты.Номенклатура КАК Номенклатура,
НАЧАЛОПЕРИОДА(Обороты.Период, ДЕНЬ) КАК Период,
Обороты.КоличествоОборот КАК Оборот
ПОМЕСТИТЬ ВТ_Обороты
ИЗ
РегистрНакопления.ТоварыНаСкладах.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК Обороты;
////////////////////////////////////////////////////////////////////////////////
// Шаг 5: Соединяем все данные и рассчитываем остаток на каждый день
ВЫБРАТЬ
Календарь.Дата КАК Период,
Начальный.Номенклатура КАК Номенклатура,
// Математический расчет остатка: Начальный остаток + Нарастающий оборот до текущей даты включительно
ЕСТЬNULL(Начальный.НачальныйОстаток, 0) + СУММА(ЕСТЬNULL(ОборотыПредыдущие.Оборот, 0)) КАК КоличествоНачальныйОстаток
ИЗ
ВТ_Календарь КАК Календарь
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_НачальныйОстаток КАК Начальный
ПО ИСТИНА
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Обороты КАК ОборотыПредыдущие
ПО Начальный.Номенклатура = ОборотыПредыдущие.Номенклатура
И ОборотыПредыдущие.Период < Календарь.Дата
СГРУППИРОВАТЬ ПО
Календарь.Дата,
Начальный.Номенклатура,
Начальный.НачальныйОстаток
Важное примечание по расчету: Обратите внимание на условие связи в последнем пакете: ОборотыПредыдущие.Период < Календарь.Дата. Использование строгого знака «меньше» позволяет получить остаток именно на начало каждого дня. Если вам нужен остаток на конец дня, условие связи следует заменить на <=.
Этот запрос работает значительно быстрее классического соединения «в лоб» из исходного сообщения автора, так как левое соединение происходит по агрегированной таблице оборотов, которая в разы меньше таблицы детальных движений регистра.
---
Мы проанализировали три основных подхода к решению задачи. Выбор конкретного метода зависит от архитектуры вашего решения: