トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-19 FIFOで在庫単価の計算

SQLパズル

在庫テーブル
SEQ   区分  個数   入荷単価
---   ----  ----   --------
 1    入庫     9        100
 2    入庫     5        110
 3    出庫    -5       null
 4    返品    -6       null
 5    入庫     5        120
 6    返納     3       null
 7    入庫     5        100

先入先出法で在庫単価を求める。

在庫には入庫、出庫、返品、返納があります。
SEQ順に処理するとして返品は最も古い入庫個数から減らします。
返納は最も新しい入庫個数を増やします。

SEQから入荷単価までをフィールドに持つとして在庫数と在庫単価を求めます。

出力結果
SEQ   区分  個数   入荷単価    在庫数   在庫単価
---   ----  ----   --------   ------   ---------------------------------
 1    入庫     9        100       9    100
 2    入庫     5        110      14    (9*100 + 5*110) / 14 = 103.571...
 3    出庫    -5       null       9    ((9-5)*100 + 5*110) / 9 = 105.555...
 4    返品    -6       null       3    (3*110) / 3 = 110
 5    入庫     5        120       8    (3*110 + 5*120) / 8 = 116.25
 6    返納     3        120      11    (3*110 + (5+3)*120) / 11 = 117.272...
 7    入庫     5        100      16    (3*110 + 8*120 + 5*100) / 16 = 111.875

こちらを参考にさせていただきました


データ作成スクリプト

create table 在庫(
SEQ number(1),
区分 char(4),
個数 number(1),
入荷単価 number(3));

insert into 在庫 values(1,'入庫', 9,100);
insert into 在庫 values(2,'入庫', 5,110);
insert into 在庫 values(3,'出庫',-5,null);
insert into 在庫 values(4,'返品',-6,null);
insert into 在庫 values(5,'入庫', 5,120);
insert into 在庫 values(6,'返納', 3,null);
insert into 在庫 values(7,'入庫', 5,100);
commit;


SQL

--■■■表関数を使わない方法■■■
with WorkView as (
select SEQ,区分,個数,入荷単価,在庫数,正数計,
Lag(正数計,1,0) over(order by SEQ) as Lag正数計,負数計
from (select SEQ,区分,個数,
      decode(区分,'返納',Last_Value(入荷単価 ignore nulls)
                         over(order by SEQ),入荷単価) as 入荷単価,
      sum(個数) over(order by SEQ) as 在庫数,
      sum(decode(sign(個数), 1, 個数,0)) over(order by SEQ) as 正数計,
      sum(decode(sign(個数),-1,-個数,0)) over(order by SEQ) as 負数計
      from 在庫))
select SEQ,区分,個数,入荷単価,在庫数,
((select sum(b.個数*b.入荷単価) from WorkView b
   where b.SEQ <= a.SEQ
     and b.個数 > 0)
-
case when a.負数計>0
then nvl((select sum(b.個数 * b.入荷単価) from WorkView b
           where b.SEQ <= a.SEQ
             and b.個数 > 0
             and b.正数計 <=a.負数計),0) 
   + nvl((select (a.負数計-b.Lag正数計) * b.入荷単価 from WorkView b
           where b.SEQ = (select min(c.SEQ) from WorkView c
                           where c.SEQ <= a.SEQ
                             and c.正数計 > a.負数計)),0) else 0 end) / 在庫数 as 在庫単価
from WorkView a
order by SEQ;

--■■■表関数を使う方法■■■
create or replace Package Pack10_19 Is
    type PrintType is record(
    SEQ      在庫.SEQ%type,
    区分     在庫.区分%type,
    個数     在庫.個数%type,
    入荷単価 在庫.入荷単価%type,
    在庫数   number(2),
    在庫単価 number(5,2));

    type PrintTypeSet is table of PrintType;
end;
/

create or replace function 在庫Record return Pack10_19.PrintTypeSet PipeLined is
    out_rec Pack10_19.PrintType;

    --単価データを保存する配列
    type 単価DataTypeArray is table of number(3) index by binary_integer;
    単価Array 単価DataTypeArray;
    単価ArrayInsPointer binary_integer :=0;

    総単価 number(5);
begin
    for rec in (select SEQ,区分,個数,
                decode(区分,'返納',Last_Value(入荷単価 ignore nulls)
                                   over(order by SEQ),入荷単価) as 入荷単価,
                sum(個数) over(order by SEQ) as 在庫数
                from 在庫
                order by SEQ) Loop

        if rec.区分 in('入庫','返納') then --配列に入れる
            for I in 1..rec.個数 Loop
                単価ArrayInsPointer := 単価ArrayInsPointer + 1;
                単価Array(単価ArrayInsPointer) := rec.入荷単価;
            end Loop;
        else  --配列から取り去る
            for I in 1..abs(rec.個数) Loop
                単価Array.delete(単価Array.FIRST);
            end Loop;
        end if;

        out_rec.SEQ := rec.SEQ;
        out_rec.区分 := rec.区分;
        out_rec.個数 := rec.個数;
        out_rec.在庫数 := rec.在庫数;
        out_rec.入荷単価 := rec.入荷単価;

        総単価 := 0;
        for I in 単価Array.FIRST..単価Array.LAST Loop
            総単価 := 総単価 + 単価Array(I);
        end Loop;
        out_rec.在庫単価 := 総単価/rec.在庫数;

        pipe row(out_rec);
    end Loop;
end;
/

sho err

select * from table(在庫Record);


解説

withでワークビューを作成すると、
複雑なSQLを記述しやすくなります。

表関数を使ってもいいでしょう。

7-54 引き当て処理
10-20 LIFOで在庫単価の計算

PL/SQLのコレクションおよびレコードの使用

スタック (stack)の資料
待ち行列,キュー(queue)の資料