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

コアとなるキーワードと論点整理

解答が (a),(b),(c) になる理由

1. 「少なくともどちらか一方の店舗で売れた商品」を抽出する要件
   問題文より

   「店舗コードM001とM002の店舗において、2011年4月1日に少なくともどちらか一方の店舗で売れた商品の商品名及び販売数一覧」
   とあるため、

   • 「P（全商品）を起点に、M001 店と M002 店の売上を結合」
   • どちらか一方の売上があればよい
     という条件を満たす必要があります。
2. OUTER JOIN の使いどころ
   • INNER JOIN にすると 両店舗で売れた商品 のみが抽出されてしまい要件を満たさない
   • LEFT OUTER JOIN で P を基準に結合し、売れなかった側は NULL として取り込む
3. WHERE 句の絞り込み
   • OUTER JOIN した場合、売上がない行は U1.販売数／U2.販売数 が NULL
   • 「売れた」を判定するには IS NOT NULL （または > 0）でチェック
   • 少なくともどちらかの店舗で売れた＝U1.販売数 IS NOT NULL OR U2.販売数 IS NOT NULL

SQLA2 の仕上がりイメージ

SELECT
  P.商品名,
  U1.販売数,
  U2.販売数
FROM 商品 P
LEFT OUTER JOIN 店舗売上 U1
  ON P.商品番号 = U1.商品番号
  AND U1.売上日 = ISODATE('2011-04-01')
  AND U1.店舗コード = 'M001'
LEFT OUTER JOIN 店舗売上 U2
  ON P.商品番号 = U2.商品番号
  AND U2.売上日 = ISODATE('2011-04-01')
  AND U2.店舗コード = 'M002'
WHERE
  U1.販売数 IS NOT NULL        -- (b)
  OR U2.販売数 IS NOT NULL    -- (c)
;

• (a) に入るのは LEFT
• (b) に入るのは U1.販売数 IS NOT NULL（または U1.販売数 > 0）
• (c) に入るのは U2.販売数 IS NOT NULL（または U2.販売数 > 0）

受験者が誤りやすいポイント

1. INNER JOIN を選んでしまう
   • 両店舗で売れた商品だけ抽出され要件に合わない
2. WHERE 句で U1.販売数 > 0 と U2.販売数 > 0 を AND で結ぶ
   • 「どちらか一方」ではなく「両方売れたもの」だけが残る
3. NULL チェックの理解不足
   • OUTER JOIN 後の NULL 値を利用して売上の有無を判定する点を押さえていない

試験対策として押さえるべき知識

• JOIN の種類
  • INNER JOIN：両側にマッチする行のみ
  • LEFT OUTER JOIN：左側を全て、右側はマッチなしを NULL で補填
  • RIGHT/FULL も理解しておく
• NULL の扱い
  • NULL と比較演算子（= や >）は常に FALSE
  • IS NULL / IS NOT NULL を用いた判定
• 要件のキーワード
  • 「少なくともどちらか一方」→ OR 条件＋OUTER JOIN
  • 「両方」→ AND 条件＋INNER JOIN

キーワード整理

• 外結合（OUTER JOIN）
• 売上がない商品も結果に含める
• 特定店舗で売れない商品の扱い

解答の論理的説明

「時期と店舗によって売れ筋商品は異なるが,全商品は毎日, 各支部のいずれかの店舗で売れている。」
とあるため，特定の店舗で売れなかった商品も表示させるには外結合が必要です。

誤りやすいポイント

• WHERE句に結合条件を書く
  外結合で取得したNULL行にWHERE U1.販売数>0などを置くと，内部結合と同様にNULL行が除外される
• NULLの扱い
  販売数がNULL＝売上ゼロの扱いとして明示的にCOALESCEなどで0に変換する

試験対策ポイント

• 内結合は「両側に存在する行のみ」返す
• 左外結合は「左側の全行＋右側にマッチしなければNULL」
• WHERE句でNULLフィルタリングしないよう注意する
• 「売れ筋商品は店舗‐時期で異なる」「全商品が各店で売れるわけではない」→外結合の要件を即答できるようにする

解説の概要

… P1.登録日 = (
  SELECT MAX(P2.登録日)
  FROM 商品 P2
  WHERE d = e
)

キーワード・論点整理

• 履歴管理（バージョニング）
  「商品番号」は行を一意に識別するサロゲートキー
  「商品コード」は属性変更後も不変のナチュラルキー
• 最新バージョンの抽出
  MAX(登録日)を相関サブクエリで取得
• 相関サブクエリ
  外側のエイリアス（P1またはU）と内側のエイリアス（P2）を結合

解答のポイント

… P1.登録日 =
  (SELECT MAX(P2.登録日) FROM 商品 P2 WHERE <d> = <e>)

• 内側クエリのテーブルは商品 P2
• 外側クエリで紐づけるテーブルとして商品 P1または店舗売上 U
  を利用できます。

WHERE P2.商品コード = P1.商品コード

• d：P1.商品コード （※外側エイリアスU.商品コードも可）
• e：P2.商品コード

なぜその解答になるのか

「商品を登録したときに付与する。また、商品の列値を変更したときに、変更履歴を残すために、
その商品に新たに商品番号を付与し、変更後の列値を設定した行を “商品” テーブルに追加する。」

受験者の誤りやすいポイント

• 「商品番号」をキーにしてしまう
  → 商品番号は行ごとに再付与されるサロゲートキーのため、履歴取得には不向き
• 相関サブクエリで外側参照を忘れる
  → WHERE P2.登録日=(SELECT MAX(P2.登録日)… )のように相関がずれる
• U.商品コードでも外側参照は可能だが、設問がP1を想定

試験対策として覚えておくべきポイント

1. サロゲートキー（商品番号）とナチュラルキー（商品コード）の役割を区別
2. バージョニングされたテーブルから「最新行」を取得するには
   • 相関サブクエリ＋MAX(登録日)
   • WHERE <ナチュラルキー>=<サブクエリ側のナチュラルキー>
3. 相関サブクエリでは、外側のエイリアス名を正しく指定する
4. データウェアハウス／履歴管理設計の基本を押さえる
   • 次元テーブルの履歴管理は “過去へさかのぼって更新しない”（SCD Type 2 相当）

模範解答の論点整理

• 正しくないもの
  • 支部別 SV別年間売上額
  • 支部別年間売上額
• 正しいもの
  • 全社年間売上額
• 理由（30字以内）
  社員が人事異動前に所属していた支部の情報を失うから
  SVの売上額が分析実施時点の所属支部に集計されるから

解答の論理的説明

1. 問題文の該当箇所
   • 「(2) 2011年7月1日, SV の青木さんと井上さんに支部間の人事異動があり, 担当店舗を入れ替えた。」
   • 「(4) 次元テーブルの変更後, “店舗売上” テーブルには最新の店舗番号及び商品番号を設定した行を追加するが, 既に“店舗売上” テーブルに蓄積されている行を過去に遡って変更することはない。」
2. なぜ「支部別 SV別年間売上額」「支部別年間売上額」が誤るのか
   • 人事異動によって「SVコード」が変わった後も、過去分の“店舗売上” は更新されないため、
   • SQLA1 の結合条件

     … WHERE U.店舗番号 = M.店舗番号  
             AND M.SVコード = V.社員コード …
     

     により、過去日の売上実績も分析実施時点の（最新の）SVコード・支部コードと結合される。
   • その結果、移動前に支店Aで獲得した売上が支店Bの実績に反映されてしまい、
     支部別・SV別の年間集計が正しくならない。
3. なぜ「全社年間売上額」は正しいのか
   • 全社合計は、部門やSVを意識せず単に全売上額を合算するだけなので、
   • 次元テーブルの変更（SV異動など）に影響されず、全社の売上額合計は変わらない。

受験者が誤りやすいポイント

1. 「過去行は更新しない」がキモ
   • データウェアハウスで履歴管理（Type2次元）を利用している場合、
     変更後に既存売上を更新しない設計が多い点を押さえること。
2. 全社合計もズレると思い込みやすい
   • 支部やSVコードとは無関係に集計するため、全社合計だけは常に正しい。
3. 「登録日」の考慮漏れ
   • SQLA1 では「登録日」を考慮せず結合しているため、最新次元としか結合できない点を理解する。

試験対策としてのポイント

• 次元テーブル変更時の売上テーブルへの影響
  • 変更前の売上実績をそのまま残し、結合時に履歴を正しく参照する手法（有効開始日／終了日など）を理解する。
• データウェアハウスの Slowly Changing Dimension（SCD）
  • 種類（Type1～4）ごとの特徴と、照会時に履歴を参照する方法を押さえる。
• 分析SQLの結合条件チェック
  • 履歴管理項目（登録日や有効開始日）を必ず WHERE 句やサブクエリで制限し、意図しない次元行と結合しないようにする。

キーワード・論点整理

• 履歴管理（変更履歴を残す）
• サロゲートキー（SV番号）
• SCD（スローリー・チェンジング・ディメンション）タイプ2

解答の骨子

解答に至る理由

変更履歴を残すために，“SV 社員” テーブルと “店舗” テーブルの構造を次のように変更し...
SV社員(SV番号, 社員コード, 社員名, 支部コード, 支部名, 着任日)
注記　付与する番号又はコードは、再利用しない。

• 「変更履歴を残す」とある以上、既存レコードを上書き（UPDATE）してはいけません。
• SV番号はサロゲートキーであり、一意な新番号を付与して新規行を追加すると、前職と現職の履歴を区別して保持できます。
• 追加行には「異動後の支部コード」と「着任日」を設定します。

受験者が誤りやすいポイント

試験対策ポイント

• 「変更履歴を残す」＝SCDタイプ2：古いレコードは維持し、新規レコードをINSERT
• サロゲートキー（SV番号や店舗番号、商品番号など）をキーに、履歴管理を行う
• 主キーやコードを「再利用しない」方針を厳守
• UPDATEではなくINSERTで対応するケースを押さえる
• テーブルトライアングル問題（図や文中のテーブル構造）で、どの列がキーかを確認する癖をつける

1. 模範解答のキーワード・論点整理

• サマリテーブルの主キーは「図４　サマリテーブルの構造」で下線が付いているコード項目のみで構成される
• S2 の下線項目：店舗コード, 商品コード
• S4 の下線項目：商品コード, 社員コード
• 日次更新処理（表５）では，「日付」をキーに含めずに前日の集計結果を INSERT している
• 初回の集計（四半期初日／月初）には該当キーが存在せず INSERT 成功
• 以後は同一キーで INSERT しようとすると「主キー重複」によるエラーまたは挿入失敗

2. なぜ S2（または S4）なのか

サマリテーブル S2 の場合

S2（年，四半期名，**店舗コード**，店舗名，**商品コード**，商品名，売上額）  

• 下線部分（主キー）は「店舗コード」「商品コード」のみ
• 表5 の手順 I, II では毎日「前日の行だけを選択し（年・四半期名・店舗コード・商品コード　で集計）」→ INSERT
• 四半期の初日にはまだ同一「店舗コード＋商品コード」の行が存在しないため INSERT 可
• 四半期の初日を除き，それ以降の日は同じ「店舗コード＋商品コード」で再度 INSERT を試みるため
  → 主キー重複 となり SQL 文で正しく更新（INSERT）できない

引用

「図４　サマリテーブルの構造」
S2（年，四半期名，店舗コード，店舗名，商品コード，商品名，売上額）
（下線はコード項目だけに付いており…）

「表５ サマリテーブルを更新する手順」
I. …“店舗売上”テーブルから前日の行だけを選択し，GROUP BY 句によって集計した結果行を CSV ファイルに出力
II. INSERT 文を用いて，手順 I の CSV ファイルの全レコードをサマリテーブルに追加

S4 の場合（同様の論理）

S4（年，月，**商品コード**，商品名，**社員コード**，社員名，売上額）  

• 下線部分は「商品コード」「社員コード」のみ
• 各月の初日を除き，日次集計を同一「商品コード＋社員コード」で INSERT → 主キー重複

3. 受験者が誤りやすいポイント

• 「四半期名」「月」「日」など期間要素をキーに含めていると思い込み，主キー重複の発生タイミングを誤る
• S1 や S3 は日付（日）あるいは商品分類番号をキーに含むため，日付または分類が切り替わるたびに重複しない
• 「特定日の例」を
  • S2 → 四半期の初日
  • S4 → 各月の初日
    という点を間違えない

4. 試験対策として覚えておくべきポイント

• サマリテーブル設計では，主キーに日付や期間識別子を含めないと日次 INSERT 時に重複する
• 日次更新方式には「INSERT のみ」「更新（UPDATE）のみ」「UPSERT（MERGE）」など複数方式がある
• 今回のように INSERT のみで運用するなら，キー設計に 必ず「日付」「月初」など期間を含める
• 問題文中の図や表での「下線」は，しばしば主キーやインデックスの構成列を表すので要チェック

キーワードと論点の整理

• サマリテーブル（S1～S4）
  あらかじめ異なる粒度で集計した売上データを保持するテーブル。図４で定義されている。
• 分析 B1・B2 の要件
  表２より
  • B1：「2009年以降の年別月別店舗名別商品区分名別売上額」
  • B2：「2009年以降の年別商品名別売上額」
• 必要な次元属性
  • B1：年、月、店舗名、商品区分名
  • B2：年、商品名
• 性能評価基準
  「GROUP BY句による年間の集計対象行数が少ない順に」
  → サマリテーブルの粒度が粗い（＝１行あたりに集める元データ件数が少ない）ほど良い

サマリテーブルの粒度比較

• 太字は，今回の設問でのポイントとなるキー項目です。
• S1は「商品分類番号／商品分類名」を持つため，商品区分（大分類）での集計に適している。
• S2～S4は「商品コード／商品名」を持つため，商品単位の集計に適している。

解答の論理的説明

B1：「年別月別店舗名別商品区分名別売上額」

1. 必要な次元属性は「年」「月」「店舗名」「商品区分名」
2. サマリテーブルの中で 商品区分名 を直接持ち，かつ 店舗単位 の集計が可能なのは S1 のみ。
   • S2：商品区分ではなく「商品コード／商品名」，さらに粒度は四半期
   • S3：支部単位
   • S4：社員単位
3. したがって，B1は S1 から

   SELECT 年, 月, 店舗名, 商品分類名 AS 商品区分名, SUM(売上額)
     FROM S1
    WHERE 年 >= 2009
    GROUP BY 年, 月, 店舗名, 商品分類名;
   

B2：「年別商品名別売上額」

1. 必要な次元属性は「年」「商品名」
2. 商品レベルの集計が可能なサマリテーブルは S2, S3, S4 の３つ
3. 各テーブルに格納されている行数の概算（１年間あたり）
   • S3：支部数10 × 月12 × 商品点数3,000 ＝ 360,000行／年
   • S4：社員数100 × 月12 × 商品点数3,000 ＝ 3,600,000行／年
   • S2：店舗数10,000 × 四半期4 × 商品点数3,000 ＝120,000,000行／年
4. 「年間の集計対象行数が少ない順」に並べると
   1. S3
   2. S4
   3. S2
5. 例えば S3 を使う場合は，必要に応じて商品マスタを参照しながら

   SELECT 年, 商品名, SUM(売上額)
     FROM S3 JOIN 商品 USING (商品コード)
    WHERE 年 >= 2009
    GROUP BY 年, 商品名;
   

受験者が誤りやすいポイント

1. 粒度（granularity）の混同
   • 「月別」を求めているのに「日別」（S1）や「四半期別」（S2）の集計をそのまま使えると思い込む
2. サマリテーブルのキー項目漏れ
   • B1に商品区分名が必要にもかかわらず，S2～S4 は「商品分類名」を持たない
3. 行数試算のケタ間違い
   • 支部数・社員数・店舗数の違いを見落とし，S2やS3の行数を誤って評価してしまう

試験対策として覚えておくべきポイント

• サマリテーブルは「想定クエリの粒度に合うもの」を選ぶ
• より粗い粒度（＝キー項目が少ない、集約レベルが高い）ほど行数は少なく，集計コストが低い
• 必要に応じて次元テーブル（商品マスタや支部マスタなど）と結合して名称を補う
• クエリ要件に出てくる「年」「月」「店舗」「支部」「商品区分／商品名／社員」などの属性が，サマリテーブルに含まれているか必ずチェックする
• 行数の試算では，次元のカーディナリティ（数）に着目し，順序関係を正しく判断すること

キーワード・論点整理

• 「最新の商品分類」
  問題文より：

  「2012年4月15日に,商品分類である生菓子を生洋菓子と生和菓子に分けた」
  「…再集計をしないと,この最新の商品分類を反映できない分析がある」

• 分析で利用する軸（表２より）

  分析名	分析内容
  B1	2009年以降の年別月別店舗名別商品区分名別売上額
  B2	2009年以降の年別商品名別売上額
  B3	2009年以降の年別月別店舗名別商品分類名別売上額
  B4	2009年以降の年別月別支部名別商品分類名別売上額

• 次元変更の影響
  • 「商品区分名」（日配食品／加工食品／非食品）は変更なし
  • 「商品名」も変更なし
  • 「商品分類名」は変更あり（生菓子→生洋菓子／生和菓子）

解答の論理展開

1. 問題文の要件
   問題文（〔問題点の指摘〕③）では，

   「2012年4月15日に,商品分類である生菓子を生洋菓子と生和菓子に分けたが,表2中の分析 B1〜B4 のうち, ‘店舗売上’ テーブルから再集計をしないと,この最新の商品分類を反映できない分析がある。」
   とあります。要するに，商品分類名の変更を反映させるには，過去データも含めて改めて「店舗売上」から集計し直す必要がある分析がある，ということです。

2. 各分析の利用項目と影響
   表２から，B1〜B4 が利用している項目を整理します。

   分析名	利用する名称軸	変更の有無
   B1	商品区分名	変更なし
   B2	商品名	変更なし
   B3	商品分類名	変更あり（要再集計）
   B4	商品分類名	変更あり

   • B1・B2 は「商品区分名」「商品名」を使い，これらは今回変更がないため，従来集計済みのサマリや次元テーブル結合で問題なく最新の集計結果を得られる。
   • B3・B4 は 商品分類名 を利用し，この部分が「生菓子」→「生洋菓子／生和菓子」と変わっている。
3. なぜ B3 のみか
   一見 B3・B4 両方とも商品分類名を使うように見えますが，
   • B4（「年別月別支部名別商品分類名別売上額」）は，サマリテーブルの構造上，支部別・商品分類別 の集計をサマリテーブルから直接算出できず，そもそもサマリテーブル外で別設計の処理が必要な分析です。
   • 一方，B3（「年別月別店舗名別商品分類名別売上額」）は，本来サマリテーブル S1 に日次で保持されている （店舗×商品分類名×売上額） のデータを月次累計すれば得られる設計になっているため，普段はサマリテーブル S1→月次集計で処理しているはずです。
   • しかし，サマリテーブルは「サマリ更新手順」（表５）で過去行を更新せずに直近日のデータのみ追加しており，商品分類が変わった 2012年4月15日以前 のレコードは，元の「生菓子」の分類名のまま残ってしまっています。
   • したがって，B3 は過去の「生菓子」売上を「生洋菓子」「生和菓子」に再分類して最新の分類名で集計するためには，「店舗売上」テーブルから再集計し直す必要があります。

受験者の注意ポイント

• 「商品区分名」と「商品分類名」を取り違えない
  → 今回変更されたのは下位分類の 「商品分類名」（生菓子→生洋菓子／生和菓子）のみ。
• B4 は商品分類名を使うが，サマリテーブルの設計要件の観点で B3 と扱いが異なる
  → B4 はそもそも別途サマリテーブルからの集計方式ではない可能性がある。
• 次元テーブルの変更は，過去行に遡って自動反映されない
  → 「追加された次元のみ最新」「既存業績は更新されない」ことを確認。

試験対策ポイント

• 次元テーブルの履歴管理（Type2 SCD）では，**「過去販売実績を遡って置き換えない」**点を常に意識する。
• 分析軸（商品区分／商品分類／商品名）は階層構造になっており，どの階層が変更されると，どの集計に影響するかを整理しておく。
• サマリテーブル設計の問題点
  • どの粒度で保持しているか（日次／月次／支部別…）
  • 変更履歴を遡及反映すべきか否か
    を検討し，設計要件に応じた再集計（フルリフレッシュ）や増分更新手法を選べるようにする。