[Oracle Database] 5. 그룹화에 대해 알아보자.

다중행 함수는 지정 테이블의 데이터를 가공하여 하나의 결과 값만 출력한다. 하나의 결과가 아닌 특정 데이터를 기준으로 묶어서 결과를 각각 알고 싶을 때는 어떻게 하면 편리할까?

 

GROUP BY 절

기본 형식

SELECT [조회할 열1 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY [그룹화할 열을 지정(여러개가능)]
ORDER BY [정렬 기준]

 

DEPTNO 열 값별로 급여의 평균 값을 구해보자.

group by 사용 x

SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 30;

 

group by 사용

SELECT AVG(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;

 

유의할 점

GROUP BY절을 사용한 그룹화는, 그룹화 된 열 외에 일반 열을 SELECT절에 명시할 수 없다.

 

쿼리문을 작성할 때 각 열별 데이터 수가 달라지지 않도록 유의하자.

 

HAVING절

그룹화된 결과 값의 범위를 제한하는데 사용

기본형식

SELECT [조회할 열1 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY [그룹화할 열을 지정(여러개 가능)]
HAVING [출력 그룹을 제한하는 조건식]
ORDER BY [정렬 기준]

 

 select job, deptno, sum(sal)
  2  from emp
  3  where deptno in (10, 30)
  4  group by job, deptno
  5  having sum(sal) >= 2500
  6  order by job, deptno;

JOB                    DEPTNO   SUM(SAL)
------------------ ---------- ----------
MANAGER                    30       2850
PRESIDENT                  10       5000
SALESMAN                   30       5600

정리) WHERE은 일반행을 제한하고, HAVING은 GROUP을 제한한다

 

 

ROLLUP, CUBE, GROUPING SETS 함수

GROUP BY절에 지정할 수 있는 특수 함수

• ROLLUP, CUBE 함수 : 그룹화 데이터의 합계를 함께 출력하는데 사용
• GROUPING SETS : 각 그룹을 묶어주는 역할

 

ROLLUP 함수의 기본 형식

SELECT [조회할 열 1 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP([그룹화 열 지정(여러 개 가능)]);

 

CUBE 함수의 기본 형식

SELECT [조회할 열 1 이름], ..., [열N 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY CUBE([그룹화 열 지정(여러 개 가능)]);

 

GROUPING SETS 기본 형식

SELECT [조회할 열 1 이름], ..., [열N 이름]
    GROUPING [GROUP BY절에 ROLLUP 또는 CUBE에 명시한 그룹화 할 열 이름]
FROM [조회할 테이블 이름]
WHERE [조회할 행을 선별하는 조건식]
GROUP BY ROLLUP 또는 CUBE([그룹화 열]);

 

ROLLUP 함수를 적용한 그룹화

SQL> select job, deptno, sum(sal)
  2  from emp
  3  group by rollup(job, deptno)
  4  order by job, deptno;

JOB                    DEPTNO   SUM(SAL)   // 각 업무별 급여의 합
------------------ ---------- ----------
ANALYST                    20       6000
ANALYST                             6000   // ANALYSY 전체의 합
CLERK                      10       1300
CLERK                      20       1900
CLERK                      30        950
CLERK                               4150   // CLERK 전체의 합
MANAGER                    10       2450
MANAGER                    20       2975
MANAGER                    30       2850
MANAGER                             8275    // MANAGER 전체의 합
PRESIDENT                  10       5000
PRESIDENT                           5000    // PRESIDENT 전체의 합
SALESMAN                   30       5600
SALESMAN                            5600    // SALESNAN 전체의 합
                                   29025    // 전체사원 급여의 합

15 rows selected.

 

CUBE 함수를 적용한 그룹화

SQL> select job, deptno, sum(sal)
  2  from emp
  3  group by cube(job, deptno)
  4  order by job, deptno;

JOB                    DEPTNO   SUM(SAL)
------------------ ---------- ----------
ANALYST                    20       6000
ANALYST                             6000
CLERK                      10       1300
CLERK                      20       1900
CLERK                      30        950
CLERK                               4150
MANAGER                    10       2450
MANAGER                    20       2975
MANAGER                    30       2850
MANAGER                             8275
PRESIDENT                  10       5000
PRESIDENT                           5000
SALESMAN                   30       5600
SALESMAN                            5600
                           10       8750    //deptno 10의 전체의 합 
                           20      10875    //deptno 20의 전체의 합
                           30       9400    //deptno 30의 전체의 합
                                   29025    // 전체사원 급여의 합

18 rows selected.

• CUBE 함수는 ROLLUP함수를 사용했을 때보다 좀 더 많은 결과가 나오는 것을 볼 수 있다.
  (2^N개 조합 출력)
• CUBE 함수는 지정한 모든 열(DEPTNO, JOB)에서 가능한 조합의 결과를 모두 출력한다.

 

GROUPING SETS 적용

SQL> select job, sum(sal)
  2  from emp
  3  group by job
  4  order by job;

JOB                  SUM(SAL)
------------------ ----------
ANALYST                  6000
CLERK                    4150
MANAGER                  8275
PRESIDENT                5000
SALESMAN                 5600

SQL> select deptno, sum(sal)
  2  from emp
  3  group by deptno
  4  order by deptno;

    DEPTNO   SUM(SAL)
---------- ----------
        10       8750
        20      10875
        30       9400

SQL> select deptno, sum(sal)
  2  from emp
  3  group by grouping sets(job, deptno)
  4  order by job, deptno;

    DEPTNO   SUM(SAL)
---------- ----------
                 6000
                 4150
                 8275
                 5000
                 5600
        10       8750
        20      10875
        30       9400

8 rows selected.