2016年 春期 応用情報技術者試験 問6

コンビニエンスストアにおけるデータウェアハウス構築及び分析

W社は、コンビニエンスストアを全国展開する企業である。店舗ごとの売上を分析するために、データウェアハウスを構築することになった。

売上ファクト表の作成

売行きが悪い商品を見つけるために、販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。

販売実績と在庫実績のデータは一つのデータベースによって管理されており、新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。データベースのE-R図の抜粋を図1に、各エンティティの概要を表1に示す。

注記：属性名の実線の下線はキー、破線の下線は外部キーを示す。主キーの実線が付いている属性名には、外部キーの破線を付けない。
表1 各エンティティの概要
エンティティ名概要
店舗コンビニエンスストアの店舗マスタ
商品分類弁当、清涼飲料、雑誌などの商品分類マスタ
商品商品の単価や商品分類などを管理する商品マスタ
販売顧客に商品を販売した実績を記録
販売詳細顧客に販売した商品の数量や販売時単価を記録
在庫1日3回、商品の入荷及び廃棄を行い、店舗が取り扱う商品の一覧と照らして、廃棄数量と在庫数量を記録
売上ファクト販売実績と在庫実績のデータを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, SD.商品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 平均在庫数量
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中のある副問合せをこうして得られた表と置き換えることで、問題を解決することができた。

出典：平成28年度 春期 応用情報技術者試験 午後問題