SQLの集計単位と表示順をどう指定するか
基本情報技術者試験 平成31年度春期 午前 問27 ／ データベース › データ操作
過去問にチャレンジ
“中間テスト”表からクラスごと，教科ごとの平均点を求め，クラス名，教科名の昇順に表示するSQL文中のaに入れる字句はどれか。
- ア
GROUP BY クラス名，教科名 ORDER BY クラス名，AVG(点数)
- イ
GROUP BY クラス名，教科名 ORDER BY クラス名，教科名
- ウ
GROUP BY クラス名，教科名，学生番号 ORDER BY クラス名，教科名，平均点
- エ
GROUP BY クラス名，平均点 ORDER BY クラス名，教科名
会話形式の解説
講師平均を出す単位と、結果を並べる順番を分けて考えましょう。まず、どの行を一組にしますか。
受験者クラスごと、教科ごとなので、クラス名と教科名の組合せで一組にします。
講師そのとおりです。GROUP BYは、集計するときにどの行を一組にするかを指定します。ここにはクラス名と教科名が必要です。
受験者学生番号も加える選択肢がありますが、細かく分けたほうが正確に思えます。
講師学生番号を加えると、同じクラス・教科でも学生番号ごとに分かれます。求めたい組合せ単位の平均にはなりません。
受験者では、教科名を入れず、クラス名と平均点でまとめる指定も違いますね。
講師はい。教科名がなければ、クラスと教科の組合せごとにまとめられません。次は結果の表示順です。何を先に並べますか。
受験者クラス名を先にして、その中で教科名の順に並べます。
講師ORDER BYは、集計後の行を表示する順番を指定します。クラス名、教科名の順に指定します。
受験者クラス名の次に平均点で並べる指定では、集計単位は合っていても、教科名の順にはなりませんね。
講師そうです。クラス名と教科名でまとめ、同じ順番で並べる選択肢が条件に合います。ASCを省略しても昇順です。
受験者集計単位と表示順をそれぞれ確かめればよいのですね。並べ方だけ合っていても、まとめ方が違えば目的の平均点は得られません。
まとめ
クラス・教科ごとの平均点を求めるには、GROUP BYでクラス名と教科名を指定します。表示はORDER BYでクラス名、教科名の順に指定し、ASCを省略すると昇順になります。学生番号を集計単位に加えたり、平均点で並べたりすると、設問の条件から外れます。正解はイです。
解答・解説
正解はイです
この問題のテーマ
集計する単位と、集計結果を表示する順序をSQLでどう指定するかを問う問題です。
解き方の考え方
まず、平均点を求める単位はクラスと教科の組合せなので、GROUP BYには両方の列が必要です。次に、表示順はクラス名を先、教科名を後にしてORDER BYで指定します。ASCを省略した場合は昇順です。
選択肢ごとの解説
- ×ア GROUP BY クラス名，教科名 ORDER BY クラス名，AVG(点数)クラスと教科の組合せで集計する点は合っています。しかし、クラス内の並びを決めるのが教科名ではなく平均点なので、指定された表示順になりません。
- ○イ GROUP BY クラス名，教科名 ORDER BY クラス名，教科名クラス名と教科名でグループ化するため、各組合せの平均点を求められます。表示もクラス名、教科名の順に昇順となり、設問の条件を満たします。
- ×ウ GROUP BY クラス名，教科名，学生番号 ORDER BY クラス名，教科名，平均点GROUP BYに学生番号まで加えると、同じクラス・教科でも学生番号ごとに分かれます。そのため、求めたいクラス・教科単位の平均にはなりません。
- ×エ GROUP BY クラス名，平均点 ORDER BY クラス名，教科名GROUP BYに教科名が含まれていません。クラスと教科の組合せごとに行をまとめられないため、表示順の指定が合っていても目的の集計にはなりません。
POINT
- GROUP BYは、平均を計算する際にどの行を一組として扱うかを決めます。
- ORDER BYは、集計後の行をどの列の順に表示するかを決めます。
- 並べ替えに使う列が適切でも、集計単位の指定が違えば目的の結果は得られません。