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

← На главную

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

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

Почему нельзя использовать запрос в цикле

Самое очевидное, но крайне неэффективное решение, которое часто выбирают начинающие разработчики, — это обход списка объектов в цикле и выполнение отдельного запроса для каждого объекта с передачей индивидуальных параметров. Рассмотрим этот антипаттерн:


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

Почему это плохо? Каждый запрос в цикле генерирует отдельное обращение к СУБД. Если в вашей таблице будет 100 строк, система выполнит 100 запросов. Если строк будет 10 000, база данных может полностью зависнуть из-за чрезмерной нагрузки и сетевых задержек. Наша цель — выполнить все расчеты в рамках одного запроса.

Решение 1. Использование связи по периоду в соединении таблиц

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

Рассмотрим по шагам, как построить такой запрос:

Поместим исходную таблицу с лицевыми счетами и их индивидуальными периодами во временную таблицу ВТ_СводныеОстатки.
Выполним левое соединение временной таблицы с виртуальной таблицей ОстаткиИОбороты регистра. При этом параметры периода виртуальной таблицы оставим пустыми.
В секции ПО укажем условие соответствия периодов: период движения регистра должен находиться между начальной и конечной датами из нашей временной таблицы.

Посмотрим на пример практической реализации этого запроса:


ВЫБРАТЬ
    ТаблицаДолг.ЛС КАК ЛС,
    ТаблицаДолг.Помещение КАК Помещение,
    // Приводим время конца периода к концу дня 23:59:59
    КОНЕЦПЕРИОДА(ТаблицаДолг.КонецПериода, ДЕНЬ) КАК КонецПериодаДолг,
    НАЧАЛОПЕРИОДА(ТаблицаДолг.НачалоПериода, ДЕНЬ) КАК НачалоПериодаДолг
ПОМЕСТИТЬ ВТ_СводныеОстатки
ИЗ
    &ТаблицаДолг КАК ТаблицаДолг
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_СводныеОстатки.ЛС КАК ЛС,
    ВТ_СводныеОстатки.Помещение КАК Помещение,
    ВТ_СводныеОстатки.НачалоПериодаДолг КАК НачалоПериодаДолг,
    ВТ_СводныеОстатки.КонецПериодаДолг КАК КонецПериодаДолг,
    Взаиморасчеты.СуммаКонечныйОстаток КАК СуммаДолга
ИЗ
    ВТ_СводныеОстатки КАК ВТ_СводныеОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.КУ_Взаиморасчеты.ОстаткиИОбороты(, , Регистратор, , ) КАК Взаиморасчеты
        ПО Взаиморасчеты.ЛицевойСчет = ВТ_СводныеОстатки.ЛС
        И Взаиморасчеты.Помещение = ВТ_СводныеОстатки.Помещение
        И Взаиморасчеты.Период МЕЖДУ ВТ_СводныеОстатки.НачалоПериодаДолг И ВТ_СводныеОстатки.КонецПериодаДолг

Важный нюанс с границами дат: Как справедливо замечено на форуме, всегда используйте функции НАЧАЛОПЕРИОДА и КОНЕЦПЕРИОДА. Без приведения конечной даты к концу дня (КОНЕЦПЕРИОДА(ТаблицаДолг.КонецПериода, ДЕНЬ)) время даты установится в значение 00:00:00, и вы потеряете все движения и остатки, зафиксированные внутри последнего дня выбранного периода.

Получение только актуального остатка на последнюю дату периода

Поскольку в предыдущем запросе мы использовали детализацию до регистратора, левое соединение вернет нам все движения (строки) за указанный период. Чтобы получить актуальный остаток на самую последнюю дату движения внутри индивидуального периода, выполним двухэтапный запрос с использованием агрегатной функции МАКСИМУМ.

Разберем этот алгоритм:


// Шаг 1: Получаем максимальную дату движения для каждого ЛС в его периоде
ВЫБРАТЬ
    ВТ_СводныеОстатки.ЛС КАК ЛС,
    ВТ_СводныеОстатки.Помещение КАК Помещение,
    МАКСИМУМ(Взаиморасчеты.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
    ВТ_СводныеОстатки КАК ВТ_СводныеОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.КУ_Взаиморасчеты.ОстаткиИОбороты(, , Регистратор, , ) КАК Взаиморасчеты
        ПО Взаиморасчеты.ЛицевойСчет = ВТ_СводныеОстатки.ЛС
        И Взаиморасчеты.Период МЕЖДУ ВТ_СводныеОстатки.НачалоПериодаДолг И ВТ_СводныеОстатки.КонецПериодаДолг
СГРУППИРОВАТЬ ПО
    ВТ_СводныеОстатки.ЛС,
    ВТ_СводныеОстатки.Помещение
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем конечные остатки, соединяя по найденной максимальной дате
ВЫБРАТЬ
    ВТ_МаксимальныеДаты.ЛС КАК ЛС,
    ВТ_МаксимальныеДаты.Помещение КАК Помещение,
    Взаиморасчеты.СуммаКонечныйОстаток КАК ФинальныйОстаток
ИЗ
    ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.КУ_Взаиморасчеты.ОстаткиИОбороты(, , Регистратор, , ) КАК Взаиморасчеты
        ПО Взаиморасчеты.ЛицевойСчет = ВТ_МаксимальныеДаты.ЛС
        И Взаиморасчеты.Помещение = ВТ_МаксимальныеДаты.Помещение
        И Взаиморасчеты.Период = ВТ_МаксимальныеДаты.МаксПериод

Решение 2. Оптимизация производительности (Минимизация сканирования СУБД)

Хотя предыдущее решение полностью рабочее, у него есть скрытая проблема производительности. Оставленные пустыми параметры виртуальной таблицы ОстаткиИОбороты(, , ...) заставляют СУБД сканировать всю историю регистра накопления с самого начала ведения учета. На больших базах данных это приведет к фатальному замедлению работы.

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


// 1. Сначала в коде накладываем отбор и находим глобальные границы
МинимальноеНачало = Неопределено;
МаксимальныйКонец = Неопределено;

Для каждого Стр Из ТаблицаДолг Цикл
    Если МинимальноеНачало = Неопределено Или Стр.НачалоПериода < МинимальноеНачало Тогда
        МинимальноеНачало = Стр.НачалоПериода;
    КонецЕсли;
    Если МаксимальныйКонец = Неопределено Или Стр.КонецПериода > МаксимальныйКонец Тогда
        МаксимальныйКонец = Стр.КонецПериода;
    КонецЕсли;
КонецЦикла;

// Корректируем время
МинимальноеНачало = НачалоПериода(МинимальноеНачало, "День");
МаксимальныйКонец = КонецПериода(МаксимальныйКонец, "День");

// 2. Передаем эти параметры в запрос
Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    ВТ_СводныеОстатки.ЛС КАК ЛС,
    |    ВТ_СводныеОстатки.Помещение КАК Помещение,
    |    Взаиморасчеты.СуммаКонечныйОстаток КАК СуммаДолга
    |ИЗ
    |    ВТ_СводныеОстатки КАК ВТ_СводныеОстатки
    |    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.КУ_Взаиморасчеты.ОстаткиИОбороты(&ГлобальноеНачало, &ГлобальныйКонец, Регистратор, , ) КАК Взаиморасчеты
    |        ПО Взаиморасчеты.ЛицевойСчет = ВТ_СводныеОстатки.ЛС
    |        И Взаиморасчеты.Период МЕЖДУ ВТ_СводныеОстатки.НачалоПериодаДолг И ВТ_СводныеОстатки.КонецПериодаДолг";

Запрос.УстановитьПараметр("ГлобальноеНачало", МинимальноеНачало);
Запрос.УстановитьПараметр("ГлобальныйКонец", МаксимальныйКонец);
Запрос.УстановитьПараметр("ТаблицаДолг", ТаблицаДолг);

Благодаря этому СУБД будет анализировать данные регистра только в узком интервале времени (например, в рамках одного конкретного года, охватывающего периоды всех лицевых счетов), что ускорит выполнение запроса в десятки и сотни раз.

Решение 3. Использование механизма компоновки данных (СКД)

Если задача решается в контексте построения отчета, то наиболее производительным и рекомендуемым фирмой «1С» способом является использование **Связей наборов данных в СКД**. СКД умеет самостоятельно оптимизировать запросы к виртуальным таблицам и передавать параметры динамически для каждой строки.

Рассмотрим по шагам настройку СКД для решения данной задачи:

Создайте Набор данных - запрос (Назовем его НаборДанных_ЛС). В нем выберите лицевые счета и их индивидуальные даты:


ВЫБРАТЬ
    ТаблицаЛС.ЛицевойСчет КАК ЛицевойСчет,
    ТаблицаЛС.НачалоПериода КАК НачалоПериода,
    ТаблицаЛС.КонецПериода КАК КонецПериода
ИЗ
    &ТаблицаЛС КАК ТаблицаЛС

Создайте второй Набор данных - запрос (Назовем его НаборДанных_Остатки). В его запросе обратитесь к виртуальной таблице с использованием параметров-подстановок:


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

Перейдите на вкладку Связи наборов данных и настройте связь:
- Источник связи: НаборДанных_ЛС
- Приемник связи: НаборДанных_Остатки
- Условие связи: ЛицевойСчет = ЛицевойСчет
- Параметры: укажите имя параметра КонецПериода и сопоставьте его с выражением источника — полем КонецПериода. Также передайте параметр ЛицевойСчет.

При выполнении отчета СКД автоматически разобьет ваш список лицевых счетов на порции и выполнит оптимальные параметризованные запросы на уровне СУБД, обеспечивая максимальное быстродействие.