令和6年度秋期 データベーススペシャリスト 午前Ⅱ 問7

集計条件と表の対応を確認する視点

二つの表を組み合わせて売上の平均を求める問題では、結合対象と集計対象がどのように定まるかを整理し、SQL文の条件が結果集合へ与える影響に注目してください。慎重に

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

SQL文を読むとき、商品情報と売上実績の対応付け、抽出条件、平均の対象となる行の範囲が一貫しているかを確認することに焦点を絞って検討します。

視点

表同士の関連付けと集計処理を分けて捉え、どの行が平均の母集団に含まれる設計になっているかを確認する。

確認ポイント

• 結合に使われる表同士の対応関係を確認する
• 抽出条件が対象行の範囲にどう影響するかを見る
• 平均の対象列と集計前の行集合を対応させる

正解

イ

解説

WHERE句で商品ランクがAの商品だけに絞ると、対象はS001、S002、S003です。売上実績はそれぞれ50、NULL、250となり、AVGはNULLを除外して計算するため、平均は(50円＋250円)÷2件＝150円です。

理由

商品ランクAの商品はS001、S002、S003です。LEFT OUTER JOINによって、売上実績のあるS001には50円、S003には250円が結合され、実績のないS002の売上合計金額はNULLになります。AVGはNULLを集計対象から除外するため、(50円＋250円)÷2件＝150円です。

比較

LEFT OUTER JOINは実績のない商品も結合結果に残しますが、AVGはNULLを0円として扱いません。このため、3商品で合計を3件で割るのではなく、値が存在する2件だけを分母にします。したがって、売上実績のないS002を含めて平均を計算する選択肢は誤りです。

選択肢ア

これは不正解だよ。100円になるのは、売上実績の合計300円を3商品で割った場合に相当します。しかし、AVG関数はNULLを0円として分母に含めないため、実績がないS002を件数に加えてはいけません。対象となる売上金額は50円と250円の2件です。

選択肢イ（正解）

これは正解だよ。WHERE句で商品ランクAに絞ると、S001、S002、S003が対象になります。結合後の売上金額は50円、NULL、250円であり、AVGはNULLを除外します。そのため、(50円＋250円)÷2件＝150円となります。

選択肢ウ

これは不正解だよ。225円は、売上実績があるS001とS003の平均としては計算されません。これらの金額は50円と250円なので、合計は300円、件数は2件です。売上実績のないS002のNULLを除外して計算すると、平均は150円になります。

選択肢エ

これは不正解だよ。275円は、商品ランクAの対象に含まれない金額や、結合対象を誤って加えた場合に生じる値です。SQLのWHERE句はランクAだけを残し、AVGはその対象内の50円と250円だけをNULLを除いて集計するため、275円にはなりません。

学習のポイント

LEFT OUTER JOINとWHERE句、AVGにおけるNULLの扱いを正しく理解する。