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