応用情報技術者 2016年 春期 午後 問06
コンビニエンスストアにおけるデータウェアハウス構築及び分析に関する次の記述を読んで、設問1〜4に答えよ。
W社は、コンビニエンスストアを全国展開する企業である。店舗ごとの売上を分析するために、データウェアハウスを構築することになった。
〔売上ファクト表の作成〕
売行きが悪い商品を見つけるために、販売実績と在庫実績のデータを1日単位で集計して売上ファクト表を作成する。
販売実績と在庫実績のデータは一つのデータベースによって管理されており、新たに追加するデータウェアハウスのデータも同じデータベース内に格納する。データベースのE-R図の抜粋を図1に、各エンティティの概要を表1に示す。
このデータベースでは、E-R図のエンティティ名を表名にし、属性名を列名にして、適切なデータ型で表定義した関係データベースによって、データを管理する。
売上ファクト表に挿入するデータを抽出するSQL文を図2に示す。
なお、店舗に在庫はあるが販売実績がない商品は日間販売数量を0とする。関数COALESCE(A, B)は、AがNULLでないときはAを、AがNULLのときはBを返す。
〔売行きが悪い商品分類の一覧の作成〕
店舗ごとの月間の売行きが悪い商品分類の一覧を作成するために、図3のSQL文を作成した。一覧は、売上年月が新しいものから、店舗IDを昇順にして、平均在庫数量が多い順に表示させる。
なお、関数TO_YYYYMMは日付型の引数を受け、年月を6文字の文字列として返す。
↩設問3
↩設問3〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕
図3のSQL文を、過去の実績データを用いてテストしたところ、複数の商品分類の平均販売数量に誤った値が見つかった。そこで、幾つかの店舗における販売及び在庫管理の運用方法を確認したところ、店舗や商品によって在庫数量を記録する頻度にばらつきがあることが判明した。ある店舗では、販売実績が少ない商品は1日3回ではなく、1週間に1回だけ、在庫数量を記録していた。この点に注目して、処理を見直すことにした。まず、①図2中のある副問合せを抜き出して、その結果を新たに作成した表に格納する。次に、この表に②不足しているデータを追加する。図2中のある副問合せをこうして得られた表と置き換えることで、問題を解決することができた。
設問1：
問題文を見る模範解答
a：販売時単価
b：←
c：←
解説
解答の導き方
-
問題文で着目すべき箇所を押さえる
-
a の決定（属性名）
- 「販売詳細」に関する記述「顧客に販売した商品の数量や販売単価を記録」より、販売の都度の単価を保存する属性が必要であることが確定します。
- マスタ側の「単価」と区別するため、取引時点の単価を表す語として適切なのは 販売時単価 です。よって a は 販売時単価 になります。
- 補足説明：同じ販売内で商品ごとに単価が異なったり、マスタの単価が後で変更されたりしても、売上の正しい履歴を残すために販売明細側に単価を持たせます。
-
b の決定（商品 と 販売詳細 の関連の向き）
- 図補助の属性記述に「商品ID (FK → 商品.商品ID)」が販売詳細側にあることから、販売詳細側が外部キーで商品を参照している＝複数の販売詳細が同一の商品を参照できる、つまり 商品 が1側、販売詳細 が 多 側であると判断できます。
- 図1の凡例で 1対多 を表す記号は矢印です（凡例：→：1対多）。図の配置に合わせて、1側を示す矢尻が左向きになる位置が b なので、b は ← です。
-
c の決定（商品分類 と 商品 の関連の向き）
- 商品側に「商品分類ID (FK → 商品分類.商品分類ID)」があることから、商品が外部キーで商品分類を参照している＝複数の商品が同一の商品分類に属し得る、つまり 商品分類 が1側、商品 が 多 側であると判断できます。
- 同様に凡例に従い、図上の該当位置に付ける矢印は ← となるため、c は ← です。
-
最終的な記入（まとめ）
- a：販売時単価
- b：←
- c：←
誤りやすいポイント
- 「単価」をそのまま商品マスタの属性名（単価）で埋めてしまうミス。販売履歴としては取引時点の価格を保持する必要があるため、販売明細側に販売時単価等の属性を置く点を忘れないこと。
- 矢印の向きの判断を図上の記号だけで行い、外部キーの向き（どちらがFKを持っているか）を見落とすミス。まず外部キーで「多」側を特定することが確実です。
- 属性を誤ったエンティティに付けてしまう（例：販売時の単価を販売エンティティに付与する）。販売と販売詳細の役割を整理して、行単位で変わり得る情報は販売詳細に置きます。
- 凡例の記号意味と実際の図中の矢印向きを混同するミス。凡例で「1対多」を確認し、どちらが1側か（FKの有無で判定）→図上で該当する向きの記号を記入する手順を踏むと安全です。
FAQ
Q: なぜ属性名を「販売時単価」としたのですか？
A: 表1が「販売詳細は顧客に販売した商品の数量や販売単価を記録する」としているため、販売時の単価を保存する属性が必要です。商品マスタの「単価」とは別に、取引発生時の価格を残す目的で「販売時単価」と名付けるのが適切です。
A: 表1が「販売詳細は顧客に販売した商品の数量や販売単価を記録する」としているため、販売時の単価を保存する属性が必要です。商品マスタの「単価」とは別に、取引発生時の価格を残す目的で「販売時単価」と名付けるのが適切です。
Q: 矢印の向きは具体的にどう判断すれば良いですか？
A: まずどちらが多側かを外部キーの向きで判定します（外部キーを持つ方が多側）。次に図の凡例で1対多の記号を確認し、図上の該当位置にその向きの記号を置きます。問題文の図では1側を示す矢印が左向きになる位置に記載するため、bc は ← です。
A: まずどちらが多側かを外部キーの向きで判定します（外部キーを持つ方が多側）。次に図の凡例で1対多の記号を確認し、図上の該当位置にその向きの記号を置きます。問題文の図では1側を示す矢印が左向きになる位置に記載するため、bc は ← です。
Q: 販売と販売詳細、どちらに商品単価を持たせるべきですか？
A: 商品ごとの取引単位で変動する情報（行単位の価格）は販売詳細に持たせます。販売（トランザクション）自体は複数行を持つ可能性があるため、行ごとの価格は販売詳細が適切です。
A: 商品ごとの取引単位で変動する情報（行単位の価格）は販売詳細に持たせます。販売（トランザクション）自体は複数行を持つ可能性があるため、行ごとの価格は販売詳細が適切です。
関連キーワード: E-R図、外部キー、1対多関係、属性設計、正規化
設問2：
問題文を見る模範解答
d：LEFT OUTER JOIN
e：ST.店舗ID = SS.店舗ID
f：ST.商品ID = SS.商品ID
解説
解答の論理構成
-
前提条件の整理
問題文には次の指示があります。
「店舗に在庫はあるが販売実績がない商品は日間販売数量を0とする。」
この要件から、在庫（ST）の行は必ず残し、販売（SS）の行がなくても結果集合に含める必要があります。 -
JOIN 方式の決定
・在庫を基準に販売をくっつける――販売がなくても在庫は残す。
・したがって、在庫側（ST）が “残る” 外部結合を選択する。
⇒ d には「LEFT OUTER JOIN」が入る。 -
結合キーの特定
図2の ON 句にはすでに
ST.確認年月日 = SS.販売年月日
が示されており、残るキーは “店舗” と “商品” です。
在庫と販売を1日単位で紐付けるには、
・店舗を一致させる条件
・商品を一致させる条件
が不可欠です。別名付きで書くと
ST.店舗ID = SS.店舗IDとST.商品ID = SS.商品IDになります。
店舗か商品のどちらを先に書いても意味は変わらないので、eとfは順不同と指定されています。 -
まとめ
・d LEFT OUTER JOIN
・e ST.店舗ID = SS.店舗ID
・f ST.商品ID = SS.商品ID
誤りやすいポイント
- INNER JOIN を選んでしまい、販売実績がない商品が欠落する
→ 要件「販売実績がない商品は日間販売数量を0」に反します。 - RIGHT OUTER JOIN と混同する
→ 結合の基準テーブルが逆転し、在庫のない販売だけが残る恐れがあります。 - 別名を付け忘れる
→ 「表の列名には必ずその表の別名を付けて答えよ」という指示違反になります。 - 結合キーを1つだけにする
→ 店舗・商品を両方指定しないと、異なる店舗や商品のデータが混ざり集計値が壊れます。
FAQ
Q: LEFT JOIN と LEFT OUTER JOIN の違いはありますか？
A: 多くの RDBMS では同義ですが、ANSI SQL の正式キーワードは LEFT OUTER JOIN です。試験では明示的に “OUTER” を書いておくと安全です。
A: 多くの RDBMS では同義ですが、ANSI SQL の正式キーワードは LEFT OUTER JOIN です。試験では明示的に “OUTER” を書いておくと安全です。
Q: 結合条件に WHERE 句を使ってはいけませんか？
A: ON 句で書くのが鉄則です。WHERE 句に混在させると外部結合の意味が変わり、除外される行が発生します。
A: ON 句で書くのが鉄則です。WHERE 句に混在させると外部結合の意味が変わり、除外される行が発生します。
Q: COALESCE 関数はなぜ必要なのですか？
A: LEFT OUTER JOIN で対応行がない場合、SS.日間販売数量 は NULL になります。COALESCE により NULL を 0 に置き換え、集計や表示を正しく行えます。
A: LEFT OUTER JOIN で対応行がない場合、SS.日間販売数量 は NULL になります。COALESCE により NULL を 0 に置き換え、集計や表示を正しく行えます。
関連キーワード: 外部結合, NULL処理, 集約関数, データウェアハウス, 正規化
設問3：
問題文を見る図3中のgに入れる適切な字句又は式を答えよ。
なお、表の列名には必ずその表の別名を付けて答えよ。
模範解答
g：ORDER BY SF.売上年月 DESC, SF.店舗ID ASC, 平均在庫数量 DESC
解説
解答の論理構成
- 並び替えの条件は【問題文】の「売上年月が新しいものから、店舗IDを昇順にして、平均在庫数量が多い順に表示させる。」という記述に明示されています。
- SQL で並び順を指定するには ORDER BY 句を用い、降順は DESC、昇順は ASC を付与します。
- 現在の SELECT 句では、年月を表す列が SF.売上年月、店舗を示す列が SF.店舗ID、平均在庫を示す列が 平均在庫数量 という別名で取得されています。
- したがって、条件をそのままSQLの句に置き換えると
• 年月：SF.売上年月 DESC
• 店舗：SF.店舗ID ASC
• 在庫：平均在庫数量 DESC
となります。 - 以上より、g に入る字句は
sql ORDER BY SF.売上年月 DESC, SF.店舗ID ASC, 平均在庫数量 DESC
誤りやすいポイント
- SF.売上年月 を降順にするのを忘れて、最新月が最後に並ぶ。
- 平均在庫数量 を別名のまま使わず、AVG(SF.日間在庫数量) と書き直してしまう。
- 並び替えの優先順位を誤り、SF.店舗IDを先頭に置いてしまう。
- 別名に表別名SF. を付け忘れ、SQLシンタックスエラーとなる。
FAQ
Q: 別名 平均在庫数量 には表別名を付けられませんか？
A: 集計結果に付けたカラム別名には表別名を付けません。ORDER BY で参照する場合もカラム別名のみ記述します。
A: 集計結果に付けたカラム別名には表別名を付けません。ORDER BY で参照する場合もカラム別名のみ記述します。
Q: GROUP BY で指定した列も ORDER BY に書かなければなりませんか？
A: 必須ではありません。ORDER BY は表示順を制御するだけなので、集計列以外でも自由に指定できます。
A: 必須ではありません。ORDER BY は表示順を制御するだけなので、集計列以外でも自由に指定できます。
Q: 別名SFのサブクエリを省略し、直接 売上ファクト を参照してもよいですか？
A: 年月変換関数TO_YYYYMMの結果を再利用するためにサブクエリSFを設けています。直接参照すると同じ式を複数回書くことになり、可読性が落ちます。
A: 年月変換関数TO_YYYYMMの結果を再利用するためにサブクエリSFを設けています。直接参照すると同じ式を複数回書くことになり、可読性が落ちます。
関連キーワード: ORDER BY, ソート順序, カラム別名, DESC ASC, 集計関数
設問4：〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕について、(1)、(2)に答えよ。
問題文を見る模範解答
α
解説
解答の論理構成
- 問題文には、誤った平均値の原因を解消するために
「まず、①図2中のある副問合せ を抜き出して、その結果を新たに作成した表に格納する。」
とあります。 - ここで抜き出す副問合せは、後続の手順で
「この表に ②不足しているデータ を追加する」
と述べられており、
すなわち “本来存在すべきなのに欠けている在庫情報を補完する” ためのものです。 - 図2の SQL は、日単位の在庫・販売情報を集計し、最終的に JOIN しています。
a. 片方の副問合せは在庫表を GROUP BY して日間在庫数量を求めるもの
b. もう一方の副問合せは販売表＋販売詳細表を GROUP BY して日間販売数量を求めるもの - 問題文は「店舗や商品によって在庫数量を記録する頻度にばらつきがある」と指摘しており、
補完対象は “在庫” であると読み取れます。
販売データはPOSシステムで確実に記録される一方、在庫データは欠損しやすいからです。 - したがって、抜き出すべき副問合せは在庫を集計している側、すなわち図2の [α] に位置する副問合せです。
- よって解答は α となります。
誤りやすいポイント
- 「不足しているデータ＝販売データ」と早合点し、[β] を選んでしまう。実際には販売数量はPOSにより自動収集されるため欠損しにくい。
- 副問合せを “外側 SELECT の直前” といった位置情報で判断しようとして混乱する。α/β という記号で指定されているので、内容（在庫か販売か）に注目することが重要。
- ②の「不足しているデータ」を “NULL行の削除” と解釈し、処理の意図を取り違える。本質はNULLを0に置き換えるのではなく、そもそも存在しない在庫行を追加すること。
FAQ
Q: 販売データにも欠損が起こる可能性はありませんか？
A: POSで自動連携される想定なので欠損は非常に稀です。問題文でも在庫記録の頻度ばらつきのみが指摘されています。
A: POSで自動連携される想定なので欠損は非常に稀です。問題文でも在庫記録の頻度ばらつきのみが指摘されています。
Q: COALESCE でNULLを0にしているのに、なぜ行追加が必要なのですか？
A: COALESCE は “行が存在している” ことが前提です。行自体がなければNULL判定の対象にならないため、欠損行を補完する処理が必要となります。
A: COALESCE は “行が存在している” ことが前提です。行自体がなければNULL判定の対象にならないため、欠損行を補完する処理が必要となります。
Q: 抜き出した在庫副問合せを表化する利点は？
A: 先に在庫だけを独立して持つことで、後からスクリプトやバッチで欠損行を効率的に INSERT でき、月次分析時の平均値が正しくなります。
A: 先に在庫だけを独立して持つことで、後からスクリプトやバッチで欠損行を効率的に INSERT でき、月次分析時の平均値が正しくなります。
関連キーワード: サブクエリ, JOIN, 集計関数, NULL処理
設問4：〔売行きが悪い商品分類の一覧を作成するSQL文の不具合〕について、(1)、(2)に答えよ。
問題文を見る（2）本文中の下線②とはどのようなデータか。40字以内で述べよ。
なお、販売及び在庫管理の運用方法は変更しないこと。
模範解答
在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ
解説
解答の導き方
設問は「本文中の下線②とはどのようなデータか」を問うものです。結論（設問の答え）を先に示します。
在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ
在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ
以下に、その結論に至る手順を段階的に示します。
-
問題箇所の把握
本文に「ある店舗では、販売実績が少ない商品は1日3回ではなく、1週間に1回だけ、在庫数量を記録していた。」とあります。これにより、在庫テーブルには店舗・商品ごとに日付の欠損（日単位で在庫が記録されていない日）が生じることが分かります。 -
図2の処理の確認（在庫側の集計）
図2の副問合せに「（SELECT SC.確認年月日, SC.店舗ID, SC.商品ID, AVG(SC.在庫数量) AS 日間在庫数量 FROM 在庫 SC GROUP BY SC.確認年月日, SC.店舗ID, SC.商品ID） ST」があります。これは在庫テーブルに存在する確認年月日だけを基に日間在庫数量を作る処理です。したがって、在庫の記録がない日はSTに行が生成されません。 -
図2の結合と COALESCE の意味の正しい理解
図2は在庫側（ST）を基点に販売側（SS）と結合し、さらに COALESCE(SS.日間販売数量, 0) によってSTに対応する販売が無い場合に販売数量を0にしています。重要なのは、COALESCE の処理は「STの行が存在する場合に適用される」ことであり、ST自体が存在しない（=在庫が記録されていない）日はCOALESCEの対象にならず、当該日付の行は結果に現れない点です。 -
欠損が分析に与える影響
図3では月ごと・店舗ごと・商品分類ごとに AVG(SF.日間販売数量) や AVG(SF.日間在庫数量) を計算します。STに日付が欠けると、平均の分母（日数）が減り、平均値が偏ります。したがって、在庫を記録していない日について「在庫数量」を補っておかないと、平均販売数量・平均在庫数量の算出が誤差を生じます。 -
抜本的な対応方針（問題文の処理に一致）
問題文は「図2中のある副問合せを抜き出して、その結果を新たに作成した表に格納する。次に、この表に不足しているデータを追加する」と述べています。ここで追加すべき「不足しているデータ」とは、在庫が記録されていない日についての日次行を補うデータであり、実績を用いて当該日の在庫数量を推定・導出した値です。これを補うことでST相当の表に日付の欠損がなくなり、図3の平均計算が正しく行えるようになります。
以上の論理展開から、下線②は「在庫数量を記録していない日の商品の在庫数量を実績から導出したデータ」であると判断できます。
誤りやすいポイント
- COALESCE が在庫未記録日を自動的に販売数量0にする、という誤解。COALESCE はSTの行がある場合にのみ働き、ST自体が無ければ効果がない点を見落としやすいです。
- 「影響は在庫平均だけ」と考える誤り。日付欠損は平均の分母を変えるため、平均販売数量・平均在庫数量の両方に影響します。
- 図2のどちらの副問合せが問題かを見誤る。今回の問題は在庫側の集計（ST）から来る欠損が原因です。
- 対処を販売側の補完で済ませようとする誤り。販売は実績がある日だけ記録されるため、在庫側の日付欠損を埋めない限り分母の整合が取れません。
- 補完手法を無批判に適用すると実データの分布を歪める点。補完方法は業務ルールに合わせて選ぶ必要があります。
FAQ
Q: 実績から導出する具体的な方法はどうすればよいですか？
A: 代表的な方法として、直近観測値を日次に持ち越す方法（前日値の複製）や、観測値間を線形補間する方法、あるいは同一週・類似商品からの推定などがあります。業務要件（廃棄や入荷のタイミング等）に照らして妥当な手法を選んでください。
A: 代表的な方法として、直近観測値を日次に持ち越す方法（前日値の複製）や、観測値間を線形補間する方法、あるいは同一週・類似商品からの推定などがあります。業務要件（廃棄や入荷のタイミング等）に照らして妥当な手法を選んでください。
Q: なぜ販売側を補完しないのですか？
A: 図2では販売が発生した日は販売テーブルに実績として記録されるため、販売側の欠測は少ない想定です。問題の主因は在庫記録の頻度不均一による日付欠損なので、在庫側の補完が優先されます。なお、販売側にも欠測があるなら同様の考え方で補完対象になります。
A: 図2では販売が発生した日は販売テーブルに実績として記録されるため、販売側の欠測は少ない想定です。問題の主因は在庫記録の頻度不均一による日付欠損なので、在庫側の補完が優先されます。なお、販売側にも欠測があるなら同様の考え方で補完対象になります。
Q: データウェアハウス側での別の対策はありますか？
A: 日付の全組合せ（カレンダーテーブルと店舗×商品 の組合せを作る）を基点にして在庫・販売を左結合し、欠損日を明示的に作ってから補完する方法がよく使われます。これにより分析時の分母を安定させられます。
A: 日付の全組合せ（カレンダーテーブルと店舗×商品 の組合せを作る）を基点にして在庫・販売を左結合し、欠損日を明示的に作ってから補完する方法がよく使われます。これにより分析時の分母を安定させられます。
関連キーワード: 欠損データ、データ補完、外部結合、日次集計、集計の分母整合性
