Oracle® 非公式 SQL, PL/SQL & DBA's ガイドサイト

検索結果のグループ化 (GROUP BY と HAVING)

サンプルデータ

グループ化の適用範囲

グルーピング処理は以下のような例題についての解答になる。

• 部門別に利用者は何人いるか?
• 部門別に平均在職期間はどれくらいか?
  
  
• 在職期間の平均が XX になる部門はどこか? など

グループ化とは

グループ化とは、SELECT 文 で 抽出したデータ(※１）を指定したキーワードで分類(※２) する。
その結果セットを 指定したキーワード、または、集計ファンクション を使用して取り出すこと意味する。

(※１) 抽出したデータ＝ WHERE 句で対象のデータを条件付けること。
(※２) 指定したキーワードで分類＝ GROUP BY 句 抽出結果のグルーピング。

部門でグループ化

• サンプルデータを グループ化してみる。

SELECT DEPT_NO FROM USER_MASTER2 GROUP BY DEPT_NO

グループ化のイメージ(作業前)

利用者マスタ(USER_MASTER2)
利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)
0020	1001	小泉 純一	29
0019	1001	林 喜朗	12
0018	1002	少渕 恵三	21
0017	1003	橋本 虎太郎	33
0001	1003	鳩山 一代	8
0000	1003	影のボス	<NULL>

グループ化 STEP(1)

利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)
0020	1001	小泉 純一	29
0019		林 喜朗	12
0018	1002	少渕 恵三	21
0017	1003	橋本 虎太郎	33
0001		鳩山 一代	8
0000		影のボス	<NULL>

グループ化 STEP(2)

利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)
-	1001	-	-
-	1002	-	-
-	1003	-	-

注目する点はオレンジ色の部分である。オレンジ色の部分はグループ化によって情報が集約される。 この部分を取り出すには、集計関数を使用しなければならない。

集計関数を使用しなければ以下のエラーが発生する。

SQL> SELECT DEPT_NO,USER_NAME FROM USER_MASTER2 GROUP BY DEPT_NO ;
SELECT DEPT_NO,USER_NAME FROM USER_MASTER2 GROUP BY DEPT_NO
               *
行1でエラーが発生しました。:
ORA-00979: GROUP BYの式ではありません。

グループ化の例題の解答

• 部門別に利用者は何人いるか?

SELECT DEPT_NO, COUNT(*) FROM USER_MASTER2 GROUP BY DEPT_NO ;

グループ化 STEP(1)

利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)
0020	1001	小泉 純一	29
0019		林 喜朗	12
0018	1002	少渕 恵三	21
0017	1003	橋本 虎太郎	33
0001		鳩山 一代	8
0000		影のボス	<NULL>

グループ化 STEP(2)

利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)	COUNT(*)
-	1001	-	-	2
-	1002	-	-	1
-	1003	-	-	3

• 部門別に平均在職期間はどれくらいか?

SELECT DEPT_NO, AVG(TERM) FROM USER_MASTER2 GROUP BY DEPT_NO ;

グループ化 STEP(1)

利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)
0020	1001	小泉 純一	29
0019		林 喜朗	12
0018	1002	少渕 恵三	21
0017	1003	橋本 虎太郎	33
0001		鳩山 一代	8
0000		影のボス	(※) <NULL>

グループ化 STEP(2)

利用者ＩＤ (USER_ID)	部門 (DEPT_NO)	利用者名 (USER_NAME)	在職期間 (TERM)	AVG(TERM)
-	1001	-	(29,12)	20.5
-	1002	-	(21)	21
-	1003	-	(33,8,NULL)	(※) 20.5

(※) 集計関数のほとんどが NULL を無視する。

グループ化した後のさらなる条件付け HAVING

在職期間の平均が XX になる部門はどこか? という例題を解答するには、グループ化した結果にさらになる条件付けが必要である。
これには HAVING という句を使用する。HAVING はグループ化した結果セットへの WHERE 条件である。

• グループ化のみ

SELECT DEPT_NO, AVG(TERM) FROM USER_MASTER2 GROUP BY DEPT_NO ;

• グループ化と HAVING
  HAVING による 条件付け 在職期間の平均が 20.5 の部門を取得する。

SELECT DEPT_NO FROM USER_MASTER2 GROUP BY DEPT_NO
           HAVING AVG(TERM) = 20.5

• WHERE、GROUP BY と HAVING の使用
  これまでの SQL に WHERE 条件を加える。在職期間が 10 以下のものを対象外とする。

SELECT DEPT_NO FROM USER_MASTER2 WHERE TERM<=10 GROUP BY DEPT_NO
           HAVING AVG(TERM) = 20.5

部門 1003 は検索結果に該当しなくなる。