Oracle SQL SELECT２ : SELECT文の基礎２(グループ化について)
- SELECT文でのORDER BY句を使ったソート
- SELECT文でのGROUP BY句を使ったグループ化
- グループ化の式の範囲指定にはHAVING句
- COUNT(),SUM(),MAX(),MIN(),AVG()
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]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
6行が選択されました。
この例では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
6行が選択されました。
この例ではENAMEの昇順の指定を付け加えています。ORDER BY句では並び替えの方法が、
記述されている順番に評価されていきます。この場合ではSALでは降順に並べた後、さらに
SALが同じ値の場合ENAMEの昇順に並び替えが行われます。(4,5行目の部分)
簡単なグループ化のSELECT文
SQL> SELECT SUM(SAL) FROM EMP GROUP BY DEPTNO;
SUM(SAL)
----------
8750
10875
9400
DEPTNOでグループ化を行い、それぞれのSALの合計値を出力しています。
EMP表に設定されているデータにはDEPTNOの種類が10,20,30の3種類あるためこの例の様に
3行の結果が表示されます。このままではどのDEPTNOの合計値なのかわからないので実際には
以下の様なSELECT文が一般的です。
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO;
DEPTNO SUM(SAL)
---------- ----------
10 8750
20 10875
30 9400
グループ化でのよくある間違い
SQL> SELECT * FROM EMP GROUP BY DEPTNO;
SELECT * FROM EMP GROUP BY DEPTNO
*
エラー行: 1: エラーが発生しました。
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
*
エラー行: 1: エラーが発生しました。
ORA-00979: GROUP BYの式がありません。
簡単なグループ化での並び替え
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY SUM(SAL);
DEPTNO SUM(SAL)
---------- ----------
10 8750
30 9400
20 10875
DEPTNOでグループ化を行い、それぞれのSALの合計値で昇順ソートした結果を出力しています。
ORDER BY句にグループ化の式を指示します。
簡単なグループ化での範囲指定
SQL> SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL) > 9000;
DEPTNO SUM(SAL)
---------- ----------
20 10875
30 9400
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
*
1行でエラーが発生しました。
ORA-00936: 式がありません。
その他のグループ関数について
上の例の様に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
20 5 10875 2175 3000 800
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> DESC TT_TEST
名前 NULL? 型
----------------------------------------------------- -------- ------------------------------------
CODE NUMBER(5)
DATA1 NUMBER(8)
SQL> SELECT * FROM TT_TEST;
CODE DATA1
--------- ---------
1 100
2 200
3 300
4
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)
--------- ------------ ---------- ---------- ---------- ----------
4 3 600 200 100 300
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
2 WHERE CODE = 4;
COUNT(*) COUNT(DATA1) SUM(DATA1) AVG(DATA1) MIN(DATA1) MAX(DATA1)
---------- ------------ ---------- ---------- ---------- ----------
1 0
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)
2 FROM TT_TEST
3 WHERE CODE = 4;
COUNT(*) COUNT(DATA1) NVL(SUM(DATA1),0) NVL(AVG(DATA1),0) NVL(MIN(DATA1),0) NVL(MAX(DATA1),0)
--------- ------------ ----------------- ----------------- ----------------- -----------------
1 0 0 0 0 0
NVL()関数について
NVL(式1,式2) 式1の値がNULLの場合は式2を値が戻り値となり、式1の値がNULLでない場合は式1の値 そのものをNVLの戻り値とします。戻り値のデータ型は式1のデータ型と同様となります。 但し、文字列型の場合はVARCHAR2型となります。
