Как в запросе 1С выбрать данные из регистра на определенную сумму (LIFO и нарастающий итог)?

← На главную

Нередко перед разработчиком возникает задача отобрать документы или записи регистра на определенную общую сумму. Типичный сценарий — определение неоплаченных накладных: мы берем остаток долга контрагента на конец периода и должны выбрать последние продажи в обратном хронологическом порядке (по принципу LIFO), пока накопленная сумма отгрузок не покроет сумму задолженности. Разберем, как реализовать подобный алгоритм средствами языка запросов 1С, а также рассмотрим альтернативные и более производительные варианты решения задачи в СКД и коде.

Принцип реализации выборки по сумме через нарастающий итог в запросе

В языке запросов 1С отсутствуют стандартные оконные функции СУБД вроде SUM(...) OVER (ORDER BY ...), поэтому расчет нарастающего итога строится на базе неэквивалентного соединения таблицы самой с собой. Рассмотрим пошаговый алгоритм решения задачи:

Подготовка исходных данных и ключа упорядочивания: выгружаем строки регистра во временную таблицу. Так как в пределах одной секунды может быть проведено несколько документов, одного поля Период для однозначной сортировки недостаточно. Необходимо использовать связку периода и ссылки на документ-регистратор (или МоментВремени).
Неэквивалентное соединение таблицы с самой собой: соединяем таблицу саму с собой по ключевым аналитикам (например, Контрагент, Договор) и условию нестрогого неравенства по времени (Таблица1.МоментВремени <= Таблица2.МоментВремени для обратного порядка LIFO или >= для прямого порядка FIFO).
Группировка и получение нарастающей суммы: группируем записи по полям первой таблицы, вычисляя сумму по строкам второй таблицы — эта агрегированная сумма и будет нарастающим итогом.
Ограничение по сумме и расчет остатка: накладываем условие отбора на нарастающий итог и рассчитываем сумму строки через ВЫБОР КОГДА, чтобы учесть граничную накладную, которая закрывается долгом лишь частично.

Пример построения запроса с расчетом LIFO

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


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Т1.Регистратор КАК Регистратор,
    Т1.Период КАК Период,
    Т1.Номенклатура КАК Номенклатура,
    Т1.Сумма КАК СуммаСтроки,
    СУММА(Т2.Сумма) КАК НарастающийИтог,
    СУММА(Т2.Сумма) - Т1.Сумма КАК ПредыдущийИтог
ПОМЕСТИТЬ ВТ_НарастающиеИтоги
ИЗ
    ВТ_Продажи КАК Т1
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Продажи КАК Т2
        ПО (Т1.Период <= Т2.Период)
        И (Т1.Регистратор.МоментВремени <= Т2.Регистратор.МоментВремени)

СГРУППИРОВАТЬ ПО
    Т1.Регистратор,
    Т1.Период,
    Т1.Номенклатура,
    Т1.Сумма
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ИТОГИ.Регистратор КАК Регистратор,
    ИТОГИ.Период КАК Период,
    ИТОГИ.Номенклатура КАК Номенклатура,
    ВЫБОР
        КОГДА ИТОГИ.НарастающийИтог <= &СуммаДолга
            ТОГДА ИТОГИ.СуммаСтроки
        ИНАЧЕ &СуммаДолга - ИТОГИ.ПредыдущийИтог
    КОНЕЦ КАК СуммаПокрытия
ИЗ
    ВТ_НарастающиеИтоги КАК ИТОГИ
ГДЕ
    ИТОГИ.ПредыдущийИтог < &СуммаДолга
УПОРЯДОЧИТЬ ПО
    ИТОГИ.Период УБЫВ

Разберем логику последнего блока запроса: условие ГДЕ ИТОГИ.ПредыдущийИтог < &СуммаДолга отсекает все накладные, которые идут после того, как требуемая сумма уже полностью набрана. В поле СуммаПокрытия с помощью конструкции ВЫБОР КОГДА выделяется полная сумма строки, если она целиком уложилась в лимит, либо разница между лимитом и суммой предыдущих строк для граничного документа.

Ограничения производительности метода неэквисоединения

Обратим внимание на критический нюанс: неэквивалентное соединение таблицы с самой собой порождает квадратичную сложность O(N²). Если выборка содержит сотни или тысячи документов, такой запрос приведет к огромному промежуточному декартову произведению, резкому росту базы tempdb на SQL-сервере и существенным задержкам.

Поэтому в реальных высоконагруженных задачах рекомендуется применять альтернативные методы.

Альтернатива 1: Использование выражений в СКД

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

Для этого в схеме компоновки данных используется функция ВычислитьСумму. В выражении поля на вкладке «Ресурсы» или «Вычисляемые поля» указывается вызов функции с контекстом текущей строки:

ВычислитьСумму("СуммаСтроки", "ГруппировкаКонтрагент", "Текущая")

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

Альтернатива 2: Обработка результата запроса в коде

Когда требуется максимальное быстродействие на больших объемах, наиболее эффективным решением остается последовательный обход выборки на сервере с линейной сложностью O(N):

Выполняем простой запрос к оборотам или остаткам с сортировкой УПОРЯДОЧИТЬ ПО Период УБЫВ.
Создаем результирующую таблицу значений.
В цикле Пока Выборка.Следующий() вычитаем сумму текущей строки из оставшегося лимита долга.
Если текущая строка превышает остаток лимита — фиксируем частичную сумму и прерываем цикл (Прервать).
Передаем полученную таблицу в СКД через объект ВнешнийНаборДанных в событии ПриКомпоновкеРезультата.


ОстатокСуммы = СуммаДолга;
Выборка = Запрос.Выполнить().Выбрать();

Пока Выборка.Следующий() И ОстатокСуммы > 0 Цикл
    НоваяСтрока = ТаблицаРезультат.Добавить();
    ЗаполнитьЗначенияСвойств(НоваяСтрока, Выборка);
    
    Если Выборка.Сумма <= ОстатокСуммы Тогда
        НоваяСтрока.Сумма = Выборка.Сумма;
        ОстатокСуммы = ОстатокСуммы - Выборка.Сумма;
    Иначе
        НоваяСтрока.Сумма = ОстатокСуммы;
        ОстатокСуммы = 0;
    КонецЕсли;
КонецЦикла;

Тонкости предметного учета взаиморасчетов

При реализации закрытия по LIFO/FIFO через оборотные регистры важно помнить об особенностях учета:

Возвраты и корректировки: простое списание по оборотам продаж не учитывает возвраты товаров от покупателей, взаимозачеты и корректировки долга. Если в конфигурации ведется детализированный учет взаиморасчетов «По документам расчетов», более точным и быстрым подходом будет прямое обращение к регистру расчетов с отбором остатков в разрезе документов.