오라클 SQL과 PL/SQL ( ROLLUP 함수 / 분석함수 - 1 )

분석 함수

기존 관계형 데이터베이스는 컬럼끼리의 연산, 비교, 집계 등을 앞에서 살펴본 그룹 함수들을 사용해서 쉽게 할 수 있었지만,
행끼리비교 연산하는 것을 하나의 SQL로 처리하는게 힘들었습니다.
만약 그런 작업들을 해야 할 경우가 생기면 PL/SQL과 같은 절차형 프로그램을 작성하거나 아주 복잡한 SQL문을 작성해야 했습니다.
그러나 오라클 버전이 올라가면서 행끼리의 연산이나 비교를 쉽게 지원해주기 위한 함수를 제공하게 되었는데 이 함수를 분석함수(ANALYTIC FUNCTION) 또는 윈도함수(WINDOW FUNCTION) 라고 합니다.
분석 함수를 잘 활용한다면 복잡한 로직을 비교적 간단한 SQL문장으로 해결할 수 있습니다.

1. 각 기준별 소계를 요약해서 보여 주는 ROLLUP() 함수
많은 데이터들이 있을 때 그 데이터들에 특정 기준을 주고 모아서 합계를 보는 경가 아주 많습니다.
예를들어 고객들의 구매 내역을 정리해서 고객별 구매 내역 합계를 본다든지 본사에서 월별 매장별 매출 합계를 보는경우가 많겠죠 ?
이럴 경우 편리하게 사용할 수 있는 함수가 여러가지 있는데 오늘은 ROLLUP()함수를 살펴보겠습니다.
ROLLUP() 함수는 GROUP BY 의 확장형태로 사용하기 쉬우며 복잡한 SQL을 짧게 만들고 해당 데이터들을 반복적으로 읽는 것을 최소하해서 속도를 빠르게 해주는 등 다양한 장점을 가지고 있습니다.
ROLLUP()에 지정된 컬럼들은 소계의 기준이 되는 컬럼들 입니다. 만약 ROLLUP()에 지정된 컬럼들의 수를 N이라고 했을 때 N + 1 LEVEL의 소계가 생성이 됩니다.
즉 월별 매장별로 매출의 합계를 보고 싶다고 할 경우 소게를 내야 할 기준이 2개이므로 2+1개의 소그룹이 만들어 집니다.
그리고 ROLLUP()은 지정된 컬럼의 순서가 바뀌면 결과도 바뀜으로 컬럼 순서의 주의해야 합니다.

예제 1. 부서와 직엽별 평균 급여 및 사원 수와 부서별 평균 급여와 사원 수 , 전체 사원의 평균 급여와 사원수를 구하세요.

순서를 나누자면 
1. 부서와 직업별 평균 급여 및 사원수
2. 부서별 평균 급여와 사원 수
3. 전체 사원의 평균 급여와 사원 수

SELECT DEPTNO, NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY DEPTNO
UNION ALL 
SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY DEPTNO, JOB
UNION ALL 
SELECT NULL DEPTNO , NULL JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
ORDER BY DEPTNO, JOB

쿼리가 굉장히 길고 복잡합니다. 이번엔 ROLLUP() 그룹 함수를 사용해서 SQL문을 만들어 보겠습니다.

SELECT DEPTNO, JOB, ROUND(AVG(SAL), 1) AVG_SAL, COUNT(*) CNT_EMP
FROM EMP
GROUP BY ROLLUP (DEPTNO, JOB)

ROLLUP() 함수를 사용하니까 쿼리가 훨씬 더 간결해졌습니다. 

 

위의 예는 두 개의 컬럼 모두 ROLLUP한 결과이지만 하나씩 해도 상관없습니다.

SELECT DEPTNO, POSITION, COUNT(*), SUM(PAY)
FROM PROFESSOR
GROUP BY POSITION, ROLLUP(DEPTNO)

POSITION으로 먼저 분류를 한 후 같은 DEPTNO가 있을 경우 ROLLUP함수를 사용해서 요약한 후 DEPTNO별로 출력한 것입니다.

 

SELECT DEPTNO, POSITION, COUNT(*), SUM(PAY)
FROM PROFESSOR
GROUP BY DEPTNO , ROLLUP(POSITION);

위 그림은 DEPTNO별로 먼저 그룹핑한 후 POSITION별로 ROLLUP한 결과입니다.

 

***더 자세히 살펴보기 위해 연습용 테이블을 복사한 후 테스트를 해보겠습니다.***

CREATE TABLE PROFESSOR2 AS SELECT DEPTNO, POSITION , PAY
FROM PROFESSOR;

INSERT INTO PROFESSOR2 VALUES(101, 'instructor', 100);
INSERT INTO PROFESSOR2 VALUES(101, 'a full professor', 100);
INSERT INTO PROFESSOR2 VALUES(101, 'assistant professor', 100);
COMMIT

실습을 위해 101번 학과에 중복된 직급 데이터도 추가로 입력하였습니다. (파란색 선택 된 데이터)

 

SELECT DEPTNO, POSITION , SUM(PAY)
FROM PROFESSOR2
GROUP BY DEPTNO , ROLLUP(POSITION);

위 화면과 같이 ROLLUP 함수는 GROUP BY절에서 주어진 조건으로 소계값을 구해줍니다.
쿼리도 간결해지고 속도도 훨씬 빨라집니다.