[Oracle] 오라클 GROUPING, GROUPING_ID 함수 사용법 정리

오라클의 GROUPING, GROUPING_ID 함수는 소계와 합계를 집계할 때 사용하는 ROLLUP, CUBE, GROUPING SETS 함수와 함계 사용된다. 소계와 합계로 집계되어 출력된 행을 구분할 때 사용한다.

 

기본 사용법

GROUPING 함수는 1개의 매개변수만 있으며, GROUPING_ID 함수는 여러개의 매개변수를 입력할 수 있다.

 

GROUPING 함수

SELECT job
     , deptno
     , SUM(sal) 
     , GROUPING(job)
     , GROUPING(deptno)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)

 

 

GROUPING 함수는 소계, 합계로 집계된 행의 컬럼 NULL을 구분할 수있다.

NULL인 경우 1을 반환하고 아닌경우 0을 반환한다.

 

SELECT job
     , mgr
     , SUM(sal) 
     , GROUPING(mgr)
  FROM emp
 WHERE job = 'PRESIDENT'
 GROUP BY ROLLUP(job, mgr)

 

 

실데이터의 NULL은 0을 반환한다. 소계 산출로 생성된 행의 NULL만 1을 반환한다.

 

GROUPING_ID 함수

SELECT job
     , deptno
     , SUM(sal) 
     , GROUPING_ID(job, deptno)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)

 

 

GROUPING_ID 함수는 여러 컬럼을 매개변수로 사용할 수 있다.

매개변수의 컬럼 순서에 맞게 해당 컬럼이 NULL인 경우 1을 반한하고 한 행을 2진수라고 생각하면 된다.

 

첫번재 예시) 01 → 0001 (2진수) → 1 (10진수)

두번째 예시) 11 → 0011 (2진수) → 3 (10진수)

 

2진수를 10진수로 변환하면 GROUPING_ID 함수 결과가 된다.

 

SELECT job
     , deptno
     , mgr
     , SUM(sal) 
     , GROUPING_ID(job, deptno, mgr)
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno, mgr)

 

 

첫번재 예시) 001 → 0001 (2진수) → 1 (10진수)

두번째 예시) 011 → 0011 (2진수) → 3 (10진수)

세번째 예시) 111 → 0111 (2진수) → 7 (10진수)

 

활용 예제

GROUPING, GROUPING_ID 함수는 SELECT 절과 HAVING 절에서 사용할 수 있다.

 

SELECT DECODE(GROUPING(job), 1, '합계', job)
     , SUM(sal) 
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job)

 

 

합계 행의 JOB 컬럼에 NULL 대신 합계를 표시한다.

 

SELECT DECODE(GROUPING_ID(job, deptno), 3, '합계', job)    AS job
     , DECODE(GROUPING_ID(job, deptno), 1, '소계', deptno) AS deptno
     , SUM(sal) 
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)

 

 

소계, 합계 행의 JOB, DEPTNO 컬럼에 NULL 대신 소계, 합계를 표시한다.

 

SELECT job
     , deptno
     , SUM(sal) 
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER')
 GROUP BY ROLLUP(job, deptno)
HAVING GROUPING_ID(job, deptno) IN (0, 3)

 

 

소계 행을 제거하고, 데이터 행(0)과 합계 행(3)만 표시한다.

 

예제쿼리(테이블포함).txt

0.00MB

 

[Oracle] 오라클 ROLLUP 함수 사용법 (합계, 소계) [Oracle] 오라클 CUBE 함수 사용법 (합계, 소계) [Oracle] 오라클 GROUPING SETS 사용법 (합계, 소계)