在庫数の加減変動SQL 累積sum overを使う

boxcodeadmin

2021年6月20日

在庫数の推移を時系列に追いたいということございませんか？

単純に入庫数と出庫数の総数を日付ごとに足し引きすれば簡単にできますが、

意外と方法が思いつかない。

今日はそんな在庫推移を累積値を用いたSQLをご紹介します。

とある店舗における入荷と売上（返品は考慮しない）を例にとっていきます。

また、取扱商品はあり得ませんが、一つのみ。

データの準備

--入荷テーブル
create table receive(
    orderno varchar(2)
   ,orderdate date
   ,quantity numeric
);

--入荷データ
insert into receive values
('1', '2021/01/05', '10'),
('2', '2021/01/10', '20'),
('3', '2021/01/20', '10'),
('4', '2021/01/31', '20');

--売上テーブル
create table sales(
    orderno varchar(2)
   ,orderdate date
   ,quantity numeric
);

--売上データ
insert into sales values
('1', '2021/01/05', '2'),
('2', '2021/01/05', '1'),
('3', '2021/01/07', '1'),
('4', '2021/01/08', '2'),
('5', '2021/01/08', '3'),
('6', '2021/01/09', '1'),
('7', '2021/01/11', '2'),
('8', '2021/01/13', '5'),
('9', '2021/01/14', '1'),
('10', '2021/01/14', '1'),
('11', '2021/01/15', '2'),
('12', '2021/01/17', '1'),
('13', '2021/01/19', '3'),
('14', '2021/01/20', '1'),
('15', '2021/01/23', '2'),
('16', '2021/01/25', '1'),
('17', '2021/01/28', '4'),
('18', '2021/01/28', '1'),
('19', '2021/01/29', '1'),
('20', '2021/01/30', '1');

以下の通り、とある店舗では入荷が発生しています。

売上は以下の通りです。

SQL作成

まずは入荷日と売上日のみを使用すると結果が日付の歯抜けデータとなるため以下のようなクエリを準備します。

--入荷テーブルと売上テーブルをunion結合し、日付を取得する
with aaa as (select orderdate 
               from receive
              union all
             select orderdate
               from sales),
--入荷テーブルと売上テーブルの日付の最小値と最大値取得し、最小値は1日、最大値は末日に設定する
bbb as      (select cast(generate_series as date) as 日付
               from generate_series((select date_trunc('month',min(orderdate)) from aaa),
                                    (select date_trunc('month',max(orderdate)) + '1 month' +'-1 Day' from aaa), '1 day'))

次に入荷と売上の日付毎の総数を取得するクエリを作成します。

--入荷の日付毎の数量
ccc as      (select orderdate as 入荷日
                   ,sum(quantity) as 入荷数
               from receive
              group by orderdate),
--売上の日付毎の数量
ddd as      (select orderdate as 売上日
                   ,sum(quantity) as 売上数
               from sales
              group by orderdate)

これらのSQLを足し合わせ、まずは日付ごとの入荷数と売上数を表示します。

with aaa as (select orderdate 
               from receive
              union all
             select orderdate
               from sales),
bbb as      (select cast(generate_series as date) as 日付
               from generate_series((select date_trunc('month',min(orderdate)) from aaa),
                                    (select date_trunc('month',max(orderdate)) + '1 month' +'-1 Day' from aaa), '1 day')),
ccc as      (select orderdate as 入荷日
                   ,sum(quantity) as 入荷数
               from receive
              group by orderdate),
ddd as      (select orderdate as 売上日
                   ,sum(quantity) as 売上数
               from sales
              group by orderdate)
select bbb.日付
      ,coalesce(ccc.入荷数,0) as 入荷数
      ,coalesce(ddd.売上数,0) as 売上数
  from bbb
  left join ccc
    on bbb.日付 = ccc.入荷日
  left join ddd
    on bbb.日付 = ddd.売上日

結果

在庫推移を出すためには

①入荷数の日付順での累計値を取得

②売上数の日付順での累計値を取得

③①から②を引く

で対応できます。

累計はsum overで対応しました。

with aaa as (select orderdate 
               from receive
              union all
             select orderdate
               from sales),
bbb as      (select cast(generate_series as date) as 日付
               from generate_series((select date_trunc('month',min(orderdate)) from aaa),
                                    (select date_trunc('month',max(orderdate)) + '1 month' +'-1 Day' from aaa), '1 day')),
ccc as      (select orderdate as 入荷日
                   ,sum(quantity) as 入荷数
               from receive
              group by orderdate),
ddd as      (select orderdate as 売上日
                   ,sum(quantity) as 売上数
               from sales
              group by orderdate)
select bbb.日付
      ,coalesce(ccc.入荷数,0) as 入荷数
      ,coalesce(ddd.売上数,0) as 売上数
      ,coalesce(sum(ccc.入荷数) over(order by bbb.日付),0) as 入荷数累計 --①入荷数の日付順での累計値を取得
      ,coalesce(sum(ddd.売上数) over(order by bbb.日付),0) as 売上数累計 --②売上数の日付順での累計値を取得
      ,coalesce(sum(ccc.入荷数) over(order by bbb.日付),0)
       - coalesce(sum(ddd.売上数) over(order by bbb.日付),0) as 在庫数推移 --③①から②を引く
  from bbb
  left join ccc
    on bbb.日付 = ccc.入荷日
  left join ddd
    on bbb.日付 = ddd.売上日

結果