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 は検索結果に該当しなくなる。

HAVING 句 と WHERE 句との違い

HAVING と WHERE 句との違いとは、グループ化した 「結果の集計後」の結果への条件か、レコードをグループ化処理する前の「表全体のレコード」への条件かの違いである。

つまり [表からの絞り込みは WHERE] ⇒ [結果の集計は GROUP BY] ⇒ [集計結果レコードからのさらなる選別は HAVING ] という処理となる。

例えば、ある学校全体の成績データベースから 「４年の生徒」で 「どの教科でもいいので成績が 3以上ある」の生徒のデータを検索したい場合に…

• WHERE ： 学年が 4
• GROUP BY ： 出席番号 （または 学籍番号）
• HAVING : MAX(成績) >= 3

SQL のイメージ

SELECT 出席番号 FROM 成績表
WHERE 学年 = 4
GROUP BY 出席番号
HAVING MAX(成績) >= 3

この例の場合は WHERE の条件を HAVING 側に書いても求めている結果を検索することができるかもしれない。（出席番号がどのレベルで一意なのかで違う結果になる

しかし、事前に明らかに不要とわかっているデータを集計対象から除外しておくことは処理の高速化になる。
さらに、科目を「数学と英語」に限定したい場合は？…、科目を限定した後の成績の平均が 4 以上という条件を追加したい場合は？…と考えてみるとそれぞれの役割の違いがわかりやすくなるだろう。

ちなみに GROUP BY がなくても HAVING は使用できる。(Oracle 9i R2 〜）