В стиле SQL - 3 (распределение сумм)

[egorius]

Посмотрим на пример из жизни. В разных вариациях он встречался мне раза три как минимум.

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

• собственно позиция (item_id) — стол офисный, кадка с фикусом и т. п.
• стоимость одной штуки (cost)
• количество штук (quantity) — положительно для поступлений, отрицательно для выбытий
• дата (trx_date)

При этом так получилось, что для поступлений мы знаем стоимость позиций (сами же покупали), а для выбытий — нет (кладовщик выдает первую попавшуюся подходящую позицию; неизвестно, когда и по какой стоимости она закупалась).

Задача: написать отчет, показывающий стоимость наличных остатков каждой позиции. За неимением реальной информации о выбытиях считать, что позиции уходят со склада в порядке FIFO.

На примере (для одной позиции):

1. Поступление 5 штук по цене 1001 руб.
2. Поступление 4 штук по цене 1010 руб.
   
3. Выбытие 3 штук

   цена	поступило	выбыло	осталось
   1001	5	3	2
   1010	4	0	4

4. Выбытие 3 штук

   цена	поступило	выбыло	осталось
   1001	5	3+2	0
   1010	4	1	3

5. Поступление 2 штук по цене 1100 руб.

   цена	поступило	выбыло	осталось
   1001	5	3+2	0
   1010	4	1	3
   1100	2	0	2

Итак, отчет должен показать 3×1010 + 2×1100 = 5230.

Лирическое отступление. Числа в примерах лучше подбирать так, чтобы по результату можно было сразу сказать, откуда он взялся. 5230 читается справа налево: осталось 0 от первого поступления, 3 от второго и 2 от третьего, 5 — контрольная цифра. А вот попробуйте понять, что такое 2000 при цене поступлений 1000 и 500.

Процедурное решение вполне очевидно. Например, можно сделать так:

create table transactions(
  item_id     number,
  quantity    number,
  cost        number,
  trx_date    date
);

create or replace type trx_rec is object(
  item_id     number,
  quantity    number,
  cost        number
);
/
create or replace type trx_tab is table of trx_rec;
/

-- транзакции из примера
insert into transactions values(1, +5, 1001, to_date('01.02.2014','DD.MM.YYYY'));
insert into transactions values(1, +4, 1010, to_date('02.02.2014','DD.MM.YYYY'));
insert into transactions values(1, -3, null, to_date('05.02.2014','DD.MM.YYYY'));
insert into transactions values(1, -3, null, to_date('07.02.2014','DD.MM.YYYY'));
insert into transactions values(1, +2, 1100, to_date('09.02.2014','DD.MM.YYYY'));
-- добавим еще вторую позицию
insert into transactions values(2, +3,  300, to_date('03.02.2014','DD.MM.YYYY'));
insert into transactions values(2, -1, null, to_date('04.02.2014','DD.MM.YYYY'));

set serveroutput on
declare
  trx trx_tab := trx_tab();
  qty_so_far number;
begin
  -- читаем поступления в массив (в порядке дат)
  for i in (
    select   t.item_id,
             t.quantity,
             t.cost
    from     transactions t
    where    t.cost is not null
    order by t.trx_date
  )
  loop
    trx.extend;
    trx(trx.count) := trx_rec(i.item_id, i.quantity, i.cost);
  end loop;
  -- обработка выдач
  for i in (
    select   t.item_id,
             sum(t.quantity) quantity
    from     transactions t
    where    t.cost is null
    group by t.item_id
  )
  loop
    qty_so_far := -i.quantity;
    for n in 1 .. trx.count loop
      if trx(n).item_id = i.item_id and trx(n).quantity > 0 then
        if trx(n).quantity >= qty_so_far then
          trx(n).quantity := trx(n).quantity - qty_so_far;
          exit;
        else
          qty_so_far := qty_so_far - trx(n).quantity;
          trx(n).quantity := 0;
        end if;
      end if;
    end loop;
  end loop;
  -- результат
  for i in (
    select   t.item_id,
             sum(t.cost * t.quantity) cost
    from     table(trx) t
    group by t.item_id
    order by t.item_id
  )
  loop
    dbms_output.put_line(i.item_id||': '||i.cost);
  end loop;
end;
/

Небольшая оптимизация состоит в том, чтобы списывать сразу общую сумму, а не возиться с каждым выбытием отдельно. Чтобы не засорять код сверх меры, я все-таки перепоручил заключительную часть — группировку сумм по позициям  — SQL, обратившись к коллекции как к таблице (select from table()).

А как это сделать на чистом SQL? Аналитические функции — наше все.

Здесь нам поможет сумма нарастающим итогом. Напомню, что функция sum (как и ряд других функций) может играть несколько ролей:

• sum
  Агрегатная функция: суммирует все, что сгруппировано с помощью group by.
• sum over (partition by ...)
  Аналитическая функция: суммирует все, что попадает в окно, заданное partition by.
• sum over (partition by ... order by ...)
  Аналитическая функция: суммирует нарастающим итогом все, что попадает в окно, заданное partition by.

Идею решения удобно показать на том же примере:

поступило	поступило (нарастающим итогом)	всего должно выбыть	разница
A	B	C	D = B − C
5	5	6	−1
4	9	6	3
2	11	6	5

Возможны три варианта.

1. Разница отрицательна (D < 0):
   этой и предыдущих транзакций не хватило для выбытия, остаток равен нулю.
2. Разница меньше количества в данной транзакции (0 ≤ D < A):
   этой транзакции уже хватило, остаток равен разнице.
3. Разница не меньше количества в данной транзакции (D ≥ A):
   до списания с этой транзакции дело не дошло.

Собственно запрос:

with transactions(item_id, quantity, cost, trx_date) as (
  -- транзакции из примера
  select 1, +5, 1001, to_date('01.02.2014','DD.MM.YYYY') from dual union all
  select 1, +4, 1010, to_date('02.02.2014','DD.MM.YYYY') from dual union all
  select 1, -3, null, to_date('05.02.2014','DD.MM.YYYY') from dual union all
  select 1, -3, null, to_date('07.02.2014','DD.MM.YYYY') from dual union all
  select 1, +2, 1100, to_date('09.02.2014','DD.MM.YYYY') from dual union all
  -- добавим еще вторую позицию
  select 2, +3,  300, to_date('03.02.2014','DD.MM.YYYY') from dual union all
  select 2, -1, null, to_date('04.02.2014','DD.MM.YYYY') from dual
),
receipts(item_id, quantity, cost, cumulation_qty, trx_date) as (
  select   item_id,
           quantity,
           cost,
           sum(quantity) over (partition by item_id order by trx_date),
           trx_date
  from     transactions
  where    cost is not null
),
issues(item_id, quantity) as (
  select   item_id,
           -sum(quantity)
  from     transactions
  where    cost is null
  group by item_id
),
distributed_qty(item_id, quantity, cost) as (
  select   r.item_id,
           case
             when r.cumulation_qty - i.quantity < 0 then
               0
             when r.cumulation_qty - i.quantity < r.quantity then
               r.cumulation_qty - i.quantity
             else
               r.quantity
           end,
           r.cost
  from     receipts r,
           issues i
  where    i.item_id = r.item_id
)
select   item_id,
         sum(cost * quantity) cost
from     distributed_qty
group by item_id
order by item_id;

Вот вроде бы все то же самое, а кода получается в полтора раза меньше.

Небольшое предостережение напоследок: если по правилу сортировки, заданному во фразе over (order by), на одно место будут претендовать несколько строк, то результат, скорее всего, будет неожиданным. Например, приведенный запрос отработает некорректно, если на одну дату будут две транзакции. В таком случае, если реальный порядок не важен, а нарастающий итог все-таки нужен, удобно добавлять к сортировке rownum.