Oracle SQL SELECT2 : SELECT文の基礎2(グループ化について)


おすすめ書籍



SELECT文の基本的な構文

SELECT <列名リスト> | <式リスト> FROM <表名リスト>
  [WHERE <検索条件>]
  [GROUP BY <列名リスト>]
  [HAVING <グループ条件>]
  [ORDER BY <式> [ASC | DESC] ...]

WHERE句についてはSELECT文その1で説明しましたが、今回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()関数等を利用し結果を0にする。
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型となります。
■関連記事
NULLについて再考





ページのトップへ戻る