Каталог решений - Пропорциональное распределение в запросе

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

Итак, предположим, у нас есть таблица Долги с полями Договор и Долг. Кроме того у нас есть параметр СуммаОплаты.

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

ВЫБРАТЬ
	Долги.Договор,
	Долги.Долг,
	СуммаДолга.Сумма КАК СуммаДолга,
	КОЛИЧЕСТВО(Порядок.Долг) КАК Порядок,
	&СуммаОплаты КАК СуммаОплаты,
	ВЫРАЗИТЬ(&СуммаОплаты / СуммаДолга.Сумма * Долги.Долг КАК ЧИСЛО(15, 2)) КАК РаспределеннаяОплата
ПОМЕСТИТЬ РаспределениеОплаты
ИЗ
	Долги КАК Долги
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			СУММА(Долги.Долг) КАК Сумма
		ИЗ
			Долги КАК Долги) КАК СуммаДолга
		ПО (ИСТИНА)
		ЛЕВОЕ СОЕДИНЕНИЕ Долги КАК Порядок
		ПО (Порядок.Долг > Долги.Долг
				ИЛИ Порядок.Долг = Долги.Долг
					И Порядок.Договор.Код < Долги.Договор.Код)

СГРУППИРОВАТЬ ПО
	Долги.Договор,
	Долги.Долг,
	СуммаДолга.Сумма

Мы получим таблицу:

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

ВЫБРАТЬ
	РаспределениеОплаты.Договор,
	РаспределениеОплаты.Долг,
	РаспределениеОплаты.Порядок,
	РаспределениеОплаты.СуммаДолга,
	РаспределениеОплаты.РаспределеннаяОплата,
	СуммаРаспределения.Сумма КАК СуммаРаспределения,
	РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма КАК Отклонение,
	ВЫБОР
		КОГДА РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма < 0
				И РаспределениеОплаты.Порядок < -(РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма) * 100
			ТОГДА -0.01
		КОГДА РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма > 0
				И РаспределениеОплаты.Порядок < (РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма) * 100
			ТОГДА 0.01
		ИНАЧЕ 0
	КОНЕЦ КАК ДополнителноеРаспределение,
	ВЫБОР
		КОГДА РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма < 0
				И РаспределениеОплаты.Порядок < -(РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма) * 100
			ТОГДА -0.01
		КОГДА РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма > 0
				И РаспределениеОплаты.Порядок < (РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма) * 100
			ТОГДА 0.01
		ИНАЧЕ 0
	КОНЕЦ + РаспределениеОплаты.РаспределеннаяОплата КАК Оплата
ИЗ
	РаспределениеОплаты КАК РаспределениеОплаты
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			СУММА(РаспределениеОплаты.РаспределеннаяОплата) КАК Сумма
		ИЗ
			РаспределениеОплаты КАК РаспределениеОплаты) КАК СуммаРаспределения
		ПО (ИСТИНА)

В результате получим таблицу:

Все, оплата распределена.

Если убрать из запроса лишние поля, то итоговый результат будет выглядеть следующим образом:

ВЫБРАТЬ
	Долги.Договор,
	КОЛИЧЕСТВО(Порядок.Долг) КАК Порядок,
	&СуммаОплаты КАК СуммаОплаты,
	ВЫРАЗИТЬ(&СуммаОплаты / СуммаДолга.Сумма * Долги.Долг КАК ЧИСЛО(15, 2)) КАК РаспределеннаяОплата
ПОМЕСТИТЬ РаспределениеОплаты
ИЗ
	Долги КАК Долги
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			СУММА(Долги.Долг) КАК Сумма
		ИЗ
			Долги КАК Долги) КАК СуммаДолга
		ПО (ИСТИНА)
		ЛЕВОЕ СОЕДИНЕНИЕ Долги КАК Порядок
		ПО (Порядок.Долг > Долги.Долг
				ИЛИ Порядок.Долг = Долги.Долг
					И Порядок.Договор.Код < Долги.Договор.Код)

СГРУППИРОВАТЬ ПО
	Долги.Договор,
	Долги.Долг,
	СуммаДолга.Сумма
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
	РаспределениеОплаты.Договор,
	ВЫБОР
		КОГДА РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма < 0
				И РаспределениеОплаты.Порядок < -(РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма) * 100
			ТОГДА -0.01
		КОГДА РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма > 0
				И РаспределениеОплаты.Порядок < (РаспределениеОплаты.СуммаОплаты - СуммаРаспределения.Сумма) * 100
			ТОГДА 0.01
		ИНАЧЕ 0
	КОНЕЦ + РаспределениеОплаты.РаспределеннаяОплата КАК Оплата
ИЗ
	РаспределениеОплаты КАК РаспределениеОплаты
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			СУММА(РаспределениеОплаты.РаспределеннаяОплата) КАК Сумма
		ИЗ
			РаспределениеОплаты КАК РаспределениеОплаты) КАК СуммаРаспределения
		ПО (ИСТИНА)

Идея, думаю, понятна. Таким же образом можно перераспределить положительные и отрицательные суммы между договорами и так далее. Как ни странно, в Интернете я похожего решения не нашел, везде пишут «запросом невозможно, делайте в цикле». Поэтому описал максимально подробно смысл каждого действия. Пользуйтесь на здоровье.