谜题69 入出库之 累加

create table widgetinventory
(
receipt_nbr integer not null primary key,
purchase_date timestamp default current_timestamp,
qty_on_hand integer not null
check (qty_on_hand >= 0),
unit_price decimal(12,4) not null
)
/
insert into widgetinventory
select 1,date '2005-08-01', 15,10.00 from dual union all
select 2,date '2005-08-02', 25,12.00 from dual union all
select 3,date '2005-08-03', 40,13.00 from dual union all
select 4,date '2005-08-04', 35,12.00 from dual union all
select 5,date '2005-08-05', 45,10.00 from dual
/
create view lifo(stock_date,unit_price,qty_on_hand,tot_cost)
as
select w1.purchase_date,w1.unit_price,sum(w2.qty_on_hand),sum(w2.qty_on_hand * w2.unit_price)
from widgetinventory w1,widgetinventory w2
where w2.purchase_date >= w1.purchase_date
group by w1.purchase_date,w1.unit_price
/
select * from lifo order by 1
/
select w1.purchase_date,
       w1.unit_price,
       sum(w1.qty_on_hand) over(order by w1.purchase_date desc) as qty_on_hand,
       sum(w1.qty_on_hand * w1.unit_price) over(order by w1.purchase_date desc) as tot_cost
  from widgetinventory w1
 order by w1.purchase_date
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss ff6';