Могу ли я разделить сумму между несколькими сторонами и округлить до "основной" стороны в одном SQL-запросе?

#sql #plsql #rounding

Вопрос:

Я работаю над процессом oracle PL/SQL, который делит одну денежную сумму между несколькими заинтересованными сторонами в определенной группе. Предполагая, что «pGroupRef» является входным параметром, текущая реализация сначала определяет «основную» вовлеченную сторону, а затем распределяет сумму по всем второстепенным элементам следующим образом:

 INSERT INTO ActualValue
SELECT
...
pGroupRef AS GroupRef,
ROUND(Am.Amount * P.SplitPercentage / 100, 2) AS Amount,
...
FROM
Amount Am,
Party P
WHERE
Am.GroupRef = pGroupRef
AND P.GroupRef = Am.GroupRef
...
P.PrimaryInd = 0;

Наконец, он запускает вторую процедуру, чтобы вставить любую сумму, оставшуюся основной стороне, т. е.:

 INSERT INTO ActualValue
SELECT
...
pGroupRef AS GroupRef,
Am.Amount - S.SecondaryAmounts,
FROM
Amount Am,
Party P,
(SELECT SUM(Amount) AS SecondaryAmounts FROM ActualValue WHERE GroupRef = pGroupRef) S
WHERE
Am.GroupRef = pGroupRef
AND P.GroupRef = Am.GroupRef
...
P.PrimaryInd = 1;

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

Я полагаю, что всегда мог бы рассчитать правильные суммы в массиве перед запуском единой унифицированной вставки, но я чувствую, что должен быть элегантный математический способ передать эту логику в одном SQL-запросе.

Ответ №1:

Таким образом, вы можете использовать аналитические функции, чтобы получить то, что вы ищете. Поскольку я не знал вашей точной структуры, это всего лишь пример:

 SELECT s.party_id, s.member_id, 
       s.portion   DECODE(s.prime, 1, s.total - SUM(s.portion) OVER (PARTITION BY s.party_id),0)
FROM (SELECT p.party_id, p.member_id, 
             ROUND(a.amt*(p.split/100), 2) AS PORTION,
             a.amt AS TOTAL, p.prime
      FROM party p
      INNER JOIN amount a ON p.party_id = a.party_id) s

Таким образом, в запросе у вас есть подзапрос, который собирает необходимую информацию, затем внешний запрос объединяет все вместе, применяя только оставшуюся часть к записи, помеченной как простая.

Вот DBFiddle, показывающий, как это работает (ССЫЛКА)

Н. Б.: Интересно, что в примере в DBFiddle переплата составляет 0,01, поэтому основной на самом деле платит меньше.