応用情報技術者試験の過去問＆キーワードを動画2443本の動画で解説！スキマ時間に動画！※2017年9月11日現在

PR広告

Adsence

平成28年度春 応用情報技術者試験 午後問6 データベース 設問4 合格率アップ！動画付き解説

問６　コンビニエンスストアにおけるデータウェアハウス構築及び分析に関する次の記述を読んで、設問１～４に答えよ。

W社は、コンビニエンスストアを全国展開する企業である。

店舗ごとの売上を分析するために、データウェアハウスを構築することになった。

〔売上ファクト表の作成〕

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

販売実績と在庫実績のデータは一つのデータベースによって管理されており、新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。

データベースのE-R図の抜粋を図1に、各エンティティの概要を表1に示す。

平成28年度春 応用情報技術者試験 午後問6 データベース 合格率アップ！動画付き解説！

平成28年度春 応用情報技術者試験 午後問6 データベース 合格率アップ！動画付き解説！

このデータベースでは、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公式）」となります！

平成28年度春 応用情報技術者試験 午後 目次


タグ: ,

亀井亮介 (2016年7月13日 05:46)

PR広告

Adsence

フェイスブックコメント

Facebook Comment

難解な応用情報技術者試験午前で80点中48点を取る優しい方法

難解な応用情報技術者試験午前で80点中48点を取る優しい方法

平成28年度春 応用情報技術者試験 午後 テキスト・動画解説

平成28年度春 応用情報技術者試験 午後

平成28年度春 応用情報技術者試験 午後 テキスト・動画解説

平成28年度春 応用情報技術者試験 午後 動画解説再生リスト

平成28年度春 応用情報技術者試験 午後 動画解説再生リスト

平成28年度春 応用情報技術者試験 午前 テキスト・動画解説

平成28年度春 応用情報技術者試験 午前

平成28年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成28年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成28年度春 応用情報技術者試験 午前 動画解説再生リスト

平成27年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成27年度秋 応用情報技術者試験 午前

平成27年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成27年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成27年度春 応用情報技術者試験 午後 テキスト・動画解説

平成27年度春 応用情報技術者試験 午後

平成27年度春 応用情報技術者試験 午後 テキスト・動画解説

平成27年度春 応用情報技術者試験 午後 動画解説再生リスト

平成27年度春 応用情報技術者試験 午後 動画解説再生リスト

平成27年度春 応用情報技術者試験 午前 テキスト・動画解説

平成27年度春 応用情報技術者試験 午前

平成27年度春 応用情報技術者試験 午前 テキスト・動画解説

平成27年度春 応用情報技術者試験 午前 動画解説再生リスト

平成27年度春 応用情報技術者試験 午前 動画解説再生リスト

平成26年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成26年度秋 応用情報技術者試験 午前

平成26年度秋 応用情報技術者試験 午前 テキスト・動画解説

平成26年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成26年度秋 応用情報技術者試験 午前 動画解説再生リスト

平成26年度春 応用情報技術者試験 午前 テキスト・動画解説

平成26年度春 応用情報技術者試験 午前

平成26年度春 応用情報技術者試験 午前 テキスト・動画解説

平成26年度春 応用情報技術者試験 午前 動画解説再生リスト

平成26年度春 応用情報技術者試験 午前 動画解説再生リスト