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

PR広告

Adsence

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

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

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中のある副問合せをこうして得られた表と置き換えることで、問題を解決することができた。

設問３

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

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

解説

SQLの問題です。問題文に並び順についての記載がありますが、SQLには記載がないのでORDER BYがgに入ると考えられます。

ORDER BY句で使う

"DESC"は降順（新しい日付順、大きい順）

"ASC"は昇順（古い日付順、小さい順）

です。問題文に並び順についての記載があります。

「売上年月が新しいものから」は"SF.売上年月日 DESC"と表現

「店舗IDを昇順」は"SF.店舗ID ASC"と表現

「平均在庫数量が多い順」は"平均在庫数量 DESC"と表現

これらを「順番に」ORDER BYに書きます。

従ってgには

「ORDER BY SF.売上年月 DESC, SF.店舗ID ASC, 平均在庫数量 DESC」

が入ります！

平成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年度春 応用情報技術者試験 午前 動画解説再生リスト