Oracle SQL SELECT２ : SELECT文の基礎２(グループ化について)
SELECT文でのORDER BY句を使ったソート
SELECT文でのGROUP BY句を使ったグループ化
グループ化の式の範囲指定にはHAVING句
COUNT(),SUM(),MAX(),MIN(),AVG()
おすすめ書籍
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。
SELECT文の基本的な構文
SELECT <列名リスト> | <式リスト> FROM <表名リスト>
[WHERE <検索条件>]
[GROUP BY <列名リスト>]
[HAVING <グループ条件>]
[ORDER BY <式> [ASC | DESC] ...]
WHERE句についてはSELECT文その１で説明しましたが、今回GROUP BY句,HAVING句,ORDER BY句が
でてきました。GROUP BY句は検索列のグループ化を指示するもので、またHAVING句はグループ化
を指示しているデータに条件付けを指示するキーワードです。この説明では良くわからないと
思いますが、以降の例を参照してもらえれば理解できると思います。
尚,ORDER BY句はデータの並び替えを指示するキーワードです。
簡単なデータの並び替え
SQL> SELECT * FROM EMP WHERE DEPTNO = 30 ORDER BY SAL DESC; |
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO |
7698 BLAKE MANAGER 7839 81-05-01 2850 30 |
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 |
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 |
7521 WARD SALESMAN 7698 81-02-22 1250 500 30 |
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 |
7900 JAMES CLERK 7698 81-12-03 950 30 |
この例ではDEPTNOが30の行を検索し、SALの降順に出力指示をしています。
ORDER BY句では並び替えを行いたい列名の後ろに"ASC","DESC"のキーワードを続けます。
"ASC","DESC"はそれぞれ昇順、降順の意味になります。また、特に"ASC","DESC"を指定しなければ
"ASC"が指定されたものとみなされます。
SQL> SELECT * FROM EMP WHERE DEPTNO = 30 ORDER BY SAL DESC , ENAME ASC; |
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO |
7698 BLAKE MANAGER 7839 81-05-01 2850 30 |
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30 |
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30 |
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30 |
7521 WARD SALESMAN 7698 81-02-22 1250 500 30 |
7900 JAMES CLERK 7698 81-12-03 950 30 |
この例ではENAMEの昇順の指定を付け加えています。ORDER BY句では並び替えの方法が、
記述されている順番に評価されていきます。この場合ではSALでは降順に並べた後、さらに
SALが同じ値の場合ENAMEの昇順に並び替えが行われます。(4,5行目の部分)
簡単なグループ化のSELECT文
SQL> SELECT SUM(SAL) FROM EMP GROUP BY DEPTNO; |
DEPTNOでグループ化を行い、それぞれのSALの合計値を出力しています。
EMP表に設定されているデータにはDEPTNOの種類が10,20,30の3種類あるためこの例の様に
3行の結果が表示されます。このままではどのDEPTNOの合計値なのかわからないので実際には
以下の様なSELECT文が一般的です。
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO; |
グループ化でのよくある間違い
SQL> SELECT * FROM EMP GROUP BY DEPTNO; |
SELECT * FROM EMP GROUP BY DEPTNO |
ORA-00979: GROUP BYの式がありません。 |
私もそうでしたが、初心者のころにはグループ化の意味が良くわからなく、SELECT句に
グループ化を行う項目を指定しないで"SELECT *"などとしていました。
GROUP BY句を指定したら、必ずグループ化を行う関数等をSELECT句に書かなければなりません。
以下のSELECT文も同じ様な間違いです。
SQL> SELECT ENAME FROM EMP GROUP BY DEPTNO; |
SELECT ENAME FROM EMP GROUP BY DEPTNO |
ORA-00979: GROUP BYの式がありません。 |
簡単なグループ化での並び替え
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY SUM(SAL); |
DEPTNOでグループ化を行い、それぞれのSALの合計値で昇順ソートした結果を出力しています。
ORDER BY句にグループ化の式を指示します。
簡単なグループ化での範囲指定
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) > 9000; |
DEPTNOでグループ化を行い、それぞれのSALの合計値が9000より大きいものを出力しています。
グループ化の式の範囲指定にはHAVING句に指定します。よくあるエラーですが、以下の様にWHERE句に
範囲指定を行うことはできません。
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP WHERE HAVING SUM(SAL) > 9000 GROUP BY DEPTNO ; |
SELECT DEPTNO,SUM(SAL) FROM EMP WHERE HAVING SUM(SAL) > 9000 GROUP BY DEPTNO |
その他のグループ関数について
上の例の様にSUM()関数のほか以下の様な関数があります。
| 関数名 |
処理 |
備考 |
| COUNT() |
項目の件数を計数 |
|
| SUM() |
項目の数値の合計値 |
数値項目のみ |
| AVG() |
項目の数値の平均値 |
数値項目のみ |
| MAX() |
項目の最大値 |
|
| MIN() |
項目の最小値 |
|
上記の関数の実行例を以下に示します。
SQL> SELECT DEPTNO,COUNT(SAL),SUM(SAL),AVG(SAL),MAX(SAL),MIN(SAL) |
2 FROM EMP GROUP BY DEPTNO; |
DEPTNO COUNT(SAL) SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL) |
10 3 8750 2916.66667 5000 1300 |
30 6 9400 1566.66667 2850 950 |
■関連記事
⇒
COUNT：データの存在する行数を計数
⇒
SUM：データの合計値を返す
⇒
MAX，MIN：データ内の最大値及び最小値を返す
グループ関数の注意点について
COUNT()について
COUNT()の件数をカウントする項目名の指定で、以下の2個のSQL文は異なる結果を返す場合があります。
SELECT COUNT(*) FROM EMP;
SELECT COUNT(SAL) FROM EMP;
項目名が指定されている場合は、その値がNULLの行があればその値はカウントされません。
COUNT(*)は単純に行のカウントを調べるときに使用するべきです。また、純粋に項目のNULLでないものを
カウントする場合に使用します。
検索の実行時間からいうと、COUNT(*)よりもCOUNT(カラム名)の方が早くなるそうです。
COUNT(カラム名)のカラムはNULLが無い様にテーブルで規定値を指定するか、
データ登録で必ず値を入れた方が良いと思います。
NULLを含めたグループ関数の結果を示す為に、以下の様なテストテーブルを考えます。
SQL> SELECT * FROM TT_TEST; |
COUNT(),SUM(),AVG(),MIN(),MAX()のSQL文を以下に示します。
SQL> SELECT COUNT(*),COUNT(DATA1),SUM(DATA1),AVG(DATA1),MIN(DATA1),MAX(DATA1) FROM TT_TEST; |
COUNT(*) COUNT(DATA1) SUM(DATA1) AVG(DATA1) MIN(DATA1) MAX(DATA1) |
COUNT(DATA1)はCODE=4のNULLをカウントしていないことがわかります。
また、SUM(DATA1),AVG(DATA1),MIN(DATA1),MAX(DATA1)は当然のことながらそれぞれ数値の値がNULLで
ないもののみを計算対象としています。
但し、上記のSELECT文に条件をつけると以下の様になります。
SQL> SELECT COUNT(*),COUNT(DATA1),SUM(DATA1),AVG(DATA1),MIN(DATA1),MAX(DATA1) FROM TT_TEST |
COUNT(*) COUNT(DATA1) SUM(DATA1) AVG(DATA1) MIN(DATA1) MAX(DATA1) |
SUM(DATA1),AVG(DATA1),MIN(DATA1),MAX(DATA1)の結果は全てNULLになります。
検索の結果がNULLでは処理上問題がある場合は、NVL()関数等を利用し結果を０にする。
SQL> SELECT COUNT(*),COUNT(DATA1),NVL(SUM(DATA1),0),NVL(AVG(DATA1),0),NVL(MIN(DATA1),0),NVL(MAX(DATA1),0) |
COUNT(*) COUNT(DATA1) NVL(SUM(DATA1),0) NVL(AVG(DATA1),0) NVL(MIN(DATA1),0) NVL(MAX(DATA1),0) |
NVL()関数について
NVL(式1,式2)
式1の値がNULLの場合は式2を値が戻り値となり、式1の値がNULLでない場合は式1の値
そのものをNVLの戻り値とします。戻り値のデータ型は式1のデータ型と同様となります。
但し、文字列型の場合はVARCHAR2型となります。
■関連記事
⇒
NULLについて再考
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていてSQLのスキルが試せる様になっています。
