データベーススペシャリスト 2012年 午後1 問03

解答の導き方

1. 問いの要求を確認する
   問題文の分析A2は「店舗コードM001とM002の店舗において、2011年4月1日に少なくともどちらか一方の店舗で売れた商品の商品名及び販売数一覧」を求めています。したがって「どちらか一方で売れた（＝片方だけで売れていても含む）」商品を抽出する必要があります。
2. SQLの構造から (a) を決める理由
   表3の該当部分は次のようになっています（一部引用）： 「SELECT P.商品名、 U1.販売数、 U2.販売数 FROM 商品 P (a) OUTER JOIN 店舗売上 U1 ON P.商品番号 = U1.商品番号 AND U1.売上日 = ISODATE('2011-04-01') AND U1.店舗コード = 'M001' (a) OUTER JOIN 店舗売上 U2 ON P.商品番号 = U2.商品番号 AND U2.売上日 = ISODATE('2011-04-01') AND U2.店舗コード = 'M002' WHERE (b) OR (c)」

1. WHERE 条件 (b),(c) の決定
   ON で指定した条件により、その店舗でその日に売上の行がない商品は、U1/U2側の列がNULLになります。したがって「売れた」ことは、次のどちらでも判定できます（解答例も両方を正解としています）。

• 販売数 IS NOT NULL：その店舗・その日の店舗売上の行が結合されたこと（＝売上がある）を判定する。
• 販売数 > 0：売上の行があり、販売数が正であることを判定する。

1. まとめ（SQLに当てはめた形の例）
   最も明確な書き方の一例を示すと次のとおりです（(a),(b),(c) を置換）：

1. 正答（(b),(c) は順不同） a：LEFT
   b：U1.販売数 IS NOT NULL又はU1.販売数>0
   c：U2.販売数 IS NOT NULL又はU2.販売数>0

誤りやすいポイント

• INNER JOIN にしてしまう誤り
  ON 条件を WHERE に移したり、INNER JOIN にしてしまうと「両店舗で売れた商品」のみになってしまい、問題の「少なくともどちらか一方で売れた」という要件を満たしません。
• OUTER JOIN の ON と WHERE の配置ミス
  日付や店舗コードの条件を ON に入れるのが正解です。これらを WHERE に移すと LEFT OUTER JOIN の効果が失われ、意図せず行が除外されます。
• WHERE 句に何も条件を書かない、又は AND で結んでしまう
  条件がないとどちらの店舗でも売れていない商品まで出力され、AND にすると両方の店舗で売れた商品だけになります。
• LEFT と RIGHT の取り違え
  FROM の左側が基準になるため、FROM 商品 P の形なら LEFT を用いるのが自然です。RIGHT を指定すると構文上は動く場合もありますが、可読性・意図の明確さで誤解を招きやすいです。

FAQ

解答の論理構成

1. 分析要件の確認
   「店舗コードM001とM002の店舗において、2011年4月1日に少なくともどちらか一方の店舗で売れた商品の商品名及び販売数一覧」とあります。
2. 売上の偏り
   商品の売れ方は【問題文】「時期と店舗によって売れ筋商品は異なる」で示され、Ｍ001で売れたが Ｍ002では売れていない（または逆）のケースが存在します。
3. 結合方式の比較
   • 内部結合（INNER JOIN）…両テーブルで条件を満たす行のみ残る。
   • 外部結合（OUTER JOIN）…片方しか一致しない行も NULL を埋めて残す。
4. 要件との整合
   「少なくともどちらか一方」であるため、片方にしか無い行も結果に含める必要があり、外部結合が必須となります。
5. 25字以内の解答例
   「売れ筋が店舗ごとに異なり片方未販売商品を残すため」

誤りやすいポイント

• 2店舗ともで売れた商品だけを対象と勘違いして内部結合を選ぶ。
• 「全商品は毎日、各支部のいずれかの店舗で売れている」を根拠に “両店舗で必ず売れる” と早合点する。
• 結合条件に売上日や店舗コードを含め忘れ、期待外のNULL行を大量に作ってしまう。

FAQ

解答の論理構成

1. 目的の確認
   問題文はSQLA3を「最新の商品分類に基づいた2012年1月1日以降の日別商品分類名別売上額」と説明しています。最新判定は “商品” テーブルの履歴管理仕様に従います。
2. 履歴管理仕様
   “商品” テーブルでは「商品の列値を変更したときに、変更履歴を残すために、その商品に新たに商品番号を付与し、変更後の列値を設定した行を “商品” テーブルに追加する」とあります。ここでまとめのキーは “商品コード” です。
3. サブクエリの意味
   サブクエリは
   sql SELECT MAX(P2.登録日) FROM 商品 P2 WHERE (d) = (e)
   と書かれています。MAX を取る列が “登録日” であることから、同じグループ内で最新行を選ぶ意図と分かります。
4. 比較すべき列
   「同じグループ」は同じ “商品コード” であることを2. が示しています。したがって
   ・外側 (P1もしくはU) の“商品コード”
   ・内側 (P2) の“商品コード”
   を比較すればよい。
5. 結論
   外側は “P1.商品コード” を、内側は “P2.商品コード” を置くことで要件を満たします。外側を “U.商品コード” としても論理的に成立しますが、問題文ではP1を用いる書き方が自然であり、模範解答もそれを許容しています。

誤りやすいポイント

• “商品番号” と “商品コード” の混同
  “商品番号” は履歴ごとに変わり、“商品コード” は一意固定です。最新抽出は “商品コード” を基準にします。
• 外側テーブルの選択ミス
  P1 か U のどちらを用いても同一値ですが、FROM 句のエイリアスを確認せず “商品” ではなく “店舗売上” 側の列を指定し忘れるケースがあります。
• サブクエリで MAX を取る列の誤解
  MAX(登録日) と書かれているため、比較対象列は日付ではなくキー列（商品コード）である点を見落としがちです。

FAQ

解答の導き方

1. SQLの集計・結合の確認
   SQLA1を見ると、結合と集計のキーは「WHERE U.店舗番号 = M.店舗番号 AND M.SVコード = V.社員コード」および「GROUP BY D.四半期名、 V.社員コード、 V.社員名、 V.支部名」となっています。つまり売上行（U）は店舗（M）を経由して SV（V）の支部名でグループ化されます。
2. 保守手順とデータの保持を問題文から読み取る
   ・2011年7月1日に人事異動があり、問題文に「当該店舗に新たに店舗番号を付与し、 SVコードにそれぞれ新任SVの社員コードを設定した行を “店舗” テーブルに追加した」とあります。したがって店舗テーブルは状態変更時に新しい店舗番号の行を追加します。
   ・また「既に“店舗売上” テーブルに蓄積されている行を過去に遡って変更することはない」とあるため、過去の売上行は当時の店舗番号を保持したまま残ります。
   ・一方、商品や店舗については表に「商品の列値を変更したときに、変更履歴を残すために、その商品に新たに商品番号を付与し…」「店舗の列値を変更したときに、変更履歴を残すために、その店舗に新たに店舗番号を付与し…」と明記され、履歴管理の仕組みが説明されています。SV社員の表については同様の履歴付与の記述がありません。
3. 以上から発生する誤集計の仕組み（論理的帰結）
   売上行Uは当時の店舗番号を保持しており、その店舗番号でMの該当行と結合されるとM.SVコード には当時の担当SVの社員コードが入っています。しかしその社員コードでVと結合したとき、V.支部名が「現在の所属支部」を示す（SV社員表に履歴がないため）と、当該売上は「当時所属していた支部」ではなく「現在所属している支部」に割り当てられてしまいます。よって、SV別・支部別の年間集計が実際の時点別実績を反映せず誤ることになります。一方、全社合計は個々の割り当てが変わっても売上額の総和自体は変わらないため正しいままです。

誤りやすいポイント

• 「原因は店舗テーブルMが常に最新行と結合されることだ」と誤解する
  → 事例では店舗売上が店舗番号を保持し、店舗テーブルは変更前後で別行を持つため、M側が過去行を上書きしているわけではありません。問題の原因はSV側の履歴欠如です。
• SV社員表に「着任日」があるので履歴があると思い込む
  → 着任日の列があっても、問題文に「変更時に新たに社員コードを付与して履歴を残す」といった記述がない場合は履歴管理がされていない可能性が高い点に注意します。
• 全社合計まで誤っていると判断する
  → 集計キーのラベル付けが変わっても売上額の合計は不変なので、全社合計は正しいことが多いです。

FAQ

解答の論理構成

1. 変更要求の背景
   問題文では「支部では、月の途中に…SVの担当店舗を変えることがある」とし、その結果「青木さんと井上さんの人事異動前後の売上実績が…正しく反映されていない」①と指摘しています。
2. 新スキーマの目的
   Fさんは「SV社員(SV番号、社員コード、…、支部コード、…)」に変更し、SV番号 を主キーにして履歴を残す方針を採用しました。これはSlowly Changing Dimension Type-2と同じ考え方です。
3. 履歴を正しく残す方法
   Type-2では属性値（ここでは 支部コード）が変わった時点で「新しいサロゲートキー（SV番号）を振ったレコードを追加」し、旧レコードは残して過去分析を可能にします。
4. したがって解答
   ・当該社員に新しいSV番号 を付与した行を追加する。
   ・当該社員に異動後の 支部コード を設定した行を追加する。

誤りやすいポイント

• 既存行の 支部コード を UPDATE で書き換えてしまう
  → 過去の担当支部が失われ、前年同期比較などができなくなる。
• SV番号 を社員番号のように「一人一つ」と思い込む
  → 異動のたびに増える一対多関係である点を取り違えやすい。
• 「店舗」テーブルだけ更新すれば良いと考え、SV社員 テーブルを放置
  → 新旧SV番号 が店舗側に存在しない不整合が起きる。

FAQ

解答の論理構成

1. 手順 I は【表5】で「前日の行だけを選択」し、手順 II でその結果を INSERT しています。
2. S2の列構成は【図4】「S2（年、四半期名、店舗コード、店舗名、商品コード、商品名、売上額）」です。
3. 同一四半期内では「年、四半期名、店舗コード、店舗名、商品コード、商品名」が同一のまま日々売上額が増えるため、2 日目以降に INSERT すると「主キーが重複するから」（20 字以内の理由）SQL が失敗します。
4. ただし四半期が替わる最初の日、例として 2011-04-01（第2四半期初日）だけはテーブルにまだ行が存在しないため、正しく挿入されます。
5. S4も月次サマリなので同様の問題を抱えますが、問われたのは「一つ選び」なのでS2を採用しました。

誤りやすいポイント

• 「S1も ‘日’ を持つから安全だ」と理解できずにS1を選んでしまう。
• 「INSERT 後に UPDATE すれば良い」と手順外の操作を想定してしまう。
• 特定日を「四半期末」と誤答する（実際は四半期開始日で初回 INSERT）。
• 主キーではなく「売上額が0行は無い」条件を問題の原因と取り違える。

FAQ

解答の論理構成

1. 【問題文】の分析要件
   • B1: 「2009年以降の年別月別店舗名別商品区分名別売上額」
   • B2: 「2009年以降の年別商品名別売上額」
2. “図4” のサマリテーブル粒度
   • S1(年、月、日、店舗コード、商品分類番号、…)
   • S2(年、四半期名、店舗コード、商品コード、…)
   • S3(年、月、日、支部コード、商品コード、…)
   • S4(年、月、商品コード、社員コード、…)
3. 行数見積もり（主な列だけで概算）
   • 1日当たりの “店舗売上” 件数は “2,000万件”。
   • 店舗数 “10,000”、商品点数 “3,000”、SV “100×10”。
   • S1: 日別×店舗×商品分類(200) → 行数 ≒ 365×10,000×200
   • S3: 日別×支部(10)×商品 → 365×10×3,000
   • S4: 月別×商品×SV(1,000) → 12×3,000×1,000
   • S2: 四半期別×店舗×商品 → 4×10,000×3,000
4. 求めたい粒度との比較
   • B1は「商品区分名」。S1はすでに「商品分類番号」までまとまっており、商品区分名は商品分類テーブルと結合するだけ。よって追加集計行数が最少。
   • B2は「年×商品名」。列の余分が最も少ないのはS3（支部単位）→次にS4（月・SV単位）→最後にS2（店舗・四半期単位）。
5. 以上より
   • B1：S1
   • B2：S3, S4, S2（少ない順）

誤りやすいポイント

• 「商品区分名がS1に無いから使えない」と早合点する
  → 商品分類番号から商品区分名は次元テーブル参照で取得可能。
• 行数比較で「列の数」だけを見てしまう
  → 必ず【値のバリエーション】を掛け合わせて概算する。
• B2でS4を最小と判断してしまう
  → SV列が “1,000” 値を生むためS3より行数が多い。

FAQ

解答の論理構成

1. 変更内容の確認
   【問題文】「2012年4月15日」に商品分類「生菓子」を「生洋菓子」「生和菓子」に分割し、新しい行を “商品分類”・“商品” テーブルへ追加した。
2. サマリテーブル側の問題
   【問題文】図4 S1は「商品分類番号、商品分類名」を列として保持し、表5手順I・IIにより“前日の行だけ”を積み上げている。したがって2012年4月14日以前の行は旧分類名「生菓子」のまま固定され、分割後に自動的には更新されない。
3. 各分析で利用されるサマリテーブル
   • B1：次元は「商品区分名」。商品区分は「日配食品」などで変わっていないため影響なし。
   • B2：次元は「商品名」。分類変更に関係しない。
   • B3：次元は「店舗名」と 「商品分類名」。S1の固定値をそのまま使うため、旧データが更新されず影響を受ける。
   • B4：売上軸に 支部名 と 商品分類名 が入るが、S1 には支部列がないため、実際の集計では商品コード主体の S3 を JOIN して最新の分類を参照する設計となる。JOIN 時点で“商品分類”テーブルから最新分類名を取得できるので再集計は不要。
4. よって、該当するのは B3 のみとなる。

誤りやすいポイント

• 「商品区分名も分割された」と誤解してB1まで選んでしまう。
• 「支部名を含むから B4 も S1 だろう」と早合点し、JOIN で最新値を取得できる事実を見逃す。
• サマリテーブルに「商品分類番号」があれば最新名を引けると思い込み、「商品分類名」も物理的に保持している点を見落とす。

FAQ