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

← На главную

Разработчики в среде 1С часто сталкиваются с классической архитектурной задачей: необходимо получить остатки по регистру накопления не на одну фиксированную дату, а на целый список различных дат для разных элементов номенклатуры (или других измерений). Например, нам нужно узнать остаток товара «Стол письменный» на 01.01.2018, а товара «Стол обеденный» — на 20.03.2018.

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

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

---

Способ 1. Использование связей наборов данных в СКД (Рекомендуемый способ для отчетов)

Если ваша задача реализуется в рамках отчета на Системе компоновки данных (СКД), то платформа предоставляет великолепный встроенный инструмент, который решает эту проблему автоматически на уровне платформы.

Выполним настройку по шагам:

Создадим Набор данных - запрос (Источник). В этом запросе мы получаем нашу исходную таблицу, содержащую номенклатуру и индивидуальную дату, на которую требуется получить остаток. Назовем этот набор НаборДанныхТаблицаДаты.
```
ВЫБРАТЬ
    ИсходнаяТаблица.Номенклатура КАК Номенклатура,
    ИсходнаяТаблица.ДатаСреза КАК ДатаСреза
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
    &ТаблицаДокумента КАК ИсходнаяТаблица
```
Создадим Набор данных - запрос (Приемник). В этом наборе мы пишем простой запрос к виртуальной таблице остатков регистра накопления. В параметрах виртуальной таблицы мы обязательно указываем отбор по параметрам. Назовем набор НаборДанныхОстатки.
```
ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ТоварыНаСкладахОстатки.ВНаличииОстаток КАК Остаток
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, Номенклатура = &Номенклатура) КАК ТоварыНаСкладахОстатки
```
Настроим связи наборов данных. Перейдем на вкладку «Связи наборов данных» в конструкторе схемы компоновки:
- Источник связи: НаборДанныхТаблицаДаты
- Приемник связи: НаборДанныхОстатки
- Условие связи (Выражение источник): Номенклатура
- Условие связи (Выражение приемник): Номенклатура
- Параметры: в колонке параметров связываем параметр Период с полем источника ДатаСреза, а параметр Номенклатура с полем источника Номенклатура.

Почему это работает быстро? Платформа 1С при выполнении компоновки данных не делает глупых построчных запросов в цикле к базе данных. Она группирует передаваемые параметры пакетами (временными таблицами) и отправляет на SQL-сервер оптимизированный запрос, что обеспечивает высокую скорость выполнения.

---

Способ 2. Расчет остатка на произвольные даты через виртуальную таблицу «Обороты» (Метод нарастающего итога)

Если решение задачи требуется в «чистом» коде на встроенном языке (например, при проведении документов или в фоновом задании), метод СКД нам не подойдет. В этом случае мы воспользуемся классическим математическим алгоритмом расчета остатков через обороты.

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

Рассмотрим по шагам реализацию этого механизма в одном пакете запросов:


// Шаг 1: Помещаем исходную таблицу пар "Номенклатура - Дата" во временную таблицу
ВЫБРАТЬ
    ИсходнаяТаблица.Номенклатура КАК Номенклатура,
    ИсходнаяТаблица.ДатаСреза КАК ДатаСреза
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
    &ТаблицаПараметров КАК ИсходнаяТаблица
;

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

////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Получаем остатки по всей нашей номенклатуре на самую раннюю (минимальную) дату
ВЫБРАТЬ
    ТоварыНаСкладахОстатки.Номенклатура КАК Номенклатура,
    ТоварыНаСкладахОстатки.ВНаличииОстаток КАК НачальныйОстаток
ПОМЕСТИТЬ ВТ_НачальныеОстатки
ИЗ
    ВТ_ГраничныеДаты КАК ВТ_ГраничныеДаты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
                &МинимальнаяДата,
                Номенклатура В (ВЫБРАТЬ ВТ_ИсходныеДанные.Номенклатура ИЗ ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные)
            ) КАК ТоварыНаСкладахОстатки
        ПО ИСТИНА
;

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

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

Важное примечание по оптимизации: В условии соединения оборотов мы указали строгую фильтрацию ВТ_Обороты.Период < ВТ_ИсходныеДанные.ДатаСреза. Это позволяет динамически рассчитать остаток на секунду 00:00:00 указанного дня среза. Если вам необходимо получить остатки на конец дня (включая движения этого дня), условие соединения необходимо скорректировать на <=.

---

Способ 3. Модифицированный «метод Ильдаровича» (Свертка движений без левого соединения)

Существует еще более изящная и быстрая вариация расчета остатков на разные даты (часто упоминаемая в профессиональном сообществе как «метод Ильдаровича»). Этот метод минимизирует количество тяжелых соединений СУБД за счет использования агрегатных функций с условием ВЫБОР.

Вместо того чтобы делать ресурсоемкое левое соединение оборотов с условием «меньше даты», мы можем объединить начальные остатки и обороты в одну общую таблицу с помощью оператора ОБЪЕДИНИТЬ ВСЕ, а затем рассчитать остаток.

Рассмотрим структуру такого запроса:


// Предположим, что ВТ_ИсходныеДанные, ВТ_НачальныеОстатки и ВТ_Обороты уже получены, как в предыдущем примере.
// Объединим их в одну плоскую таблицу движений
ВЫБРАТЬ
    ВТ_НачальныеОстатки.Номенклатура КАК Номенклатура,
    &МинимальнаяДата КАК ПериодДвижения,
    ВТ_НачальныеОстатки.НачальныйОстаток КАК Количество,
    ИСТИНА КАК ЭтоНачальныйОстаток
ПОМЕСТИТЬ ВТ_ВсеДвижения
ИЗ
    ВТ_НачальныеОстатки КАК ВТ_НачальныеОстатки

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВТ_Обороты.Номенклатура,
    ВТ_Обороты.Период,
    ВТ_Обороты.Оборот,
    ЛОЖЬ
ИЗ
    ВТ_Обороты КАК ВТ_Обороты
;

////////////////////////////////////////////////////////////////////////////////
// Рассчитываем остатки в один проход СУБД с помощью ВЫБОР
ВЫБРАТЬ
    ВТ_ИсходныеДанные.Номенклатура КАК Номенклатура,
    ВТ_ИсходныеДанные.ДатаСреза КАК ДатаСреза,
    СУММА(ВЫБОР 
        КОГДА ВТ_ВсеДвижения.ЭтоНачальныйОстаток ТОГДА ВТ_ВсеДвижения.Количество
        КОГДА ВТ_ВсеДвижения.ПериодДвижения < ВТ_ИсходныеДанные.ДатаСреза ТОГДА ВТ_ВсеДвижения.Количество
        ИНАЧЕ 0 
    КОНЕЦ) КАК Остаток
ИЗ
    ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ВсеДвижения КАК ВТ_ВсеДвижения
        ПО ВТ_ИсходныеДанные.Номенклатура = ВТ_ВсеДвижения.Номенклатура
ГРУППИРОВАТЬ ПО
    ВТ_ИсходныеДанные.Номенклатура,
    ВТ_ИсходныеДанные.ДатаСреза

Преимущество метода: Такой подход работает значительно быстрее на огромных объемах данных, так как СУБД легче оптимизировать операцию суммирования с условием, чем выполнять сложные предикаты в соединениях таблиц.

---

Анализ неэффективных подходов (Почему нельзя использовать физическую таблицу движений)

Иногда начинающие разработчики пытаются решить эту задачу «в лоб»: делают левое соединение таблицы пар с физической таблицей движений регистра (или таблицей значений) и суммируют все приходы и расходы от «начала времен» до требуемой даты.

Выясним причину, почему это крайне не рекомендуется:

При обращении к реальной таблице движений СУБД вынуждена сканировать физические записи на диске. Она полностью игнорирует механизм предварительно рассчитанных итогов 1С.
Если регистр содержит миллионы записей за несколько лет, такой запрос приведет к долгому ожиданию, перегрузке дисковой подсистемы сервера и гарантированным взаимоблокировкам базы данных (Deadlocks).
Использование виртуальной таблицы Обороты всегда является приоритетным, так как платформа 1С обращается к уже рассчитанным агрегатам (итогам) по месяцам, что в сотни раз быстрее чтения «сырых» движений.

---

Сводная таблица выбора решений

Проанализируем сценарии применения рассмотренных методов, чтобы вы всегда могли выбрать оптимальный путь для вашей текущей задачи:

Сценарий задачи	Рекомендуемый метод	Сложность реализации	Производительность
Разработка отчетов любой сложности	Метод СКД (Связи наборов данных)	Низкая (настраивается в конструкторе)	Высокая (оптимизируется платформой)
Программный расчет в коде (небольшой объем данных)	Нарастающий итог через Обороты (Способ 2)	Средняя (понятная логика соединений)	Хорошая
Высоконагруженные системы, миллионы записей в РН	Модифицированный метод Ильдаровича (Способ 3)	Высокая (требует аккуратной сборки пакета)	Максимальная

Применяйте данные подходы в своей практике разработки на платформе 1С:Предприятие 8.3, избегайте запросов в циклах, берегите производительность ваших информационных баз и создавайте быстрые, надежные решения!