gifts2017

Пропорциональное распределение в запросе

Опубликовал Дмитрий Горелов (PythonJ) в раздел Программирование - Практика программирования

Решение задачи распределения суммы пропорционально каким-либо значениям (например, оплату по договорам) с помощью одного запроса таким образом, чтобы при округлении не получалось суммарного отклонения.

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

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

Первым действием нам необходимо в поле РаспределеннаяОплата распределить сумму оплаты по договорам в соответствии с коэффициентом Долг/СуммаДолга с округлением до 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
	КОНЕЦ + РаспределениеОплаты.РаспределеннаяОплата КАК Оплата
ИЗ
	РаспределениеОплаты КАК РаспределениеОплаты
		ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
			СУММА(РаспределениеОплаты.РаспределеннаяОплата) КАК Сумма
		ИЗ
			РаспределениеОплаты КАК РаспределениеОплаты) КАК СуммаРаспределения
		ПО (ИСТИНА)

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

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Kost Aridov (kvikster) 06.08.15 17:40
а если нужно распределить сумму 10 на 3 одинаковых документа с одинаковым долгом 3 ?
2. Дмитрий Горелов (PythonJ) 06.08.15 21:02
(1) kvikster,
Как понять "Распределить на три одинаковых документа"? Если документы (или договоры) с одинаковой суммой, но разными кодами, то излишек распределится в порядке очередности кода на сколько хватит. А если это одинаковые документы, то в чем тогда смысл распределения? Исходная таблица по определению не должна содержать одинаковые значения документа. Если они одинаковые, то их нужно сгруппировать до распределения. В конечном итоге предполагается получить движения, где документ - измерение, а распределенная сумма - ресурс. Какой смысл в движениях с разными ресурсами, но одинаковыми измерениями?
Поясните или приведите пример задачи, если я неправильно понял.
3. Сергей (ildarovich) 06.08.15 23:35
Для полноты картины не хватает ссылки на основательную статью по этой теме : "Честное распределение суммы по таблице значений". Безотносительно техники решения в указанной статье сравниваются разные принципы отнесения ошибок округления на конкретные строки базы распределения. Из статьи, кажется, следует, что упорядочивание по величине долга - не лучший метод. Мне кажется, что лучше упорядочивать по величине ошибки округления. Тогда будет работать критерий минимакса ошибки представления.
Похожая задача вроде бы возникает при масштабировании областей в растровой графике. Когда требуется увеличить площадь под линией на заданную величину, сохранив ее форму. Используемый здесь метод будет всегда поправлять верхнюю часть ограничительной линии, внося заметные на взгляд искажения.
Но по сравнению с отнесением всей невязки на одну строку (что приводит к еще более простому запросу) этот запрос получше.
AlexiyI; PythonJ; +2 Ответить 1
4. Dimel 07.08.15 03:01
Как ни странно, в Интернете я похожего решения не нашел, везде пишут "запросом невозможно, делайте в цикле".
- плохо искал:

ВЫБРАТЬ
	"А" КАК Объект,
	1 КАК База
ПОМЕСТИТЬ Данные

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	"Б",
	1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	"В",
	1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	"Г",
	1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	"Д",
	1

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	"Е",
	1
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	СУММА(Данные.База) КАК СуммаБазы
ПОМЕСТИТЬ Итог
ИЗ
	Данные КАК Данные
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	Данные1.Объект КАК Объект,
	ВЫРАЗИТЬ(Данные1.База КАК ЧИСЛО(15, 2)) КАК База,
	ВЫРАЗИТЬ(ЕСТЬNULL(СУММА(Данные2.База), 0) КАК ЧИСЛО(15, 2)) КАК БазаНакопленная
ПОМЕСТИТЬ ДанныеСНакоплением
ИЗ
	Данные КАК Данные1
		ЛЕВОЕ СОЕДИНЕНИЕ Данные КАК Данные2
		ПО Данные1.Объект > Данные2.Объект

СГРУППИРОВАТЬ ПО
	Данные1.Объект,
	Данные1.База
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
	Данные.Объект,
	Данные.База,
	(ВЫРАЗИТЬ(&Сумма * (Данные.БазаНакопленная + Данные.База) / Итог.СуммаБазы КАК ЧИСЛО(15, 2))) - (ВЫРАЗИТЬ(&Сумма * Данные.БазаНакопленная / Итог.СуммаБазы КАК ЧИСЛО(15, 2))) КАК РаспределеноПоБазе
ИЗ
	Итог КАК Итог,
	ДанныеСНакоплением КАК Данные
...Показать Скрыть
Жолтокнижниг; VVi3ard; lx@; slavap; PythonJ; Alien_job; +6 Ответить 2
5. Дмитрий Горелов (PythonJ) 07.08.15 06:52
(4) Dimel, Действително плохо искал. Такое простое и изящное решение, мог бы и сам додуматься. Можно ссылку, откуда это? Здесь на Инфостарте есть? Хотелось бы добавить в статью, но без ссылки на источник некрасиво будет.
6. Дмитрий Горелов (PythonJ) 07.08.15 07:14
(3) ildarovich, Отличная статья. По хорошему, нужно не ссылку сюда добавлять, а вообще статью с публикации снять. Как считаете, имеет смысл реализовать пакетными запросами все описанные там варианты, а не только вариант 4, и сравнить производительность на тестовой базе? Вариант с распределением по ошибке округления точнее, но приведет к лишнему этажу в пакетном запросе, что скажется на скорости. Я так понимаю, что 3 вариант реализован в комментарии (4) Dimel
7. Игорь Фелькер (Brawler) 07.08.15 08:23
(6) PythonJ, иной раз изящность решения задачи (ну скажем выпендрился и написал распределение в запросе) нафиг не нужно из-за своей сложности восприятия через некоторое время.
Я сторонник не пихать все и вся в запросы без необходимости. Да и на практиче возникают ситуации и у меня были, когда нужно распределить 4 числа (БУ НУ ПР ВР) параллельно по 4-м базам (БУ НУ ПР ВР) и потом еще вывести погрешности на "чистую воду" и все проровнять (решалась задача постатейного закрытия 20, 23 счетов в БП 2.0, где субконто СтатьяЗатрат не оборотное, а потом все переделывалось под БП 3.0).

К слову в БП 2.0/3.0 стандартные алгоритмы некорректно распределяют амортизацию, когда она распределяется по способам отражения, где очень много прописано разных дробных коэффициентов. Ошибка выражается в том, что правило БУ = НУ + ПР + ВР не выполняется, а должно. Пришлось написать обработку выравнивающую положение дел уже после расчета и распределения амортизации.
Увы, но переписка с 1С ни к чему не привела, они тупо отписались, что не смогли воспроизвести ошибку... хотя все предоставлял им...
8. Алексей 1 (AlX0id) 07.08.15 08:46
(6) PythonJ,
Зачем снимать? ) Пусть будет бесплатный вариант для тех, кто хочет не особо разбираться в теории, а взять чистый практический пример.
9. Дмитрий Горелов (PythonJ) 07.08.15 09:15
(7) Brawler, Все зависит от задачи. Пихать в запросы все подряд смысла нет, когда объемы обрабатываемых данных относительно невелики. Или когда задача разовая. У меня речь идет об огромных массивах, обрабатываемых ежедневно. Речь идет о конфигурации для ЖКХ и распределении оплаты коммунальных услуг по статьям по каждому лицевому счету. "Не смогли воспроизвести ошибку" - обычное дело :)
10. Dimel 07.08.15 10:54
11. Виталий Васильев (orfos) 07.08.15 23:43
Спасибо! Аналогичную задачу решал на днях (распределение 20-х счетов). Запросом круче.
12. Юрий Артемьев (urrymca) 12.08.15 08:24
За вот - такие решения в рабочих конфигураций хочется бить по башке и крайне сильно.

Желаю вам приятного геммороя при раскапывании проблем, возникающих при вкрячивании таких запросов например в проведение документов (реверанс в сторону 1С).
13. Дмитрий Горелов (PythonJ) 12.08.15 11:45
(12) urrymca,
За подобные запросы в проведении документов действительно надо бить по голове. Все суммы должны быть рассчитаны до проведения и сохранены в табличной части документа. Динамический расчет при проведении запросом или кодом породит серьезную проблему при необходимости перепровести документы. Особенно если у них последовательность проведения изменилась. Причем здесь само решение? Задача поставлена распределить. Задача решена. А если кто-то вставит это в проведение - сам виноват. Я больше вам скажу, в той нетиповой конфигурации (за авторством КАМИН), с которой я в данный момент работаю именно так и было реализовано. Не запросом, правда, а кодом. Крайне запутанным, кстати. Но это еще полбеды. Эти ребята предусмотрели два варианта проведения. С распределением сумм до проведения и сохранения в табличной части с возможностью коррекции. И с распределением во время проведения. Мало того, что перепроведение такого документа было черевато. Так еще и код распределения был в двух разных местах продублирован. Распределение при проведении - в модуле документа. А распределение на форме - в модуле формы. И код этот чуть-чуть отличался. Видимо в какой-то момент исправили небольшую ошибку в одном из вариантов, а во втором - забыли. Что при этом происходило, думаю, объяснять не надо.

А сам запрос прекрасно будет жить в рабочей конфигурации. Замечу, что при решении типовых задач никоим образом не будет лишним воспользоваться заранее придуманными алгоритмами. И это касается не только 1С. Если задача трудоемка и существует алгоритм, позволяющий увеличить скорость решения в разы, то им можно и нужно воспользоваться. На то он и существует. Это, кстати, сравнительно простое решение. Бывают гораздо более сложные. Разбираться в его коде при этом можно только один раз - когда первый раз увидел. Или вообще не разбираться, если источнику доверяешь. Главное знать, где его границы, где входные данные и где результат.
14. Роман Озеряный (rozer) 14.08.15 12:59
(10)(10) Dimel, а зачем так сложно с тета-соединением и вычитать потом его ? Не догоняю... так тоже работает

ВЫБРАТЬ
    "А" КАК Объект,
    100 КАК База
ПОМЕСТИТЬ Данные

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "Б",
    200

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "В",
    1000

;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
    СУММА(Данные.База) КАК СуммаБазы
ПОМЕСТИТЬ Итог
ИЗ
    Данные КАК Данные
;

////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
    Данные.Объект,
    Данные.База,
    (ВЫРАЗИТЬ(&Сумма * Данные.База / Итог.СуммаБазы КАК ЧИСЛО(15, 2))) КАК РаспределеноПоБазе
ИЗ
    Итог КАК Итог,
    Данные КАК Данные
...Показать Скрыть
15. Dimel 17.08.15 07:42
(14) rozer, Попробуй своим запросом распределить 10 на 3 равные части (куда у тебя ошибки округления денутся?).
16. Роман Озеряный (rozer) 19.08.15 23:51
(15) Dimel, попробовал получилось 0.77+1.54+7.69=10 Но наверно да, ты прав, про округление я не подумал но как понять про использование ВТ "ДанныеСНакоплением" я так и не понял ( Чот мудреный запрос выходит... сам придумал или подсказал кто? Смысл можешь пояснить?
17. Дмитрий Горелов (PythonJ) 20.08.15 06:53
(16) rozer, Смысл заключается в том, что ошибка округления накапливается и учитывается по мере распределения. Попробуй расписать процесс в числах с распределением, например, 20 на 7 одинаковых строк. Обрати внимание на распределение последней строки. Туда попадает весь остаток, независимо от доли. По аналогии происходит и в предыдущих строках.