システム監査技術者 2017年 午前2 問17
問題文
“商品”表と“商品別売上実績”表に対して、SQL文を実行して得られる売上平均金額はどれか。
〔SQL文〕
SELECT AVG(売上合計金額) AS 売上平均金額
FROM 商品 LEFT OUTER JOIN 商品別売上実績
ON 商品.商品コード = 商品別売上実績.商品コード
WHERE 商品ランク = 'A'
GROUP BY 商品ランク
選択肢
ア：100
イ：150（正解）
ウ：225
エ：275
🔒 解説は解答すると表示されます
LEFT OUTER JOINとAVG【午前2解説】
正解の理由
商品ランクがAの商品は、S001、S002、S003の3件です。商品別売上実績表と結合すると、次のようになります。
S002には売上実績がないため、LEFT OUTER JOINでは商品側の行が残り、売上合計金額はNULLになります。
SQLのAVG関数は、集計対象のNULLを除外して平均を計算します。したがって、平均の対象は50と250です。
よって、売上平均金額は150であり、イが正解です。
解法ステップ
- WHERE句で商品ランクがAの商品に絞ります。対象はS001、S002、S003です。
- 商品コードを使って商品別売上実績表と結合します。
- 売上実績がないS002も、LEFT OUTER JOINによって結果に残ります。ただし、その売上合計金額はNULLです。
- AVG関数はNULLを除外して平均を計算します。
- 50と250の平均を求め、150と判断します。
選択肢別の誤答解説
-
ア：100
売上実績がないS002を売上0として、3商品で平均した場合の値です。しかし、売上金額はNULLであり、AVGはNULLを平均の対象に含めません。 -
イ：150
NULLを除き、実績のある50と250の平均を計算した値です。 -
ウ：225
Aランク商品の実績だけを使い、NULLをどう扱っても得られる平均ではありません。対象行を確認し、AVGがNULLを除外する点を押さえてください。 -
エ：275
50、250、350、450という全商品の売上実績を平均した値です。商品ランクAに絞るWHERE句が反映されていません。
よくある誤解
- LEFT OUTER JOINで結合相手が見つからないと、行そのものが消えると思い込むことがあります。左側の表の行は残り、右側の列がNULLになります。
- NULLを自動的に0として扱うと思い込むことがあります。NULLと0は異なり、AVGはNULLを除外します。
- GROUP BYがあると複数の平均が必ず出ると思い込むことがあります。この問題ではAランクの商品だけに絞られるため、商品ランクAのグループが1つできます。
補足コラム
LEFT OUTER JOINは、左側の表の行をすべて残し、右側の表に対応する行がない場合は右側の列をNULLにします。
一方、AVGやSUMなどの集計関数は、通常、集計対象のNULLを無視します。したがって、「売上実績がない商品を平均計算に含めたい」という要件がある場合は、NULLを0に置き換えるなどの処理が必要です。
FAQ
売上実績がない商品を0円として平均に含めるには、どうすればよいですか？
COALESCE関数などでNULLを0に置き換えてから平均を計算します。例えば、
AVG(COALESCE(売上合計金額, 0))のようにします。WHERE句ではなく、ON句に商品ランクの条件を書いた場合も同じですか？
同じとは限りません。LEFT OUTER JOINでは、ON句の条件は結合相手を探す条件であり、WHERE句は結合後の行を絞る条件です。特に右側の表の条件をWHERE句に書くと、NULLの行が除外され、外部結合の効果が変わることがあります。
関連キーワード: LEFT OUTER JOIN、AVG関数、NULL、GROUP BY、外部結合
\ せっかくなら /
システム監査技術者を
クイズ形式で学習しませんか？
クイズ画面へ遷移する→
すぐに利用可能！