応用情報技術者 2010年 春期 午後 問06
販売管理システムで使用するSQL文の検証に関する次の記述を読んで、設問1～3に答えよ。
X社は、輸入インテリアの販売を行っており、全国に店舗を展開している。現在、店舗ごとの受注・出荷を販売管理システムで管理している。新たに、インターネットで販売を行うWebショップを開設することになり、それに合わせて、Webショップでの受注を管理できる販売管理システムを構築することにした。
新システムの開発に当たって、システム部のY君が初めてデータベースの設計・開発を任され、Z係長が指導をすることになった。
〔新システムの機能概要〕
・Webショップで受注した商品は、顧客の住所を基に在庫のある最寄り店舗から出荷する。
・受注情報には受注店舗の情報をもち、受注明細には出荷店舗の情報をもつ。
・実店舗での販売においては、受注情報と受注明細の店舗は、販売した実店舗とする。
・Webショップでの販売においては、受注情報の店舗はWebショップとし、受注明細の店舗は出荷を行う最寄り店舗とする。
・Webショップも含めた全店舗の売上を、月次で店舗ごとに集計する。
・Webショップで販売した商品の売上については、出荷した実店舗に配分する。したがって、Webショップの売上を、月次で受注明細の出荷店舗ごとに集計する。
・Webショップの店舗番号は “A09999” とする。
〔新システムで使用するSQL文の検証〕
(1) テーブル設計
Y君は、新システムのデータベースのテーブルを設計した。設計したテーブルの一部を図1に示す。
(2) SQL文の作成
Y君は、店舗ごとの売上を月次で集計する図2のSQL文と、Webショップの売上を出荷店舗ごとに月次で集計する図3のSQL文を作成した。“:指定月開始日”、“:指定月終了日”は、それぞれ集計対象月の開始日、終了日を表す埋込み変数である。
(3) テストデータの作成
Y君は、SQL文の検証のためにテストデータを作成した。作成したテストデータのうち、店舗、受注、受注明細の各テーブルのデータを表1〜3に示す。
(4) SQL文の実行
表1～3のテストデータを用いて、指定月開始日を2010年3月1日、指定月終了日を2010年3月31日として図2のSQL文を実行した結果、図4の出力が得られた。
Z係長は図4の出力された結果を見て、このSQL文では、集計の対象となる期間にd店舗の場合は、店舗ごとの売上の集計に出力されないことを指摘した。Y君がSQL文を図5のように修正して実行したところ、期待された結果が得られた。
模範解答
a：'A09999'
b：j.受注番号
c：m.受注番号
解説
解答の論理構成
-
Webショップだけを対象にする条件
- 仕様には「Webショップの店舗番号は “A09999” とする。」と明記されています。
- したがって 受注 表の列である 受注店舗番号 が “A09999” である行だけを抽出すれば、Webショップで受け付けた受注だけが対象になります。
- ここが a に入る定数であり、正しくは 'A09999' です。
-
受注表と受注明細表を結び付ける条件
- 集計対象の金額は 受注明細 表に格納されていますが、どの明細がWebショップで受け付けた受注に属するかを判定するために、両表を結合する必要があります。
- 両表を一意に対応付けるキーは受注番号です。
- よって 受注.受注番号 = 受注明細.受注番号 という等価結合が必要になります。
- ここが b と c に入る句であり、順序は問われません。具体的にはj.受注番号 とm.受注番号 です。
-
結果
- 以上より
• a：'A09999'
• b：j.受注番号
• c：m.受注番号
が解答となります。
- 以上より
誤りやすいポイント
- Webショップの売上を「受注明細の出荷店舗番号＝'A09999'」と誤解しやすい
（実際には出荷店舗は実店舗なので別番号）。 - 結合条件をj.受注店舗番号 = m.出荷店舗番号 としてしまうミス
（これではWebショップ以外の受注まで拾ってしまう）。 - 文字列リテラル 'A09999' をクォートせずに書き、実行時エラーになる。
FAQ
Q: なぜ出荷店舗番号ではなく受注店舗番号で絞り込むのですか？
A: Webショップで受け付けた受注かどうかは「受注店舗番号」で判定すると仕様にあるためです。出荷店舗は配送拠点であり、必ずしも “A09999” ではありません。
A: Webショップで受け付けた受注かどうかは「受注店舗番号」で判定すると仕様にあるためです。出荷店舗は配送拠点であり、必ずしも “A09999” ではありません。
Q: 結合キーに受注日付を使ってはいけませんか？
A: 受注日付だけでは一意に行を識別できず、重複や誤結合が発生するため適切ではありません。主キーである受注番号同士を結ぶのが正解です。
A: 受注日付だけでは一意に行を識別できず、重複や誤結合が発生するため適切ではありません。主キーである受注番号同士を結ぶのが正解です。
Q: 'A09999' をダブルクォートで囲んでも良いですか？
A: 多くのSQL実装では文字列リテラルはシングルクォートで表記する規約になっています。ダブルクォートは識別子引用符として扱われることがあるため、避けるのが無難です。
A: 多くのSQL実装では文字列リテラルはシングルクォートで表記する規約になっています。ダブルクォートは識別子引用符として扱われることがあるため、避けるのが無難です。
関連キーワード: INNER JOIN, WHERE句, グループ化, 集計関数, リテラル
模範解答
解説
解答の論理構成
-
集計対象となる注文の抽出
- 【問題文】に「Webショップの店舗番号は “A09999” とする。」とあるため、受注テーブル（表2）から 受注店舗番号 = "A09999" のレコードを抜き出します。
- 指定期間は【小問説明】にある「2010年3月1日 〜 2010年3月31日」。この期間に該当し、かつ 受注店舗番号 = "A09999" の受注番号は “50005”, “50006”, “50008” の3件です。
-
受注明細との結合
- 受注明細テーブル（表3）を上記3つの受注番号で絞り込みます。該当明細は次の3行です。
• 受注番号 “50005” ： 出荷店舗番号 “A01002” ／ 受注金額113000
• 受注番号 “50006” ： 出荷店舗番号 “A01001” ／ 受注金額36700
• 受注番号 “50008” ： 出荷店舗番号 “A01002” ／ 受注金額120000
- 受注明細テーブル（表3）を上記3つの受注番号で絞り込みます。該当明細は次の3行です。
-
出荷店舗別の集計
- 出荷店舗番号で GROUP BY し、SUM(受注金額) を算出します。
• “A01001” ： 36700
• “A01002” ： 113000 + 120000 = 233000
- 出荷店舗番号で GROUP BY し、SUM(受注金額) を算出します。
-
出力項目
- 図3の SQL は SELECT m.出荷店舗番号, SUM(m.受注金額) AS Web売上分 … GROUP BY m.出荷店舗番号 という構造なので、表示される列は
① 出荷店舗番号
② Web売上分 - 以上から回答は次のとおりです。
- 図3の SQL は SELECT m.出荷店舗番号, SUM(m.受注金額) AS Web売上分 … GROUP BY m.出荷店舗番号 という構造なので、表示される列は
誤りやすいポイント
- 「受注店舗番号」と「出荷店舗番号」を取り違える
- Web集計では “A09999” で受注したレコードだけを対象にし、出荷店舗で GROUP BY する点に注意が必要です。
- 受注テーブル側の日付条件を忘れる
- 指定期間に該当しない受注番号を含めると金額が過大になります。
- 明細行を重複して合計してしまう
- 受注明細の JOIN 条件は受注番号のみ。意図せずクロス結合になっていないかを必ず確認します。
FAQ
Q: 受注明細に複数商品がある場合、受注金額の列は単価ですか総額ですか？
A: 表3の “受注金額” は明細ごとの金額です。よって同じ受注番号に複数行あれば、その都度加算されます。
A: 表3の “受注金額” は明細ごとの金額です。よって同じ受注番号に複数行あれば、その都度加算されます。
Q: Webショップ自身（店舗番号 “A09999”）の売上は表示されないのですか？
A: 出荷店舗別に配分する仕様のため、Webショップ自身は GROUP BY の対象外です。
A: 出荷店舗別に配分する仕様のため、Webショップ自身は GROUP BY の対象外です。
Q: 期間条件を WHERE ではなく JOIN 内に書くメリットは？
A: 受注テーブルを事前に絞り込んでから結合するため、不要データの結合を防ぎパフォーマンスが向上します。
A: 受注テーブルを事前に絞り込んでから結合するため、不要データの結合を防ぎパフォーマンスが向上します。
関連キーワード: GROUP BY, INNER JOIN, 集計関数, BETWEEN, 外部結合
設問2：
問題文を見る本文中のdに入れる適切な字句を15字以内で答えよ。
模範解答
d：「受注情報が存在しない」
または
「売上がない」
解説
解答の導き方
-
図2のSQLの構造を確認します。問題文には「FROM (店舗 t INNER JOIN (SELECT j.受注店舗番号、j.受注番号 FROM 受注 j WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p ON t.店舗番号 = p.受注店舗番号) INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号」とあります。ここで注目すべきは「INNER JOIN」が使われている点です。
-
INNER JOIN の意味を考えます。INNER JOIN は結合条件を満たす行だけを結果に残します。つまり、店舗 t とサブクエリ p を結合する段階で「p に該当する受注（＝指定月内の受注）がない店舗」は結合結果に含まれません。
-
以上より、問題文の空欄 d に入るのは「受注情報が存在しない」または同義の「売上がない」です。最も適切な字句は 「受注情報が存在しない」 です。
誤りやすいポイント
-
INNER JOIN と LEFT OUTER JOIN の違いを誤解する
INNER JOIN は結合条件を満たす行のみを出力するため、期間内に受注がない店舗はそもそも集計対象になりません。売上ゼロを表示したい場合は店舗を基点に LEFT OUTER JOIN を使う必要があります。 -
集計関数 SUM() の挙動の誤解
LEFT OUTER JOIN にしても、結合の結果として集計対象列に非NULLの値が1つもないと SUM() は NULL を返します（自動的に 0 にはなりません）。表示上 0 にしたければ COALESCE(SUM(m.受注金額), 0) のように NULL を 0 に置き換える必要があります。 -
集計のキーを間違える（受注店舗と出荷店舗の混同）
Webショップ分を出荷店舗に配分する要件があるのに、受注店舗で集計してしまうと Webショップ（A09999）に売上が偏って表示されます。目的に応じて GROUP BY のキー（受注の受注店舗番号か、受注明細の出荷店舗番号か）を正しく選ぶことが必要です。 -
サブクエリや結合順序の見落とし
図2のようにサブクエリで期間絞り込みを行っている場合、そのサブクエリとの結合方法が結果に直接影響します。結合の順序・種類（内外部結合）を意識してSQLを読むことが重要です。
FAQ
Q: 図2のSQLを簡単に修正して、期間内に受注がない店舗も出力するにはどうすればよいですか？
A: 店舗 t を基点にして売上データを左外部結合（LEFT OUTER JOIN）する形に変更します。さらに、表示上 0 にしたければ COALESCE(SUM(m.受注金額), 0) を使います。概念的には「店舗を左側にして LEFT OUTER JOIN で集計データを結合」すればよいです。
A: 店舗 t を基点にして売上データを左外部結合（LEFT OUTER JOIN）する形に変更します。さらに、表示上 0 にしたければ COALESCE(SUM(m.受注金額), 0) を使います。概念的には「店舗を左側にして LEFT OUTER JOIN で集計データを結合」すればよいです。
Q: なぜ SUM(m.受注金額) がNULLになることがあるのですか？
A: 集計対象となる行が存在せず、集計列に対して非NULLの値が一つもない場合、SUM() はNULLを返します。NULLを0にしたい場合は COALESCE(SUM(...), 0) を使います。
A: 集計対象となる行が存在せず、集計列に対して非NULLの値が一つもない場合、SUM() はNULLを返します。NULLを0にしたい場合は COALESCE(SUM(...), 0) を使います。
Q: Webショップ分を出荷店舗ごとに集計するにはどう書けばよいですか？
A: 受注明細と受注を結合し、受注の受注店舗番号が 'A09999'（Webショップ）である行に絞り、出荷店舗番号で GROUP BY します。例（概念）:
SELECT m.出荷店舗番号, SUM(m.受注金額) FROM 受注明細 m JOIN 受注 j ON m.受注番号 = j.受注番号 WHERE j.受注店舗番号 = 'A09999' AND j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日 GROUP BY m.出荷店舗番号
A: 受注明細と受注を結合し、受注の受注店舗番号が 'A09999'（Webショップ）である行に絞り、出荷店舗番号で GROUP BY します。例（概念）:
SELECT m.出荷店舗番号, SUM(m.受注金額) FROM 受注明細 m JOIN 受注 j ON m.受注番号 = j.受注番号 WHERE j.受注店舗番号 = 'A09999' AND j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日 GROUP BY m.出荷店舗番号
関連キーワード: INNER JOIN、LEFT OUTER JOIN、集計関数 SUM、NULL、COALESCE
（1）図5中のeに入れる適切な字句を解答群の中から選び、記号で答えよ。
解答群
ア：EXCEPT
イ：INNER JOIN
ウ：INTERSECT
エ：LEFT OUTER JOIN
オ：UNION
模範解答
e：エ
解説
解答の論理構成
- 問題文によると、図4の結果では「集計の対象となる期間にd店舗の場合は、店舗ごとの売上の集計に出力されない」とあります。これは「受注が0件」の店舗が結果に現れないことを示しています。
- 図2のＳＱＬでは
店舗 t INNER JOIN (サブクエリ) p ON t.店舗番号 = p.受注店舗番号
という等価結合が行われており、サブクエリpに該当行が存在しない店舗は結合結果から除外されてしまいます。 - したがって、販売が無かった店舗も必ず左側（店舗表）に残す必要があります。等価結合ではなく、「左の行を必ず残し、右側が無い場合はNULLを補充する」外部結合を指定するのが正解です。
- 外部結合を表すキーワードは LEFT OUTER JOIN です。
- よって、図5中の e に入る字句は「LEFT OUTER JOIN」、解答群では「エ」です。
誤りやすいポイント
- INNER JOIN と LEFT OUTER JOIN の機能差をあいまいに覚えていると、売上ゼロの店舗が欠落する原因に気付けません。
- サブクエリ側を基準にした RIGHT OUTER JOIN でも論理的には解決できますが、設計時点で基表（店舗）を左側に置く方が自然です。
- UNION など集合演算子で補うと考えがちですが、単純な結合条件漏れの問題であり集合演算子は不要です。
FAQ
Q: なぜ FULL OUTER JOIN ではなく LEFT OUTER JOIN なのですか？
A: 必要なのは「店舗に対応する受注が無い場合も店舗を残す」ことだけで、逆に「受注はあるが店舗表に存在しない」ケースは発生しない前提だからです。
A: 必要なのは「店舗に対応する受注が無い場合も店舗を残す」ことだけで、逆に「受注はあるが店舗表に存在しない」ケースは発生しない前提だからです。
Q: 受注テーブルに存在しない店舗番号が紛れ込んだ場合はどうなりますか？
A: LEFT OUTER JOIN では店舗表に基づいて行を生成するため、受注側にだけ存在する店舗番号は結果に現れません。データ整合性は別途チェックが必要です。
A: LEFT OUTER JOIN では店舗表に基づいて行を生成するため、受注側にだけ存在する店舗番号は結果に現れません。データ整合性は別途チェックが必要です。
Q: サブクエリをビューにしても同じ効果がありますか？
A: はい、結合方法（LEFT OUTER JOIN）を維持すれば、サブクエリをビュー化しても結果は同じです。
A: はい、結合方法（LEFT OUTER JOIN）を維持すれば、サブクエリをビュー化しても結果は同じです。
関連キーワード: 外部結合, 等価結合, NULL補完, 集計関数, サブクエリ
模範解答
解説
解答の論理構成
-
図2のSQLでは
「SELECT t.店舗番号、t.店舗名、SUM(m.受注金額) AS 金額
FROM (店舗 t INNER JOIN (SELECT j.受注店舗番号, j.受注番号 FROM 受注 j
WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p
ON t.店舗番号 = p.受注店舗番号)
INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号」
と “INNER JOIN” を二重に用いています。INNER JOIN は両テーブルに一致行が存在しない場合、行を落としてしまいます。 -
その結果、Z係長が指摘したとおり「集計の対象となる期間にd店舗の場合は、店舗ごとの売上の集計に出力されない」という現象が発生します。具体的には3月中に1件も受注がない店舗が除外されます。
-
図5のSQLでは、FROM 句の最初が
「FROM 店舗 t e (SELECT …) p」
となり、e には “LEFT OUTER JOIN” が入ります。LEFT OUTER JOIN は左表（店舗）をすべて残すため、受注がない店舗も行が生成されます。 -
生成された行は受注明細が無いので SUM(m.受注金額) がNULLになります。
-
以上より、図5を実行すると図4に追加される行は1行だけで、内容は
・店舗番号：A01003
・店舗名：渋谷店
・金額：null
となります。
誤りやすいポイント
- INNER JOIN と LEFT OUTER JOIN の役割を混同し、売上０円の店舗が「０」として出力されると勘違いする。実際には一致行が無いため NULL になる。
- 「受注が無い店舗＝売上０円」と「受注はあるが金額が０円」の違いを区別し忘れ、後者までNULL行に含めてしまう。
- 取得結果を比較するときに店舗テーブル自体の行数を数え忘れ、追加される行数を過大・過少に見積もる。
FAQ
Q: 金額がnullではなく0にしたい場合、どう修正すれば良いですか？
A: SELECT 句で COALESCE(SUM(m.受注金額),0) などの関数を使い、NULL を０に置き換えます。
A: SELECT 句で COALESCE(SUM(m.受注金額),0) などの関数を使い、NULL を０に置き換えます。
Q: RIGHT OUTER JOIN でも同じ結果になりますか？
A: 右側に店舗テーブルを置けば理論上可能ですが、可読性やメンテナンス性を考えると、左側に基準テーブルを置く LEFT OUTER JOIN の方が一般的です。
A: 右側に店舗テーブルを置けば理論上可能ですが、可読性やメンテナンス性を考えると、左側に基準テーブルを置く LEFT OUTER JOIN の方が一般的です。
Q: 集計月を動的に変えるとき、パフォーマンスが落ちます。対策はありますか？
A: 受注日付で絞ったサブクエリにインデックスを張る、ビュー化して統計情報を最新化する、等が有効です。
A: 受注日付で絞ったサブクエリにインデックスを張る、ビュー化して統計情報を最新化する、等が有効です。
関連キーワード: INNER JOIN, LEFT OUTER JOIN, NULL, 集計関数, サブクエリ
