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	-	-

注目する点は オレンジ色 の部分である。 オレンジ色の部分はグループ化によって情報が集約される。 この部分を取り出すには、集計関数を使用しなければならない。 集約された部分の値がすべて同じ場合、集計関数の MIN や MAX で取り出すという小技も使われることがある。

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

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 を無視する。
　 　( 33 + 8 + "" ) / 3 => 13.66.. ではなく ( 33 + 8 ) / 2 => 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 以上という条件を追加したい場合は？…と考えてみるとそれぞれの役割の違いがわかりやすくなるだろう。

HAVING は GROUP BY がない場合でも記述できる。(Oracle 9i R2 〜）

それでも 私を悩ます HAVING の存在がやっぱり許せないというのであれば

SELECT "生徒番号" FROM ( SELECT "生徒番号", MAX("成績") "最高成績" FROM "成績表" WHERE "学年" = 4 GROUP BY "生徒番号" ) WHERE "最高成績" > 3

という書き換えもできる。WHERE 句に直接　「 MAX("成績") 」 とは書かずに別名をつける。
ひと昔前の DBMS ではインラインビューの記述ができない。

---