平成29年度 春期 午前Ⅱ 問17
データベース
NULLに関する問題
“商品”表と“商品別売上実績”表に対して，SQL文を実行して得られる売上平均金額はどれか。
商品
| 商品コード | 商品名 | 商品ランク |
|---|---|---|
| S001 | PPP | A |
| S002 | QQQ | A |
| S003 | RRR | A |
| S004 | SSS | B |
| S005 | TTT | C |
| S006 | UUU | C |
商品別売上実績
| 商品コード | 売上合計金額 |
|---|---|
| S001 | 50 |
| S003 | 250 |
| S004 | 350 |
| S006 | 450 |
〔SQL文〕
SELECT AVG(売上合計金額) AS 売上平均金額
FROM 商品 LEFT OUTER JOIN 商品別売上実績
ON 商品.商品コード = 商品別売上実績.商品コード
WHERE 商品ランク = 'A'
GROUP BY 商品ランク- ア100
- イ150
- ウ225
- エ275
答えと解説を見る
✓ これが正解イ150
解説
Aランク3商品のうち売上のある2件を平均して、150になります。
外部結合と集約関数の組合せは、どの行が残り、そのとき値が何になるかを軸に計算します。商品表を左にした左外部結合なので、商品表の6行は売上実績が無くても全て残り、相手の無い行の売上合計金額は NULL になります。WHERE 句で商品ランクが A の行に絞ると、S001（50）、S002（NULL）、S003（250）の3行が残ります。AVG は NULL を計算に含めないので、値のある2行だけで平均を取り、(50＋250)÷2＝150 です。GROUP BY 商品ランクで A のグループ1つにまとまるため、結果も150の1行になります。外部結合で生まれた NULL は AVG の分母に入らない、という点を押さえておくと計算を誤りません。
ほかの選択肢はなぜ違うのか
- ア100：100は、S002 の NULL を0として分母に数え、(50＋0＋250)÷3 と計算した値です。AVG は NULL を平均の対象から外すので、分母は3ではなく2になり、この値にはなりません。
- ウ225：225は、この SQL の条件からは導けません。A ランクの3行で計算すると、NULL を外せば150、0として数えても100であり、内部結合に変えても150なので、どの扱いでも225にはなりません。
- エ275：275は、商品ランクの条件を無視して、売上実績表の4件をすべて平均した値です。(50＋250＋350＋450)÷4＝275 となりますが、WHERE 句で A ランクに絞っているので、B や C の商品の売上は含まれません。
出典：平成29年度 春期 システム監査技術者試験 午前Ⅱ 問17
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）