В практике разработки на платформе 1С часто возникает задача построения отчета, который сочетал бы в себе данные о начальных остатках, детальных движениях (с указанием документов-регистраторов) и конечных остатках за определенный период. В этой статье мы разберем, почему традиционный метод объединения нескольких таблиц является неоптимальным и как решить эту задачу профессионально.
Рассмотрим ситуацию, с которой столкнулся автор темы. Для получения полной картины движений ТМЦ был написан запрос, состоящий из трех частей, объединенных оператором ОБЪЕДИНИТЬ ВСЕ:
Остатки на начало периода.Обороты за период с детализацией до регистратора.Остатки на конец периода.Проанализируем пример кода, который использовался изначально:
ВЫБРАТЬ
NULL КАК Период,
ОстаткиТМЦ.Номенклатура,
NULL КАК Регистратор,
СУММА(ОстаткиТМЦ.КоличествоОстаток) КАК КоличествоНачОст,
NULL КАК КоличествоПриход,
NULL КАК КоличествоРасход,
NULL КАК КоличествоКонОст
ПОМЕСТИТЬ ОстаткиИОбороты
ИЗ
РегистрНакопления.ОстаткиТМЦ.Остатки(&ДатаНачала) КАК ОстаткиТМЦ
СГРУППИРОВАТЬ ПО Номенклатура
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ОборотыТМЦ.Период,
ОборотыТМЦ.Номенклатура,
ОборотыТМЦ.Регистратор,
NULL,
ОборотыТМЦ.КоличествоПриход,
ОборотыТМЦ.КоличествоРасход,
NULL
ИЗ
РегистрНакопления.ОстаткиТМЦ.Обороты(&ДатаНачала, &ДатаОкончания, Регистратор) КАК ОборотыТМЦ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
NULL,
ОстаткиТМЦ.Номенклатура,
NULL,
NULL,
NULL,
NULL,
СУММА(ОстаткиТМЦ.КоличествоОстаток)
ИЗ
РегистрНакопления.ОстаткиТМЦ.Остатки(&ДатаОкончания) КАК ОстаткиТМЦ
СГРУППИРОВАТЬ ПО Номенклатура
Почему этот подход считается неоптимальным? Во-первых, система вынуждена выполнять три отдельных обращения к базе данных. Во-вторых, виртуальные таблицы остатков сами по себе являются ресурсоемкими, и их многократное использование в одном запросе создает избыточную нагрузку на СУБД. В-третьих, такой запрос сложно поддерживать: при добавлении нового измерения в регистр придется править код в трех-четырех местах.
Для решения подобных задач в 1С предусмотрена специализированная виртуальная таблица ОстаткиИОбороты. Она позволяет получить все необходимые данные (остатки на границы и обороты внутри них) за один проход. Основной секрет заключается в правильной настройке параметров этой таблицы.
Разберем основные параметры таблицы РегистрНакопления.ИмяРегистра.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, &Периодичность, &МетодДополнения, &Условие):
Регистратор. Это заставит таблицу выдать детальные строки по каждому документу, изменившему остаток.Посмотрим, как упрощается наш запрос при использовании этого метода:
ВЫБРАТЬ
ТМЦ.Период КАК Период,
ТМЦ.Регистратор КАК Регистратор,
ТМЦ.Номенклатура КАК Номенклатура,
ТМЦ.Склад КАК Склад,
ТМЦ.КоличествоНачальныйОстаток КАК НачОст,
ТМЦ.КоличествоПриход КАК Приход,
ТМЦ.КоличествоРасход КАК Расход,
ТМЦ.КоличествоКонечныйОстаток КАК КонОст
ИЗ
РегистрНакопления.ОстаткиТМЦ.ОстаткиИОбороты(&ДатаНачала, &ДатаОкончания, Регистратор, , ) КАК ТМЦ
Проанализируем ситуацию: в данном случае платформа автоматически рассчитает остаток перед каждым документом и после него. Это избавляет нас от необходимости вручную суммировать NULL значения и группировать результаты объединения.
При работе с остатками крайне важно понимать, как 1С обрабатывает границы времени. Если вы передаете в параметр &ДатаОкончания дату типа 31.12.2023 23:59:59, документы, проведенные ровно в эту секунду, могут не попасть в расчет конечного остатка, если использовать стандартные методы.
Ранее программисты часто использовали «лайфхак» ДОБАВИТЬКДАТЕ(&ДатаОкончания, СЕКУНДА, 1), чтобы захватить движения последней секунды. Однако в современной разработке правильным подходом является использование объекта Граница.
Рассмотрим пример создания границы в программном коде перед выполнением запроса:
КонецПериодаГраница = Новый Граница(КонецДня(ДатаОкончания), ВидГраницы.Включая);
Запрос.УстановитьПараметр("ДатаОкончания", КонецПериодаГраница);
Использование ВидГраницы.Включая гарантирует, что в выборку попадут все движения до последней миллисекунды указанного периода, и итоговый остаток будет рассчитан верно.
Если ваша цель — создать полноценный отчет, то лучшим решением будет использование СКД. В этом механизме заложен мощный функционал по работе с остатками, который работает еще эффективнее, чем «чистый» запрос.
Разберем основные шаги настройки СКД для нашей задачи:
ОстаткиИОбороты с периодичностью Авто или Регистратор.НачальныйОстаток и выберите соответствующее поле периода. Аналогично для конечного остатка — роль КонечныйОстаток.Выясним причину, почему это важно: СКД понимает специфику накопления данных и не будет просто суммировать остатки за каждый день (что дало бы неверный результат), а возьмет значение из первой и последней записи периода для соответствующей группировки.
В исходном сообщении темы прозвучал важный совет: при выборе данных из виртуальных таблиц, если вы выбираете измерения (Номенклатура, Склад), их не нужно дополнительно группировать оператором СГРУППИРОВАТЬ ПО в самом запросе к виртуальной таблице. Платформа 1С оптимизирует виртуальные таблицы таким образом, что данные из них и так возвращаются сгруппированными по измерениям регистра.
Лишние группировки только увеличивают время выполнения запроса на стороне SQL-сервера. Если вам нужно получить уникальные записи, достаточно правильно указать параметры виртуальной таблицы или использовать ИТОГИ для визуализации древовидной структуры отчета.
Подводя итог, можно сказать: использование UNION ALL для получения остатков — это метод, уходящий в прошлое. Переход на ОстаткиИОбороты с использованием объекта Граница и правильной настройкой ролей в СКД — это стандарт профессиональной разработки в 1С v8.3.