令和4年度 秋期 午前Ⅱ 問7
データ操作
AVGに関する問題
“商品”表と“商品別売上実績”表に対して，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 の 50、NULL、250 から NULL を除き平均150です。
左外部結合なので、商品表の6行は全て残り、売上実績の無い商品は売上合計金額が NULL になります。WHERE で商品ランクが A の行に絞ると、S001 の 50、S002 の NULL、S003 の 250 の3行です。AVG などの集合関数は NULL を計算の対象から除くので、平均は NULL でない2行で計算し、(50＋250)÷2＝150 となります。GROUP BY 商品ランクでランク A の1グループになるので、結果は150の1行です。集合関数は NULL の行を個数にも合計にも入れない、と覚えておけば、外部結合で生じた NULL の扱いを誤りません。
ほかの選択肢はなぜ違うのか
- ア100：100 は、NULL を 0 として3行に含め、(50＋0＋250)÷3 と計算した値です。AVG は NULL の行を分母にも数えないので、S002 を加えて3で割るのは誤りです。
- ウ225：225 は、商品ランク A の3行からは、NULL をどう扱っても導けない値です。NULL を除けば150、0として数えれば100になり、この絞り込みの結果として225が出る計算はありません。
- エ275：275 は、商品別売上実績表の4行を全て使い、(50＋250＋350＋450)÷4 と計算した値です。WHERE の商品ランクが A という条件を無視しており、ランク B や C の商品まで平均に入っています。
出典：令和4年度 秋期 データベーススペシャリスト試験 午前Ⅱ 問7
この解説に誤りを見つけたら教えてください。直して、直した記録を残します。誤りを報告する（メールが開きます）