W社は、コンビニエンスストアを全国展開する企業である。
店舗ごとの売上を分析するために、データウェアハウスを構築することになった。
〔売上ファクト表の作成〕
売行きが悪い商品を見つけるために、販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。
販売実績と在庫実績のデータは一つのデータベースによって管理されており、新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。
データベースのE-R図の抜粋を図1に、各エンティティの概要を表1に示す。
このデータベースでは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。
売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。
なお、店舗に在庫はあるが販売実績がない商品は日間販売量を0とする。関数COALESCE(A,B)はAがNULLでないときはAを、AがNULLのときはBを返す。
SELECT ST.確認年月日 , ST.店舗ID , ST.商品ID , COALESCE(SS.日間販売数量, 0) , ST.日間在庫数量 FROM (SELECT SC.確認年月日 , SC.店舗ID , SC.商品ID , AVG(SC.在庫数量) AS 日間在庫数量 FROM 在庫 SC GROUP BY SC.確認年月日 , SC.店舗ID , SC.商品ID) ST d (SELECT SL.販売年月日 , SL.店舗ID , SL.商品ID , SUM(SD.販売数量) AS 日間販売数量 FROM 販売 SL INNER JOIN 販売詳細 SD ON SL.販売ID = SD.販売ID GROUP BY SL.販売年月日 , SL.店舗ID , SD.商品ID ) SS ON ST.確認年月日 = SS.販売年月日 AND e AND f
図2 売上ファクト表に挿入するデータを抽出するSQL文
〔売行きが悪い商品分類の一覧の作成〕
店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために、図3のSQL文を作成した。
一覧は、売上年月が新しいものから、店舗IDを昇順にして、平均在庫数量が多い順に表示させる。
なお、関数TO_YYYYMMは日付型の引数を受け、年月を6文字の文字列として返す。
SELECT
SF.売上年月
, SF.店舗ID
, IT.商品分類ID
, AVG(SF.日間販売数量) AS 平均販売数量
, AVG(SF.日間在庫数量) AS 平均在庫数量
, ST.日間在庫数量
FROM
(SELECT
TO_YYYYMM(SA.売上年月日) AS 売上年月
, SA.店舗ID
, SA.商品ID
, SA.日間販売数量
, SA.日間在庫数量
FROM 売上ファクト SA) SF
INNER JOIN 商品 IT ON SF.商品ID = IT.商品ID
GROUP BY
SF.売上年月
, SF.店舗ID
, IT.商品分類ID
g
図3 売行きが悪い商品分類の一覧を作成するSQL文
〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕
図3のSQL文を、過去の実績データを用いてテストしたところ、複数の商品分類の平均販売数量に誤った値が見つかった。
そこで、幾つかの店舗における販売及び在庫管理の運用方法を確認したところ、店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。
ある店舗では、販売実績が少ない商品は1日3回ではなく、1週間に1回だけ、在庫数量を記録していた。
この点に注目して、処理を見直すことにした。
まず、①図2中のある副問合せを抜き出して、その結果を新たに作成した表に格納する。
次に、この表に②不足しているデータを追加する。
図2中のある副問合せをこうして得られた表と置き換えることで、問題を解決することができた。
〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕について、(1)、(2)に答えよ。
(1) 本文中の下線①に該当する副問合せは図2中のどの位置にあるか。
α又はβで答えよ。
(2) 本文中の下線②とはどのようなデータか。40字以内で述べよ。
なお、販売及び在庫管理の運用方法は変更しないこと。
(1)
下線①図2中のある副問い合わせとは
αは在庫の副問い合わせ
βは販売の副問い合わせ
です。
問題文を読むと、「店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した」とあり、在庫のデータに問題があることがわかります。従って「α」の在庫の副問い合わせが下線①に該当します!
(2)
問題文を読むと、「1日3回在庫を記録するところ、販売の少ない商品は1週間に1回だけ在庫を記録していた」とあります。
このデータが抜けているので、平均すると正確な値が計算できず、整合性が取れません。
「在庫数量を記録していない日は商品の在庫数量は販売実績からわかる」ので、この分のデータを追加することでデータの整合性を保ちます。
上記をまとめると
「在庫数量を記録していない日の商品の在庫数量を実績から導出データ(IPA公式)」となります!