В практике разработчика 1С часто возникают ситуации, когда необходимо выполнить сложные математические расчеты, такие как распределение общего остатка по строкам заказа или расчет нарастающего итога, непосредственно в тексте запроса. Это особенно актуально при создании произвольных отчетов, где у программиста нет возможности вмешаться в процесс формирования таблицы программным кодом на встроенном языке. Разберем подробно, как можно решить задачу, когда сумма заказанных позиций в строках превышает фактический остаток, и нам нужно ограничить значения в последних строках так, чтобы общая сумма не превышала лимит.
Рассмотрим типичную ситуацию: имеется документ с несколькими строками заказа и общий остаток товара на складе. Нам нужно в каждой строке вывести количество, которое мы реально можем отгрузить. Если остаток на складе меньше, чем общая сумма по строкам, то в первых строках мы выводим полное количество, а в той строке, где остаток заканчивается — лишь его часть. Все последующие строки должны получить нулевое значение. Сложность заключается в том, что запрос 1С по своей природе работает с наборами данных одновременно, а не последовательно, поэтому "знание" о том, сколько остатка уже "потрачено" на предыдущие строки, получить не так просто.
Проанализируем самый универсальный способ, который работает на любых версиях платформы 8.3, даже там, где не поддерживаются оконные функции. Основная идея заключается в том, чтобы для каждой строки нашей таблицы найти все "предыдущие" строки и просуммировать их значения.
Шаг 1: Подготовка данных во временной таблице
Для корректного определения "предыдущих" строк нам необходим уникальный ключ, который задает строгий порядок. Это может быть МоментВремени, Дата + Ссылка или просто НомерСтроки. Крайне важно обеспечить детерминированность: если у двух строк будут одинаковые критерии сортировки, СУБД может соединить их произвольным образом, что приведет к ошибкам в расчетах.
// Поместим исходные данные во временную таблицу с порядковым номером
ВЫБРАТЬ
Заказы.Ссылка КАК Документ,
Заказы.Количество КАК Заказано,
Остатки.КоличествоОстаток КАК ОбщийОстаток,
АВТОНОМЕРЗАПИСИ() КАК ПорядковыйНомер
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
Документ.ЗаказПокупателя.Товары КАК Заказы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
ПО Заказы.Номенклатура = Остатки.Номенклатура
ГДЕ
Заказы.Ссылка = &Ссылка;
Шаг 2: Расчет накопленной суммы
Теперь мы присоединим таблицу ВТ_ИсходныеДанные к самой себе по условию, что номер строки во второй таблице меньше или равен номеру строки в первой. Это позволит нам для каждой текущей строки получить сумму всех предшествующих.
ВЫБРАТЬ
Т1.ПорядковыйНомер,
Т1.Заказано КАК ТекущееЗаказано,
Т1.ОбщийОстаток,
СУММА(Т2.Заказано) КАК НакопленныйИтогВключаяТекущую,
СУММА(Т2.Заказано) - Т1.Заказано КАК НакопленныйИтогПредыдущих
ПОМЕСТИТЬ ВТ_СИтогами
ИЗ
ВТ_ИсходныеДанные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК Т2
ПО Т2.ПорядковыйНомер <= Т1.ПорядковыйНомер
ГРУППИРОВАТЬ ПО
Т1.ПорядковыйНомер,
Т1.Заказано,
Т1.ОбщийОстаток;
Шаг 3: Финальный расчет распределения
Выясним причину, по которой мы разделяем расчет на несколько этапов. Нам нужно вычислить количество к выдаче, используя функцию ВЫБОР. Логика проста: если ОбщийОстаток больше, чем НакопленныйИтогПредыдущих, значит, для текущей строки еще что-то осталось. Мы берем минимум из того, что заказано в этой строке, и того, что осталось от общего остатка после вычета накопленного итога предыдущих строк.
ВЫБРАТЬ
ПорядковыйНомер,
ТекущееЗаказано,
ВЫБОР
КОГДА ОбщийОстаток - НакопленныйИтогПредыдущих > 0
ТОГДА ВЫБОР
КОГДА ТекущееЗаказано < ОбщийОстаток - НакопленныйИтогПредыдущих
ТОГДА ТекущееЗаказано
ИНАЧЕ ОбщийОстаток - НакопленныйИтогПредыдущих
КОНЕЦ
ИНАЧЕ 0
КОНЕЦ КАК КоличествоКВыдаче
ИЗ
ВТ_СИтогами;
Рассмотрим более современный и производительный способ. Если ваша база работает на платформе версии 8.3.20 или новее, мы можем избежать тяжелого соединения таблиц "каждый с каждым", которое при 1000 строк превращается в 1 000 000 записей для анализа СУБД. Оконные функции позволяют вычислить сумму "над" выборкой в один проход.
Посмотрим на пример синтаксиса:
ВЫБРАТЬ
Заказы.Ссылка,
Заказы.Количество КАК Заказано,
СУММА(Заказы.Количество) НАД (УПОРЯДОЧИТЬ ПО Заказы.Ссылка.МоментВремени, Заказы.НомерСтроки) КАК НакопленныйИтог
ИЗ
Документ.ЗаказПокупателя.Товары КАК Заказы
Проанализируем ситуацию: использование конструкции НАД (УПОРЯДОЧИТЬ ПО...) заменяет собой целую временную таблицу с группировками. Дальнейшая логика с использованием ВЫБОР остается такой же, как в первом методе, но запрос работает в десятки раз быстрее на больших объемах данных.
Если задача стоит именно в контексте "произвольного отчета", который строится на Системе Компоновки Данных, мы можем перенести математику из текста запроса в настройки ресурсов или вычисляемых полей. Это наиболее гибкий путь для пользователя.
Разберем по шагам настройку в СКД:
НарастающийИтог.ВычислитьВыражение("Сумма(КоличествоЗаказано)", , , "Первая", "Текущая"). Эта функция виртуально проходит по строкам отчета и суммирует значения.РаспределенныйОстаток, где пропишем логику ограничения: Выбор Когда ОбщийОстаток - (НарастающийИтог - КоличествоЗаказано) > 0 Тогда ... Конец.Важный момент: СКД выполняет эти расчеты на этапе компоновки результата, что позволяет пользователю менять группировки и сортировки в пользовательском режиме, а расчет итогов будет автоматически подстраиваться под новый порядок строк.
Проанализируем важные технические нюансы, о которых нельзя забывать при использовании "чистых запросов":
ВТ_ИсходныеДанные) обязательно индексируйте поле, по которому происходит соединение (например, ПорядковыйНомер). Это критически важно для скорости выполнения операции ЛЕВОЕ СОЕДИНЕНИЕ.Ссылка. В 1С несколько документов могут иметь идентичное время до секунды. Без учета ссылки порядок строк (и, соответственно, результат суммы) может меняться при каждом запуске отчета.Таким образом, мы выяснили, что задача распределения остатков в запросе решаема несколькими способами. Выбор конкретного метода зависит от версии платформы, объема данных и того, насколько отчет должен быть "произвольным" для конечного пользователя. Совместное использование временных таблиц и математических условий в ВЫБОР позволяет добиться нужного результата без единой строчки программного кода в модуле.