Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう!(参考記事:「Oracle 10g対応のORACLE MASTERとは?」)
本連載第3回「SQLで使える関数の基礎知識」、第4回「SQLの関数を使いこなす」の2回にわたって、単一行関数を紹介しました。第5回に当たる今回は、グループ関数を学びましょう。
第5回 SQLの関数でデータを集計する
1.確認しておきたい内容
2.グループ関数
単一行関数が1行の入力値に対して1つの値を戻すのに対し、グループ関数は複数行の入力値に対して1つの値を戻します。グループ関数には以下のものがあります。
グループ関数 | 説明 | 例(c1列に100、200、300、400、 NULLの5つの値があると想定) |
---|---|---|
AVG | 平均値 | AVG(c1)→250 |
COUNT | 行数。*を使用すると、NULL値を含めた行数を求める | COUNT(*)→5 COUNT(c1)→4 |
MAX | 最大値 | MAX(c1)→400 |
MIN | 最小値 | MIN(c1)→100 |
SUM | 合計値 | SUM(c1)→1000 |
STDDEV | 標準偏差 | STDDEV(c1)→129.099445 |
VARIANCE | 平方偏差 | VARIANCE(c1)→16666.6667 |
COUNT(*)を除き、NULL値は無視されます。NULL値を計算(個数)に含めるのであれば、NVL関数などを使用して値に置き換える必要があります。
各グループ関数では、DISTINCTを使用して重複値を排除することもできます。
■問題1
CUST表から、ロサンゼルス(Los Angeles)に住んでいる顧客のロケーション(loc)ごとの人数を求めている文を選択しなさい。
a.SELECT COUNT(DISTINCT loc) FROM cust WHERE address = 'Los Angeles';
b.SELECT COUNT DISTINCT loc FROM cust WHERE address = 'Los Angeles';
c.SELECT DISTINCT(COUNT loc) FROM cust WHERE address = 'Los Angeles';
d.SELECT COUNT(loc) FROM cust WHERE address = 'Los Angeles';
正解:a
■解説
前回の宿題とした問題です。まず、問題文があいまいであることに注意してください。問題には「ロケーションごとの人数」とありますが、選択肢を見ると実際に求めるのは「ロケーションの種類数」であると考えられます。
ロケーションの種類数ということは、重複したロケーションを取り除いた値の種類数を求めればよいということです。正解aのようにCOUNT関数にDISTINCTを使用することで、重複した値を取り除いたカウント、つまり種類数を取得することができます。
DISTINCTを使用しない選択肢dでは、重複したロケーションもカウントされてしまいます。
選択肢cのように順序を逆にして記述しないように注意しましょう。選択肢bのようにCOUNT関数にカッコがないのも構文エラーとなります。
なお、本当にロケーションごとの人数を求める場合は、次のようにグループ化するのが正しいです。
SELECT loc, COUNT(*) FROM cust WHERE address = 'Los Angeles' GROUP BY loc; |
なぜこのような問題を掲載するかといいますと、実際の試験において、このようなあいまいな問題文と選択肢で出題されることがあるためです。実際の試験でこういった問題が出されたら、選択肢の中から最も問題の意図に近いものを選択するようにしましょう。
3.GROUP BY句を使用したデータのグループ化
表を列値によってグループ化し、そのグループごとにグループ関数結果を取得するには、GROUP BY句を使用します。
SELECT {* | [DISTINCT] 列名 | 式} [列別名] [,...] FROM 表名 [WHERE 条件式] GROUP BY グループ列名 [,...] ORDER BY {列名 | 式 | 列別名 | 列位置}[,...] [ASC | DESC]; |
SELECT句の列リストにグループ関数でない列が含まれる場合、必ずGROUP BY句のグループ列に含める必要があります。
GROUP BY句では、列名のみ使用できます。ORDER BY句のように列別名や列位置を指定することはできません。
SQL> SELECT deptno, COUNT(*) FROM emp GROUP BY deptno; DEPTNO COUNT(*) ---------- ---------- 10 3 20 5 30 6 |
■問題1
次のSTUDENTS表の定義を確認してください。
STUDENT_ID | NUMBER | 学生番号 |
---|---|---|
SELECTOR_END | DATE | 期末の日付 |
SUBJ1 | NUMBER | 科目別得点 |
SUBJ2 | NUMBER | 科目別得点 |
SUBJ3 | NUMBER | 科目別得点 |
実行時にエラーとならないSELECT文を選択しなさい。
a.SELECT MAX(subj1, subj2, subj3) FROM student;
b.SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM student
GROUP BY selector_end;
c.SELECT SUM(subj1, subj2, subj3) FROM students
GROUP BY selector_end;
d.SELECT MINIMUM(subj1, subj2, subj3) FROM students;
正解:b
■解説
グループ関数は複数の値を受け取って1つの値を戻しますが、複数列を受け取るものではありません。単一の引数を受け取る必要があります。
正解はbです。そのほかの選択肢の不正解の理由は次のとおりです。
■選択肢a:MAXに複数の引数を記述することはできません。2つの値を連結してMAXを調べる(MAX(subj1 || subj2)ならば可能です。
■選択肢c:解説のとおり、グループ関数であるSUMに複数の引数を記述することはできません。
■選択肢d:MINIMUMというグループ関数はありません。
■問題2
次のSTUDENTS表の定義を確認してください。
STUDENT_ID | 学生番号 |
---|---|
SELECTOR_END | 期末の日付 |
GPA | 得点平均 |
期末ごとの得点平均合計を取得しているSELECT文を選択しなさい。
a.SELECT selector_end, gpa FROM student
WHERE gpa IS NOT NULL
GROUP BY selector_end;
b.SELECT selector_end, SUM(gpa) FROM student
WHERE gpa IS NOT NULL
GROUP BY selector_end;
c.SELECT selector_end, gpa FROM student
GROUP BY selector_end
WHERE gpa IS NOT NULL;
d.SELECT selector_end, SUM(gpa) FROM student
GROUP BY selector_end
WHERE gpa IS NOT NULL;
正解:b
■解説
WHERE句とGROUP BY句では、WHERE句を先に記述する必要があります。選択肢dのようにGROUP BY句を先に記述してしまった場合は、構文エラーとなります。
選択肢a、選択肢cでは、GROUP BY句で指定していないgpa列をSELECT列リストに記述しているため、グループ化ができずエラーとなります。GROUP BY句を使用するときはグループ関数を使用し、グループ関数でない列はGROUP BY句に含める必要があります。忘れないようにしましょう。
■問題3
次のSTUDENTS表の定義を確認してください。
STUDENT_ID | NUMBER | 学生番号 |
---|---|---|
SELECTOR_END | DATE | 期末の日付 |
SUBJ1 | NUMBER | 科目別得点 |
SUBJ2 | NUMBER | 科目別得点 |
次のSELECT文の結果として正しいものを選択しなさい。
SELECT student_id, AVG(subj1), AVG(subj2) FROM student WHERE AVG(subj1) > 8 AND AVG(subj2) > 8 GROUP BY student_id ORDER BY AVG(subj1); |
a.正しく学生別の平均科目別得点が表示される
b.WHERE句でエラーとなる
c.GROUP BY句でエラーとなる
d.ORDER BY句でエラーとなる
正解:b
■解説
WHERE句は、グループ化する前に行を制限します。そのため、WHERE句でグループ関数を使用することはできません。
SQL> SELECT student_id, AVG(subj1), AVG(subj2) 2 FROM student 3 WHERE AVG(subj1) > 8 AND AVG(subj2) > 8 4 GROUP BY student_id 5 ORDER BY AVG(subj1); WHERE AVG(subj1) > 8 AND AVG(subj2) > 8 * 行3でエラーが発生しました。: ORA-00934: ここではグループ関数は使用できません。 |
グループ関数を使用するのであれば、HAVING句を使用します。HAVING句を使用すれば、この問題は正しく実行できます。
SQL> SELECT student_id, AVG(subj1), AVG(subj2) 2 FROM student 3 HAVING AVG(subj1) > 8 AND AVG(subj2) > 8 4 GROUP BY student_id 5 ORDER BY AVG(subj1); |
4.HAVING句を使用した行の制限
GROUP BY句を使用して行をグループ化する場合、WHERE句による行の制限とHAVING句による行の制限が行えます。
WHERE句による制限 | HAVING句による制限 | |
---|---|---|
制限のタイミング | グループ化する前 | グループ化した後 |
条件式でのグループ関数 | 使用不可能 | 使用可能 |
SELECT {* | [DISTINCT] 列名 | 式} [列別名] [,...] FROM 表名 [WHERE 条件式] GROUP BY グループ列名 [,...] HAVING 条件式 ORDER BY {列名 | 式 | 列別名 | 列位置}[,...] [ASC | DESC]; |
グループ関数を使用しない条件式ではWHERE句もHAVING句も指定できますが、その場合はWHERE句を使用しましょう。WHERE句でグループ化する前に制限をすることでインデックススキャンができる可能性が高くなりますのでお勧めです。
■問題1
WHERE句とHAVING句の特徴に関する説明として正しいものを2つ選択しなさい。
a.WHERE句は行を制限する
b.WHERE句はグループを制限する
c.HAVING句は行を制限する
d.HAVING句は行とグループを制限する
e.WHERE句とHAVING句は同時に使用できない
正解:a、d
■解説
WHERE句とHAVING句はどちらも取得する行を制限するために使用します。HAVING句では、加えてグループを制限することもできるのが特徴です。通常は行を制限する場合はWHERE句、グループを制限する場合はHAVING句を使用します。行を制限するときにHAVING句を使用してしまうと、グループ化した後に制限することになるため、非効率なSELECT文となることが多いです。
問題にある選択肢cは間違ってはいませんが、すべてを表しているわけではないため、正解dの方が適切です。このような引っ掛け問題も出されますので、選択肢の中で最も適切なものを選べるようにしておきましょう。
■問題2
次のSTUDENTS表の定義を確認してください。
STUDENT_ID | 学生番号 |
---|---|
SELECTOR_EN | D期末 |
GRA | 得点ランク |
教務部では、学生の落第点を計算するために、1999年12月30日および2000年6月30日付の得点ランク平均が2.0未満の学生を求める文を実行しました。どの文が正しく実行されますか。
a.SELECT student_id, AVG(gra)
FROM student
WHERE selector_end IN ('1999-12-30', '2000-06-30')
GROUP BY student_id
HAVING AVG(gra) < 2.0;
b.SELECT student_id, AVG(gra)
FROM student
WHERE AVG(gra) < 2.0
GROUP BY student_id;
c.SELECT student_id, AVG(gra)
FROM student
WHERE selector_end BETWEEN '1999-12-30' AND '2000-06-30'
GROUP BY student_id
HAVING gra < 2.0;
d.SELECT student_id, AVG(gra)
FROM student
WHERE selector_end BETWEEN '1999-12-30' AND '2000-06-30'
GROUP BY AVG(gra)
HAVING AVG(gra) < 2.0;
正解:a
■解説
この問題のポイントは以下のとおりです。
■1999年12月30日および2000年6月30日付
WHERE句を使用して「selector_end」を制限します。特定の日付を求めるので、IN演算子を使用した「selector_end IN ('1999-12-30', '2000-06-30')」が正解です。
■得点ランク平均が2.0未満
「AVG(gra) < 2.0」を求めるにはHAVING句を使用します。
正解はaです。そのほかの選択肢の不正解の理由は次のとおりです。
■選択肢b:WHERE句で「AVG(gra) < 2.0」を行うことはできません。また、問題文の「1999年12月30日および2000年6月30日付」を満たしていません。
■選択肢c:HAVING句で使用する条件句も、グループ関数でない場合は、GROUP BY句で使用している式でなければなりません。
■選択肢d:GROUP BY句にグループ関数は使用できません。GROUP BY句はグループを作るための列を指定します。
次の内容をチェックしておきましょう。
次回は、「複数の表からのデータの表示」を確認します。次の宿題を解いておいてください。
非等価結合を使用するのに適した状況を2つ選択しなさい。
a.結合する列に同じ値が存在しない
b.値の範囲で結合する
c.主キーと外部キーの関係がある
d.3つ以上の表を結合する
Copyright © ITmedia, Inc. All Rights Reserved.