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

PR広告

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

問6 コンビニエンスストアにおけるデータウェアハウス構築及び分析に関する次の記述を読んで、設問1~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中のある副問合せをこうして得られた表と置き換えることで、問題を解決することができた。

設問2

図2中のdfに入れる適切な字句又は式を答えよ。

なお、表の列名には必ずその表の別名を付けて答えよ。

解説

SQLの問題です。

αは在庫データを店舗・商品・日ごとに取得する副問い合わせ(サブクエリ)です。βは販売データを店舗・商品・日ごとに取得する副問い合わせ(サブクエリ)です。

副問い合わせ(サブクエリ)の結果は別名をつけて1つのテーブルのように扱えます。

図2で、在庫副問い合わせはST、販売副問い合わせはSSです。

テーブルを結合するときには"JOIN"を使います。在庫(ST)に販売(SS)を結合するので"LEFT JOIN"を使います。

商品によっては、販売されなかった日があり、商品データがない場合が考えられます。

このとき内部結合(INNER JOIN)だと、商品データがない行のレコードが取得できませんが、外部結合(OUTER JOIN)にすると、データが無い場合もレコードを取得できます。

従ってdには「LEFT OUTER JOIN」が入ります。

一般的に、"LEFT JOIN"とすると、外部結合になる(OUTERが省略可能)ので、「LEFT JOIN」でも正解になると思われます。

店舗・商品・日ごとの在庫(ST)と店舗・商品・日ごとの販売(SS)の主キーとなる(ユニーク)となるのは、店舗IDと商品IDと年月日です。

年月日は在庫(ST)では確認年月日、販売(SS)では販売年月日となり、問題文で ST.確認年月日 = SS.販売年月日と記載があります。

残りは店舗IDと商品IDなので

「ST.店舗ID = SS.店舗ID」と「ST.商品ID = SS.商品ID」がON句に入ります!

efの答えとなります!(順不同)

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


タグ: ,

PR広告

フェイスブックコメント

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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