[Oracle] 분석함수, 윈도함수 - ROLLUP (기준별 소계 요약)

서론

- 윈도함수 혹은 분석 함수

  : 행(row)끼리 연산이나 비교를 쉽게 지원해주기 위한 함수

---

ROLLUP 함수 : 각 기준별 소계 요약

• 데이터들에 특정 기준을 주고 모아 합계(집계)를 보는 경우
  (ex) 본사에서 월별 매장별 매출 합계를 보는 경우
• GROUP BY의 확장 형태
• 복잡한 SQL을 짧게 만들고, 해당 데이터들을 반복적으로 읽는 것을 최소화해 속도를 빠르게 해줌
• 계층적 분류를 포함하고 있는 데이터 집계에 적합
• ROLLUP( )에 지정된 컬럼들은 소계(소그룹)의 기준이 되는 컬럼임
  (ex) 지정 컬럼 수가 N이라고 했을 때, N+1 Level의 소계(소그룹) 생성
  • 지정된 컬럼의 순서가 바뀌면 결과도 바뀌어 컬럼 순서에 주의해야함

 

---

 

• • 세 가지 요구사항으로 분리 가능
    
    (1) 부서와 직업별 평균 급여와 사원 수
    (2) 부서별 평균 급여와 사원 수
    (3) 전체 사원의 평균 급여와 사원 수
    
    
  • 위 세 가지를 각각 SQL로 만들어 UNION ALL로 연결 (책 168페이지 코드 참고)
    
    SELECT deptno, job, avg(nvl(sal, 1)) as "avg_sal", count(*) as "cnt_emp"
    FROM emp GROUP BY deptno, job
    UNION ALL
    SELECT deptno, NULL job, avg(nvl(sal, 1)) as "avg_sal", count(*) as "cnt_emp"
    FROM emp GROUP BY deptno
    UNION ALL
    SELECT NULL deptno, NULL job, avg(nvl(sal, 1)) as "avg_sal", count(*) as "cnt_emp"
    FROM emp 
    ORDER BY deptno, job;
    
    
  • 출력 결과는 직급별 그룹, 부서별 그룹, 전체 그룹 세 가지가 나타남
    하지만 쿼리문이 굉장히 길고 복잡함
    
    
  •  ROLLUP 함수를 사용해 리팩토링 가능
    - ROLLUP은 지정된 컬럼 수가 N개일 때, N+1 Level의 소계(소그룹) 생성
    - 이 말은 "컬럼의 수개 2개(부서, 직업) 일 때, 2+1=3개의 그룹 생성"이 된다는 것
    - 무슨 말이냐
    
    GROUP BY  ROLLUP ( deptno, job ); 일 때, [ N + 1 ] 그룹 생성
    => DEPTNO 그룹 집계 / DEPTNO, JOB 그룹 집계 / 전체 그룹 집계
    
    
  • ROLLUP을 활용해 위 예제의 쿼리 문을 아래와 같이 리팩토링 가능
    
    SELECT deptno, job, ROUND(AVG(sal), 1) avg_sal, COUNT(*) cnt_emp
    FROM emp
    GROUP BY ROLLUP( deptno, job );
    
    

    

    
    ▶ deptno가 앞에 있을 때, 대분류 기준은 deptno로 deptno를 기준으로한 소계의 모든 경우의 수 점검
    
    SELECT deptno, job, ROUND(AVG(sal), 1) avg_sal, COUNT(*) cnt_emp
    FROM emp
    GROUP BY ROLLUP( job, deptno );
    

    

    
    ▶ job이 앞에 있을 때, job을 기준으로 모든 소계의 경우의 수 집계
    
    
  • ROLLUP( )에 입력되는 컬럼 순서가 매우 중요예시
    부서와 직업별 평균 급여 및 사원 수와 부서별 평균 급여와 사원 수, 전체 사원의 평균 급여와 사원 수 구할 것

---

기존 테이블 복사

CREATE TABLE 테이블명
AS SELECT col1, col2, col3, ... , col4 FROM 테이블명

---

쿼리 실행계획 확인

> explain paln for [ 쿼리 ]

> select * from table(dbms_xplan.display);

---

ROLLUP( ) 함수는 전체 총 합계를 출력하지 않기에 전체 총 합계를 출력하고 싶다면 ROLLUP 대신 CUBE 함수 사용