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

← На главную

Часто при разработке механизмов проведения документов (например, при партионном учете по методам ФИФО или ЛИФО) возникает классическая задача: как сделать так, чтобы запрос выбирал только необходимое количество строк таблицы до выполнения определенного условия? Допустим, нам необходимо получить первые N партий, чтобы их общая сумма покрывала требуемое количество для списания, но при этом запрос не выбирал бы из базы огромный массив лишних данных.

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

Решение 1. Программная обработка выборки (Классический паттерн 1С)

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

В типовых конфигурациях 1С (таких как 1С:ERP или Управление торговлей) механизмы партионного учета традиционно не используют сложные запросы для отсечения «лишних» партий. Гораздо эффективнее получить запросом остатки партий по конкретной номенклатуре и складу, отсортировать их по времени, а затем обойти результат в цикле. Запрос отрабатывает мгновенно благодаря фильтрам, а отсечение лишнего происходит в оперативной памяти.

Посмотрим на пример того, как это легко реализуется во встроенном языке с помощью переменной-счетчика:


Нужно = 100;
Есть = 0;

Для Каждого СтрокаТаблица Из Таблица Цикл
    Есть = Есть + СтрокаТаблица.Сумма;
    
    // Выполняем логику списания партии...
    
    Если Есть >= Нужно Тогда
        Прервать; // Прерываем цикл, так как набрали нужную сумму
    КонецЕсли;
КонецЦикла;

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

Решение 2. Расчет нарастающего итога в запросе (Треугольное соединение)

Если по архитектурным причинам вам все же необходимо выполнить отбор строго на уровне запроса (например, платформа ниже 8.3.20), мы можем использовать метод вычисления нарастающего итога через внутреннее соединение таблицы с самой собой. Этот метод часто называют "треугольным соединением".

Рассмотрим подробнее, как это работает. Мы соединяем виртуальную таблицу остатков с ее копией по условию неравенства номеров строк или дат. Для каждой строки первой таблицы будут присоединены все строки второй таблицы, которые были "до" нее. Затем мы группируем результат и суммируем показатели.

Посмотрим на пример запроса:


ВЫБРАТЬ 
    Остатки.НомерСтроки, 
    СУММА(Остатки1.Сумма) КАК Сумма 
ИЗ 
    Остатки КАК Остатки 
ВНУТРЕННЕЕ СОЕДИНЕНИЕ 
    Остатки КАК Остатки1 
ПО 
    Остатки.НомерСтроки >= Остатки1.НомерСтроки 
СГРУППИРОВАТЬ ПО 
    Остатки.НомерСтроки 
ИМЕЮЩИЕ 
    СУММА(Остатки1.Сумма) <= &НеобходимаяСумма

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

Решение 3. Использование оконных (аналитических) функций в 1С 8.3.20+

Если ваша база работает на платформе 1С:Предприятие версии 8.3.20 и выше, у нас есть идеальное решение, которое полностью закрывает проблему на уровне СУБД без потерь производительности. Это — оконные функции.

Теперь для получения нарастающего итога больше не нужно соединять таблицу саму с собой. Мы можем использовать конструкцию ОВЕР. Разберем синтаксис:


ВЫБРАТЬ
    Партии.Номенклатура,
    Партии.ДокументОприходования,
    Партии.Количество КАК КоличествоВПартии,
    СУММА(Партии.Количество) ОВЕР (ПАРТИЦИЯ ПО Партии.Номенклатура ПОРЯДОК ПО Партии.ДатаПартии) КАК НарастающийИтог
ПОМЕСТИТЬ ВТ_НарастающийИтог
ИЗ
    ВТ_ОстаткиПартий КАК Партии

Поместив такой запрос во временную таблицу или вложенный запрос, вы получаете колонку с уже готовым нарастающим итогом для каждой записи (в разрезе номенклатуры). Следующим запросом к временной таблице мы накладываем простое условие:


ВЫБРАТЬ
    *
ИЗ
    ВТ_НарастающийИтог КАК ВТ
ГДЕ
    ВТ.НарастающийИтог - ВТ.КоличествоВПартии < &НеобходимоеКоличество

Этот способ работает максимально быстро, обрабатывается встроенными средствами SQL-сервера и возвращает ровно те строки, которые требуются для списания.

Решение 4. Нарастающий итог в Системе Компоновки Данных (СКД)

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

В вычисляемых полях СКД можно использовать специальную функцию ВычислитьВыражение. Выясним, как ее применить для получения нарастающего итога:

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

Внимание: СКД предназначена исключительно для компоновки отчетов. Не пытайтесь использовать программный вывод СКД в модуле проведения документа для получения выборки партий — это считается плохой практикой и приведет к огромным неоправданным накладным расходам и замедлению проведения.

Краткие выводы

Если вы работаете на современной платформе (8.3.20+) и хотите решить задачу исключительно запросом — используйте конструкцию ОВЕР (оконные функции). Это самый оптимальный и красивый путь.
Если платформа старая, но нужно списывать партии — используйте стандартный подход: получайте обычные остатки с сортировкой по ДатаПартии и отсекайте лишние количества программно в цикле Для Каждого.
Избегайте треугольных соединений таблиц самих с собой для расчета нарастающего итога на больших объемах данных, чтобы не "повесить" сервер СУБД.
Для отчетов используйте функцию ВычислитьВыражение встроенного языка выражений СКД.