При разработке отчетов в 1С (например, для анализа динамики складских запасов или построения графиков) мы часто сталкиваемся с одной и той же проблемой. Если использовать стандартную виртуальную таблицу регистра накопления, система выдает данные только за те периоды, когда были какие-либо движения (приходы или расходы). В результате, если товар лежал на складе без движения неделю, в отчете эти дни просто выпадут.
В этой статье мы подробно разберем, как решить эту задачу. Мы научимся строить запрос, который возвращает остатки на каждый день выбранного периода, даже если в эти дни не было никаких операций.
Предположим, у нас есть регистр накопления ТоварыНаСкладах. Нам необходимо получить отчет по остаткам конкретного товара за январь месяц на каждый день.
Если мы напишем простой запрос к виртуальной таблице ОстаткиИОбороты с периодичностью "День", результат нас разочарует. Посмотрим на логику работы платформы:
Для решения этой проблемы нам необходимо сформировать так называемый "скелет" отчета — непрерывную последовательность дат, к которой мы затем "приклеим" наши данные об остатках.
Первым делом нам нужно получить таблицу, содержащую все даты интересующего нас периода. Рассмотрим способ генерации такой таблицы непосредственно в запросе, не прибегая к циклам на встроенном языке. Это наиболее производительный метод.
Воспользуемся классическим приемом "умножения" таблиц (декартово произведение). Создадим небольшую таблицу с числами от 0 до 9, а затем объединим ее саму с собой, чтобы получить последовательность чисел (смещение в днях).
Разберем код генерации календаря по шагам:
ВЫБРАТЬ
0 КАК Цифра
ПОМЕСТИТЬ ТЗ_Цифры
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 9;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ,
ТЗ_Единицы.Цифра +
ТЗ_Десятки.Цифра * 10 +
ТЗ_Сотни.Цифра * 100
) КАК ПериодДень
ПОМЕСТИТЬ ТЗ_Календарь
ИЗ
ТЗ_Цифры КАК ТЗ_Единицы,
ТЗ_Цифры КАК ТЗ_Десятки,
ТЗ_Цифры КАК ТЗ_Сотни
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ,
ТЗ_Единицы.Цифра +
ТЗ_Десятки.Цифра * 10 +
ТЗ_Сотни.Цифра * 100
) <= &КонецПериода
;
Проанализируем, что мы сделали:
ТЗ_Цифры, содержащую 10 записей.ДОБАВИТЬКДАТЕ мы прибавляем полученное число дней к дате начала периода.ГДЕ мы отсекаем даты, выходящие за границы конца периода.Теперь у нас во временной таблице ТЗ_Календарь есть список всех дней.
Здесь кроется важный нюанс. Если мы просто соединим Календарь с таблицей Остатков, мы получим данные только по тем товарам, которые были на остатках в конкретный день. Но нам нужно видеть ситуацию по всем интересующим товарам на каждый день.
Для этого нам нужно сформировать "Полную таблицу комбинаций". Каждая дата должна сочетаться с каждым товаром. Рассмотрим, как это сделать:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ТЗ_Товары
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В (&СписокТоваров)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТЗ_Календарь.ПериодДень КАК Период,
ТЗ_Товары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ТЗ_Каркас
ИЗ
ТЗ_Календарь КАК ТЗ_Календарь,
ТЗ_Товары КАК ТЗ_Товары
;
Обратите внимание: в последнем запросе мы не используем оператор СОЕДИНЕНИЕ. Мы просто перечисляем таблицы через запятую (или используем ПОЛНОЕ СОЕДИНЕНИЕ ... ПО ИСТИНА). Это называется Декартово произведение. Теперь, если у нас 30 дней и 5 товаров, в таблице ТЗ_Каркас будет ровно 150 строк.
Теперь получим реальные данные из регистра. Используем виртуальную таблицу ОстаткиИОбороты. Важно использовать именно её, а не просто Остатки, так как нам нужна динамика внутри периода.
В параметрах виртуальной таблицы укажем периодичность ДЕНЬ и метод дополнения движений ДвиженияИГраницыПериода, чтобы не потерять входящий остаток.
ВЫБРАТЬ
ТоварыНаСкладах.Период КАК Период,
ТоварыНаСкладах.Номенклатура КАК Номенклатура,
ТоварыНаСкладах.ВНаличииКонечныйОстаток КАК Остаток
ПОМЕСТИТЬ ТЗ_Данные
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
ДЕНЬ,
ДвиженияИГраницыПериода,
Номенклатура В (&СписокТоваров)
) КАК ТоварыНаСкладах
;
Это самый сложный и важный момент. Мы не можем просто сделать левое соединение ТЗ_Каркас с ТЗ_Данные по равенству дат. Почему? Потому что в ТЗ_Данные есть записи только за те дни, когда остаток менялся (или был на начало/конец). Если 5-го числа движений не было, записи там нет, и при соединении мы получим NULL.
Существует два основных подхода к решению этой проблемы в запросе.
Мы можем соединить каждую дату календаря с ближайшей предшествующей записью остатков.
ВЫБРАТЬ
Каркас.Период,
Каркас.Номенклатура,
ЕСТЬNULL(Данные.Остаток, 0) КАК Остаток
ИЗ
ТЗ_Каркас КАК Каркас
ЛЕВОЕ СОЕДИНЕНИЕ ТЗ_Данные КАК Данные
ПО Каркас.Номенклатура = Данные.Номенклатура
И Данные.Период В (
ВЫБРАТЬ ПЕРВЫЕ 1
Т.Период
ИЗ
ТЗ_Данные КАК Т
ГДЕ
Т.Номенклатура = Каркас.Номенклатура
И Т.Период <= Каркас.Период
УПОРЯДОЧИТЬ ПО
Т.Период УБЫВ
)
Однако этот способ (коррелированный подзапрос) может работать медленно на больших объемах данных. Поэтому рассмотрим более профессиональный подход — расчет нарастающим итогом через обороты или использование особенностей СКД, если отчет строится на ней.
Но если мы пишем "чистый" запрос для получения таблицы значений, лучше всего работает метод "Дополнения" через постобработку или более хитрый запрос.
Рассмотрим улучшенный алгоритм для чистого SQL-запроса 1С:
Однако, самый надежный и универсальный метод в 1С 8.3 для "чистого запроса" — это получение остатков на каждую секунду изменения, а затем "размножение" этого остатка на последующие пустые дни до следующего изменения.
Давайте реализуем финальный запрос, используя подход с интервалами. Идея такая: каждая запись об остатке действует от "своей" даты до даты следующей записи.
// 1. Получаем таблицу остатков и оборотов (уже есть в ТЗ_Данные)
// Нам нужно определить дату следующего изменения для каждой записи.
ВЫБРАТЬ
Т1.Номенклатура,
Т1.Период КАК ДатаНачала,
МИНИМУМ(ЕСТЬNULL(Т2.Период, &КонецПериода)) КАК ДатаОкончания,
Т1.Остаток
ПОМЕСТИТЬ ТЗ_Интервалы
ИЗ
ТЗ_Данные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ТЗ_Данные КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т2.Период > Т1.Период
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.Период,
Т1.Остаток
;
// 2. Теперь соединяем наш календарь с интервалами
ВЫБРАТЬ
Каркас.Период,
Каркас.Номенклатура,
ЕСТЬNULL(Интервалы.Остаток, 0) КАК Остаток
ИЗ
ТЗ_Каркас КАК Каркас
ЛЕВОЕ СОЕДИНЕНИЕ ТЗ_Интервалы КАК Интервалы
ПО Каркас.Номенклатура = Интервалы.Номенклатура
И Каркас.Период >= Интервалы.ДатаНачала
И Каркас.Период < Интервалы.ДатаОкончания
ОБЪЕДИНИТЬ ВСЕ
// Не забываем про последний день периода, если он попадает в интервал
ВЫБРАТЬ
Каркас.Период,
Каркас.Номенклатура,
ЕСТЬNULL(Интервалы.Остаток, 0)
ИЗ
ТЗ_Каркас КАК Каркас
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТЗ_Интервалы КАК Интервалы
ПО Каркас.Номенклатура = Интервалы.Номенклатура
И Каркас.Период = &КонецПериода
И Интервалы.ДатаОкончания = &КонецПериода
Если ваша задача — вывести отчет, а не получить таблицу значений для программной обработки, все становится намного проще. Система Компоновки Данных (СКД) умеет делать это "из коробки".
Разберем настройку СКД для решения этой задачи:
ОстаткиИОбороты. Обязательно выберите поле Период.Период в списке доступных полей (обычно в роли Измерения).Период свойство "Дополнение периода" (Role -> Period Supplement).При такой настройке СКД сама автоматически:
При использовании метода с генерацией дат в запросе, обратите внимание на следующие моменты:
ТЗ_Каркас и ТЗ_Данные) обязательно индексируйте поля соединения (Номенклатура, Период). Это ускорит выполнение в разы.ЕСТЬNULL (ISNULL) для полей ресурсов при левом соединении, иначе математические операции с такими полями вернут NULL.Мы рассмотрели два основных пути решения проблемы "пустых дней":
Путь 1 (Программный): Создание таблицы дат (календаря) через объединение цифр, формирование каркаса (даты * товары) и соединение с интервалами актуальности остатков. Это дает полный контроль и позволяет получить Таблицу Значений для дальнейшей работы в коде.
Путь 2 (Отчетный): Использование встроенных механизмов "Дополнения периода" в СКД. Это самый быстрый способ, если конечная цель — визуализация данных пользователю.
Используйте первый метод для сложных алгоритмов расчета потребностей и автозаказа, а второй — для всех пользовательских отчетов.