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

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

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

• собственно позиция (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? )