В стиле SQL - 4 (округление копеек)

[egorius]

Вот еще задача из реальной жизни: округление копеек. Путь имеется отчет, показывающий какие-то денежные показатели; суммы надо выводить с точностью до копеек.

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

with depts(dept_id, quantity) as (
  select 1, 300 from dual union all
  select 2, 300 from dual union all
  select 3, 100 from dual union all
  select 4, 100 from dual union all
  select 5, 100 from dual
),
expenditures(amount) as (
  select 1000 from dual
),
report(dept_id,amount) as( 
  select   d.dept_id,
           e.amount * ratio_to_report(d.quantity) over ()
  from     depts d,
           expenditures e
)
select   dept_id,
         round(amount,2) amount
from     report;

DEPT_ID AMOUNT 
------- ------ 
      1 333,33 
      2 333,33 
      3 111,11 
      4 111,11 
      5 111,11

Какая досада, копеечка-то потерялась! Бухгалтер, работающий с РСБУ, этого не переживет.

Чтобы получилось правильно, надо учесть ошибки округления. Обычно их собирают вместе и добавляют к строке с максимальной суммой.

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

declare
  total     number;
  new_total number;
  max_dept  number;
begin
  select   round(sum(amount),2)
  into     total
  from     tmp;

  update   tmp
  set      amount = round(amount,2);
  
  select   sum(amount)
  into     new_total
  from     tmp;

  if total = new_total then
    return;
  end if;

  select   dept_id
  into     max_dept
  from     tmp
  where    amount = (
             select max(amount) from tmp
           )
       and rownum = 1;
       
  update   tmp
  set      amount = amount + total - new_total
  where    dept_id = max_dept;
end;
/

Можно ли это сделать одной командой SQL? Конечно, с помощью аналитический функций. Во-первых, посчитать сумму после округления (sum), во-вторых, занумеровать строки в порядке убывания суммы (row_number). Например, так:

with depts(dept_id, quantity) as (
  ...
),
expenditures(amount) as (
  ...
),
report(dept_id,amount) as( 
  ...
)
select   dept_id,
         round(amount,2) + case
                             when rnk = 1 then round(r_error,2)
                             else 0
                           end amount
from     (                           
           select   r.dept_id,
                    r.amount,
                    sum(r.amount) over () - sum(round(r.amount,2)) over () r_error,
                    row_number() over (order by r.amount desc) rnk
           from     report r
         );

Все это довольно тривиально и не очень интересно. Поэтому вот вопрос «со звездочкой». Нам пришлось написать довольно много вспомогательного кода вокруг отчета. В реальности сам отчет наверняка будет гораздо сложнее и усложнять его еще больше не хотелось бы. Можно ли решить задачу как-то более прозрачно?

В принципе, да, если принять другу стратегию: ошибки округления не накапливать, а добавлять их к сумме при первой возможности. Решение состоит в написании собственной аналитической функции.

Для этого создается специальный тип-объект:

create or replace type ToMoneyImpl as object(
  amount  number,
  r_error number,
  static function ODCIAggregateInitialize(sctx in out ToMoneyImpl)
    return number,
  member function ODCIAggregateIterate(self in out ToMoneyImpl, value in number)
    return number,
  member function ODCIAggregateMerge(self in out ToMoneyImpl, ctx in ToMoneyImpl)
    return number,
  member function ODCIAggregateTerminate(self in out ToMoneyImpl, returnValue out number, flags in number)
    return number
);
/

Функция ODCIAggregateInitialize будет вызываться Ораклом каждый раз при смене окна аналитической функции, ODCIAggregateIterate — для каждого нового значения из окна, а ODCIAggregateTerminate — для возврата значения. В случае обычной аналитической функции ODCIAggregateTerminate вызывается несколько раз в самом конце окна, а для режима нарастающего итога over (orber by ...) — каждый раз после ODCIAggregateIterate. Нам, разумеется, нужен именно режим нарастающего итога.

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

create or replace type body ToMoneyImpl is 
  static function ODCIAggregateInitialize(sctx in out ToMoneyImpl)
  return number
  is
  begin
    sctx := ToMoneyImpl(null,0);
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateIterate(self in out ToMoneyImpl, value in number)
  return number
  is
  begin
    self.r_error := self.r_error + value - round(value,2);
    self.amount := round(value,2) + round(self.r_error, 2);
    self.r_error := self.r_error - round(self.r_error, 2);
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateMerge(self in out ToMoneyImpl, ctx in ToMoneyImpl)
  return number
  is
  begin
    self.r_error := self.r_error + ctx.r_error;
    return ODCIConst.Success;
  end;
  
  member function ODCIAggregateTerminate(self in out ToMoneyImpl, returnValue out number, flags in number)
  return number
  is
  begin
    returnValue := self.amount;
    return ODCIConst.Success;
  end;

end;
/

Наконец, объявляем саму функцию:

create or replace function to_money(a number) return number
  aggregate using ToMoneyImpl;
/

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

with depts(dept_id, quantity) as (
  ...
),
expenditures(amount) as (
  ...
),
report(dept_id,amount) as( 
  ...
)
select   dept_id,
         to_money(amount) over (order by dept_id)
from     report;

DEPT_ID AMOUNT 
------- ------ 
      1 333,33 
      2 333,34 
      3 111,11 
      4 111,11 
      5 111,11

P. S. Документация по теме.