[SQL] 오라클 내장 함수 - 그룹화 관련 함수들

1. ROLLUP 함수, CUBE 함수

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

- 그룹화 데이터의 합계를 출력할 때 유용하게 사용 가능

- 칼럼을 대그룹, 소그룹과 같이 계층적으로 그룹화 ex) 특정 부서 내 직책별 인원수

- ROLLUP : 지정한 칼럼의 순서대로 조합한 결과를 모두 출력

- CUBE : 지정한 모든 칼럼에서 가능한 조합의 결과를 모두 출력. ROLLUP 함수보다 더 많은 결과를 출력

 

ROLLUP(A, B, C)	CUBE(A, B, C)
1. A 그룹별 B 그룹별 C 그룹에 해당하는 결과 출력    2. A 그룹별 B 그룹에 해당하는 결과 출력    3. A 그룹에 해당하는 결과 출력    4. 전체 데이터 결과 출력	1. A 그룹별 B 그룹별 C 그룹에 해당하는 결과 출력    2. A 그룹별 B 그룹에 해당하는 결과 출력    3. A 그룹별 C 그룹에 해당하는 결과 출력    4. B 그룹별 C 그룹에 해당하는 결과 출력    5. A 그룹에 해당하는 결과 출력    6. B 그룹에 해당하는 결과 출력    7. C 그룹에 해당하는 결과 출력    8. 전체 데이터 결과 출력

 

- 부분 분할(Partial) ROLLUP, 부분 분할 CUBE : 지정 칼럼의 개수가 많아지면 출력이 너무 많아지기 때문에 필요한 조합의 출력만 보기 위해 ROLLUP 함수와 CUBE 함수에 그룹화 중 칼럼 일부만을 지정하는 방법

-- ROLLUP
  SELECT [조회할 칼럼 1 이름], [칼럼 2 이름], … , [칼럼 N 이름]
    FROM [조회할 테이블 이름] 
   WHERE [조회할 칼럼을 선별하기 위한 조건식]
GROUP BY ROLLUP(그룹화 할 칼럼을 지정(여러개 가능));

-- CUBE
  SELECT [조회할 칼럼 1 이름], [칼럼 2 이름], … , [칼럼 N 이름]
    FROM [조회할 테이블 이름] 
   WHERE [조회할 칼럼을 선별하기 위한 조건식]
GROUP BY CUBE(그룹화 할 칼럼을 지정(여러개 가능));

-- 1. 
  SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
    FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

-- 2. 
  SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
    FROM EMP
GROUP BY CUBE(DEPTNO, JOB);

-- 3. 부분 ROLLUP 1
  SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
GROUP BY DEPTNO, ROLLUP(JOB);

-- 4. 부분 ROLLUP 2
  SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
GROUP BY JOB, ROLLUP(DEPTNO);

  (1) DEPTNO 칼럼별로 JOB 칼럼의 그룹화 결과를 출력, DEPTNO 칼럼별로 그룹화 결과를 출력

  (2) DEPTNO 칼럼별로 JOB 칼럼의 그룹화 결과를 출력, DEPTNO 칼럼별로 그룹화 결과를 출력, JOB 칼럼별로 그룹화 결과를 출력

  -> 동일한 SELECT 문에 각각 ROLLUP 함수와 CUBE 함수를 적용한 결과 (1)번에 비해 (2)번이 더 많은 결과를 출력한다 

  (3) DEPTNO 로 먼저 그룹화 한 후 각 그룹별로 JOB 으로 그룹화 한 결과를 출력

  (4) JOB 으로 먼저 그룹화 한 후 각 그룹별로 DEPTNO 으로 그룹화 한 결과를 출력

 

2. GROUPING SETS 함수

- 같은 수준의 그룹화 칼럼이 여러 개일 때 각 칼럼별 그룹화를 통해 결과 값을 출력하는 데 사용

- 지정한 모든 칼럼을 대그룹으로 처리하여 출력

  SELECT [조회할 칼럼 1 이름], [칼럼 2 이름], … , [칼럼 N 이름]
    FROM [조회할 테이블 이름] 
   WHERE [조회할 칼럼을 선별하기 위한 조건식]
GROUP BY GROUPING SETS(그룹화 할 칼럼을 지정(여러개 가능));

-- 1.
  SELECT DEPTNO, JOB, COUNT(*)
    FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);

  (1) 각각 DEPTNO, JOB 으로 그룹화 한 결과를 출력

 

3. GROUPING 함수

- ROLLUP 또는 CUBE 함수를 사용한 GROUP BY 절에 그룹화 대상으로 지정한 칼럼이 그룹화된 상태로 결과가 집계되었는지 확인하는데 사용

- GROUP BY 절에 명시된 칼럼 중 하나를 지정 가능

- 결과로 출력된 0은 GROUPING 함수에 지정한 칼럼이 그룹화되었음을 의미, 1은 그룹화되지 않았다는 것을 의미

  SELECT [조회할 칼럼 1 이름], [칼럼 2 이름], … , [칼럼 N 이름],
  		 GROUPING(그룹화 여부를 확인할 칼럼)
    FROM [조회할 테이블 이름] 
   WHERE [조회할 칼럼을 선별하기 위한 조건식]
GROUP BY ROLLUP or CUBE(그룹화 할 칼럼);

 

4. GROUPING_ID 함수

- ROLLUP 또는 CUBE 함수를 사용한 GROUP BY 절에 그룹화 대상으로 지정한 칼럼이 그룹화된 상태로 결과가 집계되었는지 확인하는데 사용

- GROUPING 함수와 달리 한번에 여러 칼럼 지정이 가능

- 결과는 0과 1로 구성된 그룹화 비트 벡터 값을 2진수로 보고 10진수로 바꾼 값이 최종 결과로 출력

  ex) GROUPING_ID(a, b) 일 때의 결과

 

그룹화 된 열	그룹화 비트 벡터	최종 결과
a, b	0 0	0
a	0 1	1
b	1 0	2
없음	1 1	3

 

  SELECT [조회할 칼럼 1 이름], [칼럼 2 이름], … , [칼럼 N 이름],
  		 GROUPING_ID(그룹화 여부를 확인할 칼럼(여러개 가능))
    FROM [조회할 테이블 이름] 
   WHERE [조회할 칼럼을 선별하기 위한 조건식]
GROUP BY ROLLUP or CUBE(그룹화 할 칼럼);