生活用品メーカーの在庫管理システムのデータベース実装・運用に関する記述

D 社は、日用品、園芸用品、電化製品などのホームセンター向け商品を製造販売している。販売物流の拠点では自社で構築した在庫管理システムを使用している。データベーススペシャリストのEさんは、マーケティング、経営分析などに使用するデータ（以下、分析データという）の提供依頼を受けてその収集に着手した。

分析データの提供依頼

分析データ提供依頼の例を表1に示す。

在庫管理業務の概要

在庫管理業務の概念データモデルを図1に、主な属性の意味・制約を表2に示す。在庫管理システムでは、図1の概念データモデル中のサブタイプをスーパータイプのエンティティタイプにまとめた上で、エンティティタイプをテーブルとして実装している。Eさんは、在庫管理業務への理解を深めるために、図1、表2を参照して、表3の業務ルール整理表を作成した。表3では、項番ごとに、幾つかのエンティティタイプを対象に、業務ルールを列記した①～④が、概念データモデルに合致するか否かを判定し、合致する業務ルールの番号を全て記入している。

問合せの検討

Eさんは、依頼1に対応するために図2のZチャートの例を依頼元から入手し、Zチャートを作成するための問合せの内容を、表4に整理した。表4中のT1は月間出荷数量、T2は移動累計出荷数量、T3は累計出荷数量を求める問合せである。

注記1 行ごとに問合せを記述し問合せ名を付ける。問合せ名によって問合せ結果行を参照できる。

注記2 列名又は演算には，テーブルから射影する列名又は演算によって求める項目を"項目名=［演算の内容］"の形式で記述する。

注記3 テーブル名又は問合せ名には，参照するテーブル名又は問合せ名を記入する。

注記4 選択又は結合の内容には，テーブル名又は問合せ名ごとの選択条件，結合の具体的な方法と結合条件を記入する。

依頼2について図3のSQL文の検討を行い、実装したSQL文を実行して図4のヒートマップの例を作成した。

  WITH S1 AS (
    SELECT S.拠点#, SM.棚#
    FROM 出庫 S
      INNER JOIN 出庫明細 SM ON S.拠点# = SM.拠点# AND S.出庫# = SM.出庫#
    WHERE S.拠点# = :hv1 AND S.出庫年月日 BETWEEN :hv2 AND :hv3
  ), S2 AS (
    SELECT キ AS 出庫回数
    FROM 棚 T
      LEFT JOIN S1 ON S1.拠点# = T.拠点# AND S1.棚# = T.棚#
    WHERE T.拠点# = :hv1
    ク
  ), S3 AS (
    SELECT 棚#, RANK() OVER (ケ) AS 出庫回数順位
    FROM S2
  )
  SELECT 棚#,
    CASE
      WHEN (100 * コ OVER()) <= 20 THEN '高'
      WHEN (100 * コ OVER()) <= 50 THEN '中'
      ELSE '低'
    END AS 出庫頻度区分
  FROM S3
        

依頼3への対応

Eさんは、在庫回転率及びその根拠となる数値（以下、計数という）の算出方法を確認した上で、分析データを作成する仕組みを検討することにした。

1. 計数の算出方法確認

   在庫管理業務では、次のように、年月、拠点、商品ごとに計数を算出している。

   • 月締めを行う。月締めは対象月の翌月の第5営業日までに実施する。
   • 月締めまでの間は、前月分の出荷であっても訂正できる。
   • 月末時点の在庫を、先入先出法によって評価し、在庫金額を確定する。在庫金額算出に際して、商品有高表及び残高集計表を作成する。商品有高表の例を表5に、残高集計表の例を表6に示す。
   1. 商品有高表
      • 前月末時点の残高を繰り越して受入欄に記入する。残高は、入荷ごとに記録するので、複数入荷分の残高があれば入荷の古い順に繰り越す。
      • 受入、払出の都度、収支を反映した残高を記入する。例えば、表5中の行2の残高には行1の受入を反映した残高を転記し、行3の残高には行2の受入を反映した残高を記入している。
      • 当日中の入荷を受入欄に、出荷を払出欄に記入する。入荷の入荷年月日、出荷の出荷年月日を受払日付とし、受払日付順及び入出荷の登録順に記入する。
      • 出荷による払出は、入荷の古い順に残高を引き落とし、複数入荷分の残高を引き落とす場合は、残高ごとに行を分ける。入出荷による変更後の在庫を入荷の古い順に残高欄に記入する。
      • 赤伝は、受払日付に発生日ではなく、訂正元出荷と同じ受払日付でマイナスの払出を記入する。
      • 月末時点の残高を入荷の古い順に払出欄に記入して次月に繰り越す。
   2. 残高集計表

      年月、拠点、商品ごとに、商品有高表を集計・計算して月初残高、当月受入、当月払出、月末残高、在庫回転率の数量、金額をそれぞれ次のようにまとめる。

      • 月初残高は、前月繰越による受入の数量、金額を集計する。
      • 当月受入は、当月中の入荷による受入の数量、金額を集計する。
      • 月末残高は、次月繰越による払出の数量、金額を集計する。
      • 当月払出は、「月初残高 + 当月受入 － 月末残高」によって、数量、金額をそれぞれ求める。
      • 在庫回転率は、「当月払出 ÷ ((月初残高 + 月末残高) ÷ 2)」によって、数量、金額をそれぞれ求める。

   表5 商品有高表の例（未完成）

   行	受払日付	摘要	受入		払出		残高（在庫）
   			数量	単価	金額	数量	単価	金額	数量	単価	金額
   1	09-01	前月繰越	100	80	8,000				100	80	8,000
   2	09-01	前月繰越	300	85	25,500				100	80	8,000
   3									300	85	25,500
   4	09-04	出荷				30	80	2,400	70	80	5,600
   5									300	85	25,500
   6	09-07	出荷				70	80	5,600	300	85	25,500
   7						40	85	3,400	260	85	22,100
   8	09-12	入荷	150	90	13,500				260	85	22,100
   9									150	90	13,500
   10	09-17	出荷				50	85	4,250	g	h
   11									150	90	13,500
   12	09-17	赤伝				▲50	85	▲4,250	i	j
   13									k	l
   14	09-17	黒伝				260	85	22,100
   15						40	90	3,600

   表6 残高集計表の例（一部省略）

   年月	拠点#	商品#	月初残高		当月受入		当月払出		月末残高		在庫回転率
   			数量	金額	数量	金額	数量	金額	数量	金額	数量	金額
   2023-09	33	112233	400	450	500	350	1.33	33,500	42,000	42,500	33,000	1.28
   :	:	:	:	:	:	:	:	:	:	:	:

2. 計数を格納するテーブル設計

   Eさんは、解像度の高い分析データを提供するために、商品有高表及び残高集計表の計数をテーブルに格納することにして図5のテーブルを設計した。

   1. 「受払明細」テーブル
      • 商品有高表の受入、払出のどちらかに数量、単価、金額の記載のある行を格納する。
      • 受払#には、年月、拠点#、商品#ごとに、商品有高表中の受入又は払出の数量に記載のある行を対象に1から始まる連番を設定する。一つの出荷が複数の残高から払い出される場合には、払出の行を分け、それぞれに受払#を振る。
      • 摘要区分には、「前月繰越」、「出荷」、「入荷」、「赤伝」、「黒伝」、「次月繰越」のいずれかを設定する。
   2. 「受払残高」テーブル
      • 受払明細ごとに、受払による収支を反映した後の残高数量を、基になる受入ごとに記録する。残高の基になった受入（前月繰越又は入荷）の受払#、単価を、受払残高の基受払#、単価に設定する。
   3. 「残高集計」テーブル
      • 受払明細及び受払残高の対象行を「残高集計」の作成要領に従って集計・計算して「残高集計」テーブルの行を作成する。

   受払明細（年月、拠点#、商品#、受払#、受払年月日、摘要区分、数量、単価）

   受払残高（年月、拠点#、商品#、受払#、基受払#、残高数量、単価）

   残高集計（年月、拠点#、商品#、月初残高数量、当月受入数量、当月払出数量、月末残高数量、月初残高金額、当月受入金額、当月払出金額、月末残高金額）

3. 計数を格納する処理

   Eさんは、入荷又は出荷の登録ごとに行う一連の更新処理（以下、入出荷処理という）に合わせて、図5中のテーブルに入出荷を反映した最新のデータを格納する処理（以下、計数格納処理という）を行うことを考えた。

   1. 計数格納処理の概要
      • ① 入出荷の明細ごとに、「受払明細」テーブルに赤伝、黒伝を含む新規受払の行を作成する。赤伝、黒伝の発生時には、同じ年月、拠点#、商品#で、その受払よりも先の行を全て削除した上で、入出荷の明細から行を再作成する。これを洗替えという。
      • ② ①によって変更が必要になる「受払残高」テーブルの行を全て削除した上で、再作成する。
      • ③ 変更対象の計数を集計して「残高集計」テーブルの行を追加又は更新する。
      • ④ 計数は、計数格納処理の開始時点で登録済の入出荷だけを反映した状態にする。
   2. 計数格納処理の処理方式検討

      Eさんは、計数格納処理の実装に当たって、次の二つの処理方式案を検討し、表7の比較表を作成した。

      案1：入出荷処理と同期して行う方式。同一トランザクション内で入出荷処理及び計数格納処理を実行する。

      案2：入出荷処理と非同期に行う方式。入出荷処理で、登録された入出荷のキー値（拠点#、入荷#、出荷#）を連携用のワークテーブル（以下、連携WTという）に溜めておき、一定時間おきに計数格納処理を実行する。

      • 入出荷処理では、トランザクション内で一連の更新処理を行い、最後に連携WTに行を追加してトランザクションを終了する。
      • 計数格納処理では、実行ごとに次のように処理する。
        1. 連携WT全体をロックし、連携WTの全行を処理用のワークテーブル（以下、処理WTという）に追加後、連携WTの全行を削除してコミットする。
        2. 処理WT、入荷、入荷明細、出荷、及び出荷明細から必要な情報を取得し、年月、拠点#、商品#の同じ行ごとに、まとめて次のように処理する。
           • 赤伝、黒伝がなければ、登録TSの順に受払を作成する。
           • 赤伝、黒伝があれば、洗替えの起点となる行を1行選択し、その行に対応する受払を作成する。そして、起点となる行を基に、入荷、入荷明細、出荷、出荷明細から対象となる行を入荷年月日又は出荷年月日、登録TSの順に取得して洗替えを行う。
        3. 処理WTの全行を削除してコミットする。

   表7 処理方式案の比較表

   評価項目	案1	案2
   分析データの鮮度	○常に最新	△一定時間ごとに最新
   全体的な処理時間	△入出荷処理の処理時間増加	○変わらない
   計数格納処理エラーの影響	△入出荷処理に影響あり	○入出荷処理に影響なし

   表7を基に、処理方式案を次のように判断した。

   1. 分析データの鮮度については、どちらの案でも依頼3の要件を満たす。
   2. 入出荷処理への影響について、表5において、2023-10-03に次のそれぞれの出荷の登録を仮定して、「受払明細」テーブルへの追加及び削除行数を調べることで、追加処理による遅延の大きさを推測した。
      • 09-26の出荷数量40の出荷明細を追加入力すると、次月繰越の2行を削除、出荷1行及び新たな次月繰越1行の2行を追加することになる。
      • 09-04の出荷を取り消し赤伝を追加すると、「受払明細」テーブルに合計で11行の削除、12行の追加を行うことになる。

      案1では、特に出荷の赤伝、黒伝から受払を作成する場合に、追加処理による入出荷処理の遅延が大きくなる。案2では、①連携WTに溜まった入出荷情報をまとめて処理することで、計数格納処理における出荷の赤伝、黒伝の処理時間を案1よりも短縮できる。

   3. 案1では入出荷処理の性能及び計数格納処理エラーの業務への影響が大きいことから、案2を採用することにした。なお、導入に先立って、②計数格納処理が正しく動作することを検証することにした。
4. 分析データの検証

   Eさんは、計数格納処理を実行して得たデータを用いて、ある拠点、商品の過去12か月の在庫回転率を時系列に取得して表8を得た。一定の方法で、数量、金額それぞれの在庫回転率を母集団とする外れ値検定を行ったところ、2023-09の金額の在庫回転率だけが外れ値と判定された。外れ値は、業務上の要因によって生じる場合もあれば、入力ミスなどによって生じる異常値の場合もある。

   表8 ある拠点、商品の在庫回転率（2022-10〜2023-09）

   在庫回転率	2022年			2023年
   	10月	11月	12月	1月	2月	3月	4月	5月	6月	7月	8月	9月
   数量	0.86	0.84	0.87	0.93	0.88	0.86	0.94	0.97	0.85	0.76	0.93	0.88
   金額	0.84	0.83	0.86	0.96	0.88	0.86	0.94	0.97	0.88	0.80	0.93	0.18

   表8について、Eさんは次のように推論した。

   1. ① 数量と金額の在庫回転率は、ほぼ同じ傾向で推移するが、材料費の値上がりなどに起因して、製造原価が上昇する傾向にあるとき、金額による在庫回転率はmする傾向がある。
   2. ② 2023-09の数量の在庫回転率は前月とほぼ同じ水準であるにもかかわらず、金額の在庫回転率が極端に低い値になっていることから、異常値であることが疑われる。
   3. ③ この推論を裏付けるには、「受払明細」テーブルから当該年月、拠点、商品の一致する行のうち、「摘要区分 = 'n'」の行のoに不正な値がないかどうかを調べればよい。
5. 概念データモデルの変更

   図5のテーブルをエンティティタイプ、列名を属性名として、概念データモデルに追加する。Eさんは、追加するエンティティタイプ間及び図1中のエンティティタイプとの間のリレーションシップについて、追加するエンティティタイプの外部キーを参照先のエンティティタイプを表9の形式で整理した。