令和5年度 秋期 応用情報技術者試験 午後問題 問6 在庫管理システムのE-R図・ウィンドウ関数・SQL

在庫管理システムに関する次の記述を読んで，設問に答えよ。

M社は，ネットショップで日用雑貨の販売を行う企業である。M社では，在庫管理について次の課題を抱えている。

• 在庫が足りない商品の注文を受けることができず，機会損失につながっている。
• 商品の仕入れの間隔や個数を調整する管理サイクルが長く，余計な在庫を抱える傾向にある。

〔現状の在庫管理〕

現在，在庫管理を次のように行っている。

• 商品の注文を受けた段階で，出荷先に最も近い倉庫を見つけて，その倉庫の在庫から注文個数を引き当てる。この引き当てられた注文個数を引当済数という。各倉庫において，引き当てられた各商品単位の個数の総計を引当済総数という。
• 実在庫数から引当済総数を引いたものを在庫数といい，在庫数以下の注文個数の場合だけ注文を受け付ける。
• 商品が倉庫に入荷すると，入荷した商品の個数を実在庫数に足し込む。
• 倉庫から商品を出荷すると，出荷個数を実在庫数から引くとともに引当済総数からも引くことで，引き当ての消し込みを行う。

M社では，月末の月次バッチ処理で毎月の締めの在庫数と売上個数を記録した分析用の表を用いて，商品ごとの在庫数と売上個数の推移を評価している。
また，期末に商品の在庫回転日数を集計して，来期の仕入れの間隔や個数を調整している。

M社では，商品の在庫回転日数を，簡易的に次の式で計算している。

[図表: bb_30_1]

在庫回転日数の計算において，現状では，期間内の平均在庫数として 12 か月分の締めの在庫数の平均値を使用している。

現状の在庫管理システムの E-R 図（抜粋）を図 1 に示す。

在庫管理システムのデータベースでは，E-R図のエンティティ名を表名にし，属性名を列名にして，適切なデータ型で表定義した関係データベースによって，データを管理している。

〔在庫管理システム改修内容〕

課題を解決するために，在庫管理システムに次の改修を行うことにした。

• 在庫数が足りない場合は，倉庫からは引き当てず，予約注文として受け付ける。なお，予約注文ごとに商品を発注することで，注文を受けた商品の個数が入荷される。
• 商品の仕入れの間隔や個数を調整する管理サイクルを短くするために，在庫の評価を月次から日次の処理に変更して，毎日の締めの在庫数と売上個数を在庫推移状況エンティティに記録する。

現状では，在庫数が足りない商品の予約注文を受けようとしても，在庫引当を行うと実在庫数より引当済総数の方が多くなってしまい，注文に応えられない。そこで，予約注文の在庫引当を商品の入荷のタイミングにずらすために，E-R図に予約注文用の二つのエンティティを追加することにした。追加するエンティティを表1に，改修後の在庫管理システムの E-R 図（抜粋）を図2に示す。

表1 追加するエンティティ

エンティティ名	内容
引当情報	予約注文を受けた商品の個数と入荷済となった商品の個数を管理する。
引当予定	予約注文を受けた商品の，未入荷の引当済数の総計を管理する。

（※図2のエンティティ構成のテキスト化）
倉庫

• 倉庫コード
• 倉庫名
• 所在地

a

在庫推移状況

• 年
• 月
• d
• 倉庫コード
• 商品コード
• 在庫数
• 売上個数

入荷

• 入荷番号
• 倉庫コード
• 入荷日

出荷

• 出荷番号
• 倉庫コード
• 出荷日
• 出荷先情報

入荷明細

• 入荷番号
• 入荷明細番号
• 引当番号
• 商品コード
• 個数

出荷明細

• 出荷番号
• 出荷明細番号
• 商品コード
• 個数

b

• 引当番号
• 倉庫コード
• 商品コード
• 引当済数
• 入荷済数

商品

• 商品コード
• 商品名
• 型番
• 標準単価

在庫

• 倉庫コード
• 商品コード
• 実在庫数
• 引当済総数

c

• 倉庫コード
• 商品コード
• 未入荷引当済総数

注記 新規に追加したエンティティは太枠で表す。

図2 改修後の在庫管理システムの E-R 図（抜粋）

在庫管理システムにおける予約注文を受けた商品の個数に関する処理内容を表2に示す。

表2 在庫管理システムにおける予約注文を受けた商品の個数に関する処理内容

処理タイミング	処理内容
予約注文を受けたとき	引当情報エンティティのインスタンスを生成して，引当済数には注文を受けた商品の個数を，入荷済数には 0 を設定する。 引当予定エンティティの未入荷引当済総数に注文を受けた商品の個数を足す。
予約注文された商品が入荷したとき	eエンティティの未入荷引当済総数から入荷した商品の個数を引く。 fエンティティの実在庫数と引当済総数に入荷した商品の個数を足す。 入荷した商品の個数をgエンティティの個数に設定し，引当情報エンティティのhに足す。
予約注文された商品を出荷したとき	出荷した商品の個数を出荷明細エンティティの個数に設定し，在庫エンティティの商品の実在庫数及び引当済総数から引く。

〔在庫の評価〕

より正確かつ迅速に在庫回転日数を把握するために，在庫推移状況エンティティから，期間を1週間（7日間）として，倉庫コード，商品コードごとに，各年月日の6日前から当日までの平均在庫数及び売上個数で在庫回転日数を集計することにする。
可読性を良くするために，SQL文にはウィンドウ関数を使用することにする。
ウィンドウ関数を使うと，FROM句で指定した表の各行ごとに集計が可能であり，各行ごとに集計期間が異なるような移動平均も簡単に求めることができる。ウィンドウ関数で使用する構文（抜粋）を図3に示す。

<ウィンドウ関数>::=
  <ウィンドウ関数名>(<列>) OVER {<ウィンドウ名> | (<ウィンドウ指定>)}

<WINDOW 句>::=
  WINDOW <ウィンドウ名> AS (<ウィンドウ指定>) [{, <ウィンドウ名> AS (<ウィンドウ指定>)}...]

<ウィンドウ指定>::=
  [<PARTITION BY 句>] [<ORDER BY 句>] [<ウィンドウ枠>]

<PARTITION BY 句>::=
  PARTITION BY <列> [{, <列>}...]

注記1 OVER の後に(<ウィンドウ指定>)を記載する代わりに, WINDOW 句で名前を付けて, <ウィンドウ名>で参照することができる。
注記2 PARTITION BY 句は指定した列の値ごとに同じ値をもつ行を部分集合としてパーティションにまとめるオプションである。
注記3 ウィンドウ枠の例として, ROWS BETWEEN n PRECEDING AND CURRENT ROW と記載した場合は, n 行前(n PRECEDING)から現在行(CURRENT ROW)までの範囲を対象として集計することを意味する。
注記4 ...は, 省略符号を表し, 式中で使用される要素を任意の回数繰り返してもよいことを示す。

図3 ウィンドウ関数で使用する構文 (抜粋)

ウィンドウ関数を用いて，倉庫コード，商品コードごとに，各年月日の6日前から当日までの平均在庫数及び売上個数を集計するSQL文を図4に示す。

SELECT 年, 月, 日, 倉庫コード, 商品コード,
       AVG(在庫数) <span id="q6_blank_i" class="blank-label">i</span> 期間定義 AS 平均在庫数,
       SUM(売上個数) <span id="q6_blank_i" class="blank-label">i</span> 期間定義 AS 期間内売上個数
FROM 在庫推移状況
WINDOW 期間定義 AS (
                PARTITION BY 倉庫コード, 商品コード
                <span id="q6_blank_j" class="blank-label">j</span> 年, 月, 日 ASC
                ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                )

図4 倉庫コード, 商品コードごとに, 各年月日の6日前から当日までの平均在庫数及び売上個数を集計するSQL文