0. 実験ユーザー「dicdic」でコネクトする。
SQL> conn dicdic/Oracle11g
接続されました。
SQL>
|
1. 入学後に死んでしまった人(在籍区分コード=13)が2人いる。
SQL> SELECT 氏名 FROM TT01_学生 Where 在籍区分コード='13';
氏名
----------------------------------------
坂東 徳男
岩井 尚美
SQL>
|
2. 【AVG関数】
グループ関数の一つであるAVG関数は、平均を算出することができる。
死んでしまった2人のTOEICの平均点を出してみよう。
下のように、690点と出た。果たして、この数字は本当か....。
SQL> SELECT AVG(TOEIC) FROM TT01_学生 Where 在籍区分コード='13';
AVG(TOEIC)
----------
690
SQL>
|
3. この二人、よく見ると1人は未受験のようだ。
つまり、AVG関数はデフォルトで、NULLを除外して平均を出す。
この算出の仕方が有難いのか、有難くないのかは、ビジネスシーンによって変わってくるので要注意。
SQL> SELECT 氏名,TOEIC FROM TT01_学生 Where 在籍区分コード='13';
氏名 TOEIC
---------------------------------------- ----------
坂東 徳男 690
岩井 尚美
SQL>
|
4. では、Nullを0として扱って平均を出すにはどうするだろう？
関数のネストになるので多少判りづらいが、NVL関数を使えばよい。(NVL関数についてはこちら)
SQL> SELECT AVG(NVL(TOEIC,0)) FROM TT01_学生 Where 在籍区分コード='13';
AVG(NVL(TOEIC,0))
-----------------
345
SQL>
|
5. 【GROUP BY句】
上記1〜4の例では、平均点を出す範囲をWhere句で指定したが、1年生の平均、2年生の平均....と、
ケースごとにSQL文を一つ一つ作っていては大変。そこで、GROUP BY句というのを使える。
グループとしてまとめる単位を指定できるのだ。
血液型ごとのTOEICの平均点を出してみよう。.....おっと、しかしこれではよくわからん。
SQL> SELECT AVG(TOEIC) FROM TT01_学生 GROUP BY 血液型;
AVG(TOEIC)
----------
571.272618
554.561331
575.940299
549.130372
SQL>
|
6. 5.の例では、算出された平均点がどのグループに対応しているのかまったくわからない。
行ヘッダをつけたければ、血液型の列もSelectすればよい。
SQL> SELECT DECODE(血液型,1,'A',2,'B',3,'O',4,'AB'),AVG(TOEIC) FROM TT01_学生 GROUP BY 血液型;
DE AVG(TOEIC)
-- ----------
A 571.272618
B 554.561331
AB 575.940299
O 549.130372
SQL>
|
7. もう少し改良。ソートしてみた。
SQL> SELECT DECODE(血液型,1,'A',2,'B',3,'O',4,'AB'),AVG(TOEIC) FROM TT01_学生 GROUP BY 血液型 ORDER BY 血液型;
DE AVG(TOEIC)
-- ----------
A 571.272618
B 554.561331
O 549.130372
AB 575.940299
SQL>
|
8. さらに改良。四捨五入してみた。
SQL> SELECT DECODE(血液型,1,'A',2,'B',3,'O',4,'AB'),ROUND(AVG(TOEIC),1) FROM TT01_学生 GROUP BY 血液型 ORDER BY 血液型;
DE ROUND(AVG(TOEIC),1)
-- -------------------
A 571.3
B 554.6
O 549.1
AB 575.9
SQL>
|
9. さらに改良。未受験者の得点を0点とした平均点も併記してみた。
SQL> SELECT DECODE(血液型,1,'A',2,'B',3,'O',4,'AB'),
2 ROUND(AVG(TOEIC),1),ROUND(AVG(NVL(TOEIC,0)),1)
3 FROM TT01_学生 GROUP BY 血液型 ORDER BY 血液型;
DE ROUND(AVG(TOEIC),1) ROUND(AVG(NVL(TOEIC,0)),1)
-- ------------------- --------------------------
A 571.3 364.9
B 554.6 347.8
O 549.1 364
AB 575.9 372.2
SQL>
|
10. さらに改良。列ヘッダを判り易くしてみた。
SQL> SELECT DECODE(血液型,1,'A',2,'B',3,'O',4,'AB') 血液型,
2 ROUND(AVG(TOEIC),1) 平均点,ROUND(AVG(NVL(TOEIC,0)),1) "平均点(未受験は0点扱い)"
3 FROM TT01_学生 GROUP BY 血液型 ORDER BY 血液型;
血 平均点 平均点(未受験は0点扱い)
-- ---------- -----------------------
A 571.3 364.9
AB 575.9 372.2
B 554.6 347.8
O 549.1 364
SQL>
|
11. うーん。上の例だと血液型の列ヘッダが気に食わない。こういう場合、私ならスペースをパディングしちゃう。
AB型が最上行に表示されちったけど....。まぁヨシとしよう。
SQL> SELECT RPAD(DECODE(血液型,1,'A',2,'B',3,'O',4,'AB'),3,' ') 血液型,
2 ROUND(AVG(TOEIC),1) 平均点,ROUND(AVG(NVL(TOEIC,0)),1) "平均点(未受験は0点扱い)"
3 FROM TT01_学生 GROUP BY 血液型 ORDER BY 血液型;
血液型 平均点 平均点(未受験は0点扱い)
------ ---------- -----------------------
AB 575.9 372.2
A 571.3 364.9
B 554.6 347.8
O 549.1 364
SQL>
|
|