Нам часто требуется решить задачу распределения некоторой общей суммы по списку заказов или другим документам в определенной последовательности, до тех пор, пока общая сумма не будет полностью исчерпана. Например, у нас есть сумма к распределению в 4000 единиц, и мы хотим распределить ее по заказам, имеющим следующие суммы: Заказ 1 (2000), Заказ 2 (3000), Заказ 3 (500). Ожидаемый результат должен выглядеть так: Заказ 1 получает 2000, Заказ 2 получает оставшиеся 2000, а Заказ 3 — ничего. Заказы, на которые ничего не распределено, желательно не выводить.
Эта задача не так проста, как кажется на первый взгляд, поскольку требует не просто подсчета накопительного итога, а учета "остатка" от распределяемой суммы на каждом шаге. Давайте разберем, как реализовать подобную логику средствами языка запросов 1С, используя нарастающий итог и временные таблицы.
Прежде чем перейти к распределению суммы, нам необходимо понять, как получить нарастающий итог (или накопительную сумму) в запросе 1С. В стандартном SQL для этого используются оконные функции, такие как SUM() OVER (ORDER BY...), но в языке запросов 1С их напрямую нет. Вместо этого мы эмулируем их с помощью самосоединения таблицы.
Определение порядка сортировки: Ключевым моментом для нарастающего итога является четкий и однозначный порядок следования записей. Это может быть ссылка на документ, дата, номер или специальное поле-счетчик (например, АВТОНОМЕРЗАПИСИ).
Самосоединение (LEFT JOIN или INNER JOIN): Мы соединяем таблицу саму с собой по условию, которое обеспечивает накопительный эффект. Обычно это делается так: Таблица1.ПолеСортировки >= Таблица2.ПолеСортировки. Это означает, что для каждой записи из Таблица1 будут найдены все записи из Таблица2, которые предшествуют ей или совпадают с ней по порядку сортировки.
Группировка и суммирование: После самосоединения мы группируем результат по полям первой таблицы и суммируем нужное поле из второй таблицы. Таким образом, для каждой записи первой таблицы мы получаем сумму всех соответствующих записей из второй таблицы, которые удовлетворяют условию соединения.
Рассмотрим первоначальную попытку автора, которая демонстрирует проблему: при прямом применении нарастающего итога без учета общей суммы для распределения мы получим неверный результат.
ВЫБРАТЬ
ЗаказКлиента.Ссылка КАК Ссылка,
ЗаказКлиента.СуммаДокумента КАК СуммаДокумента,
ЗаказКлиента.СуммаДокумента КАК СуммаСписания
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
Документ.ЗаказКлиента КАК ЗаказКлиента
ГДЕ
ЗаказКлиента.Партнер = &Партнер
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВременнаяТаблица.Ссылка КАК Ссылка,
ВременнаяТаблица.СуммаДокумента КАК СуммаДокумента,
СУММА(ТаЖеВременнаяТаблица.СуммаСписания) КАК СуммаСписания // Здесь будет нарастающий итог по СуммаДокумента
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
ЛЕВОЕ СОЕДИНЕНИЕ ВременнаяТаблица КАК ТаЖеВременнаяТаблица
ПО (ВременнаяТаблица.Ссылка >= ТаЖеВременнаяТаблица.Ссылка)
СГРУППИРОВАТЬ ПО
ВременнаяТаблица.Ссылка,
ВременнаяТаблица.СуммаДокумента
В этом запросе СУММА(ТаЖеВременнаяТаблица.СуммаСписания) действительно вычислит нарастающий итог по суммам документов, но это не то, что нам нужно для распределения фиксированной суммы. Если нам нужно распределить 4000, а Заказ 1 имеет сумму 2000, Заказ 2 — 3000, Заказ 3 — 500, то этот запрос даст:
Нам же нужен не накопительный итог по суммам заказов, а распределенная часть от заданной суммы, которая уменьшается с каждым заказом. То есть, мы должны вычислять, сколько "осталось" от нашей общей суммы, и сколько из этого остатка может принять текущий заказ. Это требует более сложной логики ВЫБОР КОГДА.
Один из участников предложил адаптировать существующий пример с форума (сообщение 2), который использует понятия НачальныйОстаток (НО) и КонечныйОстаток (КО). Хотя этот пример изначально был для другой задачи, его логика может быть переработана для нашей цели. Давайте проанализируем его и поймем, как он работает.
ВЫБРАТЬ
ДоговорРеализации.Договор КАК Договор,
ДоговорРеализации.Приход КАК Приход,
СУММА(ДоговорРеализацияКО.Приход) - ДоговорРеализации.Приход КАК НО, // НачальныйОстаток
СУММА(ДоговорРеализацияКО.Приход) КАК КО // КонечныйОстаток (нарастающий итог)
ПОМЕСТИТЬ ДоговорРеализацииОбратныйНарастИтог
ИЗ
ДоговорРеализации КАК ДоговорРеализации
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ДоговорРеализации КАК ДоговорРеализацияКО
ПО ДоговорРеализации.Договор = ДоговорРеализацияКО.Договор
И ДоговорРеализации.СрокОплаты = ДоговорРеализацииОбратныйНарастИтог.КО ТОГДА ДоговорРеализацииОбратныйНарастИтог.Приход КОГДА &Сумма + ДоговорРеализацииОбратныйНарастИтог.Приход >= ДоговорРеализацииОбратныйНарастИтог.КО ТОГДА ДоговорРеализацииОбратныйНарастИтог.Приход - ДоговорРеализацииОбратныйНарастИтог.КО + &Сумма ИНАЧЕ 0 КОНЕЦ КАК Сумма
ИЗ
ДоговорРеализацииОбратныйНарастИтог КАК ДоговорРеализацииОбратныйНарастИтог
В этом фрагменте кода наблюдаются синтаксические ошибки (после `И ДоговорРеализации.СрокОплаты =` ожидается значение или выражение, а не `ДоговорРеализацииОбратныйНарастИтог.КО ТОГДА...`). Вероятно, это вырванный кусок или опечатка. Однако, сам принцип, который подразумевает автор сообщения, заключается в следующем:
Мы вычисляем КонечныйОстаток (КО) как нарастающий итог по некоторому полю (`Приход` в примере), который по сути является накопительной суммой всех предыдущих записей (включая текущую), упорядоченных по полю `СрокОплаты`.
НачальныйОстаток (НО) вычисляется как КО - Приход, что дает нам нарастающий итог всех предыдущих записей, но без учета текущей.
Далее, вложенный ВЫБОР КОГДА блок используется для определения, сколько из общей суммы `&Сумма` приходится на текущую запись. Логика примерно такая:
КО (нарастающий итог до текущей включительно), то возможно, текущая запись полностью оплачивается, или ей отдается часть суммы.Для того чтобы этот пример заработал, необходимо было бы правильно выстроить самосоединение и адаптировать логику ВЫБОР КОГДА под задачу распределения, а не под "не оплачено". Поскольку это лишь шаблон, и его адаптация может быть сложной из-за синтаксических неточностей, давайте рассмотрим более полный и проверенный подход.
Для решения нашей задачи мы воспользуемся методом, который подробно описан в сообщении 15 темы. Этот метод включает создание нескольких временных таблиц для последовательного вычисления нарастающего итога и последующего распределения.
Представим, что у нас есть список заказов и общая сумма, которую нужно распределить. Например, как в сообщении 15:
Создаем временную таблицу с исходными заказами (мЗаказ). Здесь мы просто определяем наши исходные данные: Заказ и его Сумма.
ВЫБРАТЬ
"З_1" КАК Заказ,
1000 КАК Сумма
ПОМЕСТИТЬ мЗаказ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"З_2",
2000
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"З_3",
500
;
Создаем временную таблицу с общей суммой к распределению (мСумма). Это наша константа, которую мы будем распределять.
ВЫБРАТЬ
1575 КАК СуммаРаспределения
ПОМЕСТИТЬ мСумма
;
Добавляем порядковый номер для каждого заказа (мТАбНомер). Это критически важный шаг. АВТОНОМЕРЗАПИСИ гарантирует уникальный и последовательный номер для каждой строки, что позволяет нам однозначно упорядочить заказы. Без этого нарастающий итог может быть непредсказуемым.
ВЫБРАТЬ
мЗаказ.Заказ,
мЗаказ.Сумма,
АВТОНОМЕРЗАПИСИ КАК НН // Порядковый номер
ПОМЕСТИТЬ мТАбНомер
ИЗ
мЗаказ КАК мЗаказ
;
Подготавливаем таблицу для нарастающего итога (мТабНарастающий). Здесь мы выполняем самосоединение таблицы мТАбНомер саму с собой. Условие мТАбНомер.НН >= мТАбНомер1.НН позволяет для каждой записи из левой части получить все записи из правой части, включая саму себя, которые имеют меньший или равный порядковый номер.
ВЫБРАТЬ
мТАбНомер.Заказ,
мТАбНомер.НН,
мТАбНомер1.НН КАК НН1 // Порядковый номер для соединения
ПОМЕСТИТЬ мТабНарастающий
ИЗ
мТАбНомер КАК мТАбНомер
ВНУТРЕННЕЕ СОЕДИНЕНИЕ мТАбНомер КАК мТАбНомер1
ПО мТАбНомер.НН >= мТАбНомер1.НН // Условие для нарастающего итога
;
Эта временная таблица по сути является промежуточным шагом, который позволяет нам собрать все "предыдущие" записи для каждой "текущей" записи.
Вычисляем нарастающий итог по суммам заказов (мТабНарастающийСумма). Используя мТабНарастающий, мы теперь можем сгруппировать данные по Заказ и просуммировать Сумма для всех связанных записей.
ВЫБРАТЬ
мТабНарастающий.Заказ КАК Заказ,
СУММА(мТАбНомер.Сумма) КАК Сумма // Нарастающий итог по суммам заказов, включая текущий
ПОМЕСТИТЬ мТабНарастающийСумма
ИЗ
мТабНарастающий КАК мТабНарастающий
ВНУТРЕННЕЕ СОЕДИНЕНИЕ мТАбНомер КАК мТАбНомер
ПО мТабНарастающий.НН1 = мТАбНомер.НН
СГРУППИРОВАТЬ ПО
мТабНарастающий.Заказ,
мТабНарастающий.НН // Добавляем НН в группировку для получения нарастающего итога по каждому заказу
УПОРЯДОЧИТЬ ПО
мТабНарастающий.НН // Важно для корректного порядка, хотя в группировке уже есть
;
Здесь мТабНарастающийСумма.Сумма будет представлять собой нарастающий итог сумм заказов до текущего включительно. Например:
Финальная выборка и расчет суммы распределения на каждый заказ. Теперь, когда у нас есть нарастающий итог, мы можем вычислить, сколько денег реально приходится на каждый заказ из нашей общей суммы &СуммаРаспределения.
Для этого нам понадобится следующая логика:
мТАбНомер.Сумма.мСумма.СуммаРаспределения.мТабНарастающийСумма.Сумма - мТАбНомер.Сумма.Используем выражение ВЫБОР КОГДА для определения, сколько денег будет распределено на текущий заказ:
ВЫБРАТЬ
мТАбНомер.Заказ КАК Заказ,
мТАбНомер.Сумма КАК СуммаЗаказа,
мСумма.СуммаРаспределения КАК ОбщаяСуммаКРаспределению,
мТабНарастающийСумма.Сумма - мТАбНомер.Сумма КАК СуммаДоПредыдущихЗаказов,
ВЫБОР
// Условие 1: Если общая сумма к распределению уже исчерпана предыдущими заказами
КОГДА мСумма.СуммаРаспределения - (мТабНарастающийСумма.Сумма - мТАбНомер.Сумма) <= 0
ТОГДА 0 // На текущий заказ ничего не распределяем
// Условие 2: Если общая сумма к распределению полностью покрывает текущий заказ и предыдущие
КОГДА мСумма.СуммаРаспределения - мТабНарастающийСумма.Сумма >= 0
ТОГДА мТАбНомер.Сумма // Распределяем полную сумму текущего заказа
// Условие 3: Если общая сумма к распределению частично покрывает текущий заказ
ИНАЧЕ мСумма.СуммаРаспределения - (мТабНарастающийСумма.Сумма - мТАбНомер.Сумма) // Распределяем оставшуюся часть суммы
КОНЕЦ КАК СуммаРаспределенияНаЗаказ
ИЗ
мТабНарастающийСумма КАК мТабНарастающийСумма
ВНУТРЕННЕЕ СОЕДИНЕНИЕ мСумма КАК мСумма
ПО (ИСТИНА) // Соединяем с таблицей общей суммы без условия, так как она одна
ВНУТРЕННЕЕ СОЕДИНЕНИЕ мТАбНомер КАК мТАбНомер
ПО мТабНарастающийСумма.Заказ = мТАбНомер.Заказ
ГДЕ
ВЫБОР
КОГДА мСумма.СуммаРаспределения - (мТабНарастающийСумма.Сумма - мТАбНомер.Сумма) <= 0
ТОГДА 0
КОГДА мСумма.СуммаРаспределения - мТабНарастающийСумма.Сумма >= 0
ТОГДА мТАбНомер.Сумма
ИНАЧЕ мСумма.СуммаРаспределения - (мТабНарастающийСумма.Сумма - мТАбНомер.Сумма)
КОНЕЦ > 0 // Отфильтровываем строки, на которые ничего не распределено (СуммаРаспределенияНаЗаказ = 0)
УПОРЯДОЧИТЬ ПО
мТАбНомер.НН // Важно для сохранения исходного порядка
Разберем логику поля СуммаРаспределенияНаЗаказ:
мСумма.СуммаРаспределения - (мТабНарастающийСумма.Сумма - мТАбНомер.Сумма) — это по сути "сколько осталось от общей суммы к распределению после учета всех предыдущих заказов". Если это значение меньше или равно 0, значит, общая сумма уже полностью распределена или исчерпана предыдущими заказами, и на текущий заказ ничего не достается.
мСумма.СуммаРаспределения - мТабНарастающийСумма.Сумма — это "сколько осталось от общей суммы к распределению после учета всех заказов включительно по текущий". Если это значение больше или равно 0, это означает, что наша общая сумма покрывает текущий заказ полностью (и, возможно, еще остается). В этом случае на текущий заказ мы распределяем его полную сумму (мТАбНомер.Сумма).
ИНАЧЕ: Если ни одно из предыдущих условий не сработало, это означает, что общая сумма исчерпывается где-то внутри текущего заказа. В этом случае на текущий заказ мы распределяем именно тот "остаток", который остался после предыдущих заказов: мСумма.СуммаРаспределения - (мТабНарастающийСумма.Сумма - мТАбНомер.Сумма).
Применение ГДЕ СуммаРаспределенияНаЗаказ > 0 в конце запроса позволяет нам отфильтровать заказы, которые не получили ни одной единицы распределяемой суммы, что соответствует исходному требованию автора.
Порядок сортировки: Всегда убеждайтесь, что у вас есть однозначное поле или комбинация полей для сортировки, которое вы используете в условии самосоединения для нарастающего итога (например, НН, ДатаДокумента + СсылкаДокумента). Без этого результаты будут непредсказуемыми.
Производительность: Запросы с множественными самосоединениями и временными таблицами могут быть ресурсоемкими на больших объемах данных. Если производительность становится проблемой, рассмотрите возможность выполнения этой логики на стороне сервера в программном коде 1С (например, с помощью обхода выборки). Как упомянуто в обсуждении (сообщения 13, 14), иногда программное распределение может быть значительно быстрее, особенно если бизнес-логика сложна.
Тестирование: Всегда тщательно тестируйте такие запросы на различных наборах данных, включая граничные случаи (распределяемая сумма равна нулю, распределяемая сумма меньше первого заказа, распределяемая сумма равна точному итогу нескольких заказов и т.д.).
Виртуальные таблицы регистров: При работе с реальными данными в 1С вместо "ручных" таблиц можно использовать виртуальные таблицы регистров (например, остатков или оборотов), которые уже предоставляют необходимые данные.
Решение этой задачи в запросе 1С требует понимания принципов работы нарастающего итога и умения применять сложную логику ВЫБОР КОГДА для управления распределением. Представленный пошаговый метод позволяет добиться нужного результата, сохраняя всю логику в рамках одного запроса. При необходимости можно использовать готовую модель распределения суммы по базам и порядку.