Посмотрим на пример из жизни. В разных вариациях он встречался мне раза три как минимум.
Пусть имеется некое подобие складского учета, в котором ведутся только транзакции поступления позиций на склад и выбытия позиций со склада. Атрибутами транзакций являются:
- собственно позиция (item_id) — стол офисный, кадка с фикусом и т. п.
- стоимость одной штуки (cost)
- количество штук (quantity) — положительно для поступлений, отрицательно для выбытий
- дата (trx_date)
При этом так получилось, что для поступлений мы знаем стоимость позиций (сами же покупали), а для выбытий — нет (кладовщик выдает первую попавшуюся подходящую позицию; неизвестно, когда и по какой стоимости она закупалась).
Задача: написать отчет, показывающий стоимость наличных остатков каждой позиции. За неимением реальной информации о выбытиях считать, что позиции уходят со склада в порядке FIFO.
На примере (для одной позиции):
- Поступление 5 штук по цене 1001 руб.
- Поступление 4 штук по цене 1010 руб.
- Выбытие 3 штук
| цена | поступило | выбыло | осталось |
|---|
| 1001 | 5 | 3 | 2 |
| 1010 | 4 | 0 | 4 |
- Выбытие 3 штук
| цена | поступило | выбыло | осталось |
|---|
| 1001 | 5 | 3+2 | 0 |
| 1010 | 4 | 1 | 3 |
- Поступление 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? )
