平成24年度春期 データベーススペシャリスト 午後Ⅰ 問3

時点の整合から設計を検討する

次元の属性が変わったとき、集計結果がどの時点の状態に基づくべきかという要求を先に確認しましょう。その要求と履歴の持ち方、サマリの更新範囲がそろっているかという一つの観点で読み解きます。

この問題で問われていること

問われているのは個々の用語の暗記よりも、業務上の分析要求と、データの保持方法や更新手順との対応関係です。記述されている要求がどの時点の状態を基準に集計したいのかを読み取り、設計上の課題と結び付けて考えることが中心です。

視点

データウェアハウスでは、分析の軸となる情報が時間とともに変化し得ます。事実データ、軸となる情報、あらかじめ集計した結果は、それぞれ異なるタイミングで作られます。そのため、ある時点で見た集計が別の時点の状態と食い違うことがあります。設計を検討するときは、利用者がどの時点の見え方を求めているかを基準にします。そのうえで、保持と再集計の方針が一貫しているかを確かめると整理しやすくなります。

確認ポイント

• 分析者が求めている集計が、どの時点の状態を基準にしているかを読み取れたか
• 軸となる情報の変化が、既存の集計結果にどの範囲まで影響し得るかを考えたか
• 保持の方針と集計の更新手順が、読み取った要求と矛盾していないかを確かめたか

解説

コンビニチェーンのデータウェアハウスを題材に、スタースキーマの次元テーブルとサロゲートキーによる変更履歴の管理、外結合・相関副問合せを使ったSQL、日次更新するサマリテーブルの主キー設計と集計の単位を問う問題です。公式解答例では、LEFT外結合と販売数IS NOT NULLの条件、商品コードによる相関副問合せ、SV番号を新しく付けた行の追加、四半期・月単位のサマリ(S2・S4)で起きる主キーの重複、B2に使えるサマリの行数順S3・S4・S2、再集計が必要なB3、が答えとされています。

問題の全体像

E社は全国10、000店舗(10支部、1支部1、000店)の売上を“店舗売上”テーブル(ファクト)と、日付・店舗・商品などの次元テーブルからなるスタースキーマで管理しています。店舗・商品の列の値が変わったときは、新しい店舗番号・商品番号(サロゲートキー)を付けた行を追加し、過去の売上行は更新しない運用です。一方、SV社員テーブルは社員コードが主キーのままなので、人事異動の履歴を残せません。さらに日次更新のサマリテーブルS1～S4には、主キーの設計と集計の単位に問題があります。公式解答例の出題趣旨では、スタースキーマと次元テーブルの役割、サロゲートキーの役割と考慮点、サマリテーブル(データマート)の設計・運用、外結合と相関副問合せの理解が問われているとされています。

設問1(1)(a)

分析A2は「M001とM002の少なくともどちらか一方で売れた商品」の一覧です。SQLA2は“商品”Pを基準に、店舗ごとの売上U1・U2を順に結合しています。売れなかった店舗の側がNULLになっても商品の行を残すには、左側の商品表を基準にする外結合が必要です。そのため公式解答例では、aはLEFT(LEFT OUTER JOIN)とされています。売上日と店舗コードの条件をWHERE句でなくON句に書いているのも、外結合で行を残すための書き方です。

設問1(1)(b)

LEFT外結合だけでは、どちらの店舗でも売れなかった商品も、U1・U2の両方がNULLの行として結果に残ります。そこでWHERE句で「どちらか一方で売れた」行だけに絞り込みます。公式解答例では、bはU1.販売数 IS NOT NULL(またはU1.販売数>0)とされ、cとは順不同です。M001で売れた商品だけが残る条件です。

設問1(1)(c)

bと対になる条件です。公式解答例では、cはU2.販売数 IS NOT NULL(またはU2.販売数>0)とされています(bとは順不同)。bとcをORでつなぐと、少なくとも一方の店舗で売れた商品だけが残り、分析A2の要件を満たします。

設問1(2)

着目点は〔組織及び販売商品の概要〕2.(4)の「時期と店舗によって売れ筋商品は異なる」という記述です。内結合にすると、M001とM002の両方で売れた商品しか残らず、一方の店舗だけで売れた商品が抜け落ちます。公式解答例では「時期と店舗によって売れ筋商品は異なるから」「店舗によって全商品が売れるとは限らないから」とされています。25字以内に収めるには、本文の用語を使って「店舗によって売れる商品が異なる」ことを示せば十分です。

設問1(3)(d)

分析A3は「最新の商品分類」による集計です。“商品”テーブルには同じ商品コードで商品番号の違う行が複数あるため(図3)、商品コードごとに登録日が最大の行を相関副問合せで選びます。副問合せの内側P2を、外側の商品(P1、またはU)と商品コードで対応付けます。公式解答例では、dはP1.商品コード(またはU.商品コード)とされ、eとは順不同です。採点講評では、ここを商品番号とする誤りが目立ったと指摘されています。外側のWHERE句にあるU.商品コード=P1.商品コードと、表1の商品コード・商品番号の意味の違いが手掛かりです。

設問1(3)(e)

dと対になる、副問合せ側の列です。公式解答例では、eはP2.商品コードとされています。P2.商品コード=P1.商品コードとすると、同じ商品コードを持つ全行のうち最大の登録日、つまり最新の分類を持つ行だけがP1として選ばれます。店舗売上Uとは商品コードで結合しているので、過去の売上も最新の商品分類で集計されます。

設問2(1)(正しくないもの)

SQLA1は店舗→SVコード→SV社員と結合し、V.支部名で分類しています。店舗テーブルは異動時に新しい店舗番号で行を追加するので、各売上がどのSVのものかは正しく分かります。しかしSV社員テーブルは社員コードが主キーで、支部を1つしか保持できません。そのため、青木さん・井上さんの異動前後の売上が、すべて一つの支部(分析時点の所属支部)に計上されます。公式解答例では、正しくないものは支部別SV別年間売上額と支部別年間売上額とされています。全社年間売上額は支部の振り分けに関係なく合計が変わらないので、正しいままです。

設問2(1)(理由)

理由として押さえる要素は「SV社員テーブルが異動前の支部を保持していない(上書きされる)」ことと、「その結果、分析時点の支部で集計される」ことです。公式解答例では「社員が人事異動前に所属していた支部の情報を失うから」「SVの売上額が分析実施時点の所属支部に集計されるから」とされています。30字以内なので、どちらか一方の観点を簡潔に書けば足ります。

設問2(2)

変更後のSV社員テーブルは、サロゲートキーのSV番号が主キーで、店舗テーブルはSV番号を外部キーとして参照します。店舗番号・商品番号と同じ考え方で、支部が変わったときは既存行を更新せず、新しいSV番号を付けた行を追加します。そうすれば過去の店舗行は旧SV番号(旧支部)を、新しい店舗行は新SV番号(新支部)を参照し、異動前後の支部別売上が正しく分かれます。公式解答例では「当該社員に新しいSV番号を付与した行を追加する」「当該社員に異動後の支部コードを設定した行を追加する」とされています。

設問3(1)(サマリテーブル名)

表5の手順では、毎日「前日分」をGROUP BYで集計し、INSERTでサマリに追加します。主キーに日を含むS1・S3は毎日別の行になるので問題ありません。一方、S2の主キーは(年、四半期名、店舗コード、商品コード)、S4は(年、月、商品コード、社員コード)で、日単位より粗い単位です。そのため、同じ期間の2日目以降は同じ主キーの行を再びINSERTすることになります。採点講評でも、INSERT文を使うことと各サマリテーブルの主キーに着目してほしかったと述べられています。公式解答例では、S2またはS4とされています。

設問3(1)(特定日の例)

INSERTが正しく行えるのは、その集計単位の最初の日のデータを追加する日だけです(その時点ではまだ行が存在しないため)。公式解答例では、S2を選んだ場合は「四半期の初日」、S4を選んだ場合は「各月の初日」とされています。選んだサマリテーブルと特定日の組合せを取り違えないように注意が必要です。

設問3(1)(理由)

特定日以外は、同じ年・四半期(または月)・店舗(または社員)・商品の行がすでにサマリに入っています。そのため、INSERTしようとすると主キー制約に違反します。本来は既存行の売上額を加算するUPDATEが必要です。公式解答例では「主キーが重複するから」「挿入すべき行が既に存在するから」とされています。20字以内なので、主キーの重複という一点を書けば足ります。

設問3(2)(B1)

B1は年別・月別・店舗名別・商品区分名別の分析です。月と店舗の単位を持ち、商品区分へたどれるサマリが必要です。S1は日・店舗コード・商品分類番号を持ち、商品分類テーブルを参照すれば商品区分名が得られるので、集計できます。S2は四半期単位で月が分からず、S3は支部単位、S4は社員(SV)単位で店舗が分かりません。採点講評のとおり、各サマリの次元の単位と分析したい単位を比べると、公式解答例のとおりS1だけになります。

設問3(2)(B2)

B2は年別・商品名別の分析です。商品名(商品コード)を持つS2・S3・S4が使え、商品分類単位のS1は使えません。採点講評に従い、商品コード以外の次元で年間の行数を見積もります。全商品3、000点は毎日各支部のいずれかの店舗で売れ、1店舗でも1か月で全商品が売れるので、各キー組合せにはほぼ行があると考えます。S3は365日×10支部×3、000点で約1、095万行です。S4は、SVが1支部当たり100人(1、000店÷10店)なので全社1、000人となり、12か月×1、000人×3、000点で3、600万行です。S2は4四半期×10、000店×3、000点で1億2、000万行です。したがって公式解答例のとおり、少ない順にS3、S4、S2となります。SVを全社100人と読み違えると順序を誤る点に注意が必要です。

設問3(3)

生菓子を生洋菓子・生和菓子に分けても、商品区分名(日配食品)と商品名は変わりません。そのためB1とB2は影響を受けません。B4(支部別・月別・商品分類名別)はS3から集計でき、S3は商品コードを持つので、商品テーブルの最新の行をたどれば新しい分類で集計できます。B3(店舗別・月別・商品分類名別)は店舗と月の単位を持つS1からしか集計できません。しかしS1には過去分が旧分類(商品分類番号14)で集計済みなので、生洋菓子と生和菓子に分け直せず、店舗売上からの再集計が必要です。採点講評の考え方どおり、公式解答例ではB3とされています。

学習のポイント

商品番号(行ごとの番号)と商品コード(商品ごとの識別子)のように、サロゲートキーと業務上の識別子の違いを理解すること。また、サマリテーブルの主キーから集計の単位と年間の行数を見積もる手順を身に付けることが重要です。