SQL OLAP 함수로 데이터 분석하기

Db2 for i 는 버전 7.3 부터 대거 OLAP 함수를 제공하기 시작했습니다.

이번 글에서는 그 중 몇가지를 활용하는 예제를 보여드리겠습니다.

 

1. 순위 함수 (RANK, ROW_NUMBER, DENSE_RANK)

2. 집계 함수 (SUM, COUNT, AVG, MIN, MAX, MEDIAN 등)

3. 그룹 내 행 순서 (LAG, LEAD, FIRST_VALUE, NTH_VALUE, LAST_VALUE 등)

4. 그룹 내 비율 (NTILE, RATIO_TO_REPORT, CUME_DIST 등)

 

데이터는 Db2에서 제공하는 샘플 데이터베이스를 사용했습니다. 샘플 DB를 만드는 방법과 각 테이블의 설명은 이 링크를 참고하세요. 

 

1.  순위 함수

대표적인 순위 함수 3가지, RANK, DENSE_RANK, ROW_NUMBER 를 비교하기 위해 EMPLOYEE 테이블에서 각 직원의 연봉으로 정렬하여 순위를 조회하였습니다. 

 SELECT EMPNO, SALARY, 
        RANK() OVER(ORDER BY SALARY DESC),
        DENSE_RANK() OVER(ORDER BY SALARY DESC),
        ROW_NUMBER() OVER(ORDER BY SALARY DESC)  
 FROM EMPLOYEE
 FETCH FIRST 10 ROWS ONLY

조회 결과

EMPNO	SALARY	RANK	DENSE_RANK	ROW_NUMBER
000010	52,750.00	1	1	1
000110	46,500.00	2	2	2
200010	46,500.00	2	2	3
000020	41,250.00	4	3	4
000050	40,175.00	5	4	5
000030	38,250.00	6	5	6
000070	36,170.00	7	6	7
000060	32,250.00	8	7	8
000220	29,840.00	9	8	9
200220	29,840.00	9	8	10

RANK 와 DENSE_RANK는 둘다 동일한 값인 경우 순위를 동순위로 표기하지만, DENSE_RANK는 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점이다. 

ROW_NUMBER 는 동일한 값이라도 고유한 순위를 부여한다.

 

이번에는 그룹 내의 순위를 조회하는 방법입니다. 순위 함수의 OVER 절에 PARTITION BY (컬럼명) 를 추가하면, 그 컬럼을 기준으로 그룹을 묶어 그 그룹 내에서의 순위를 보여주게 됩니다. 

SELECT LASTNAME, WORKDEPT, BONUS, 
        DENSE_RANK() OVER(PARTITION BY WORKDEPT ORDER BY BONUS DESC) 
          AS BONUS_RANK_IN_DEPT 
   FROM EMPLOYEE
   WHERE WORKDEPT LIKE 'E%'

각 부서 내에서의 보너스 순위 조회 결과

LASTNAME	WORKDEPT	BONUS	BONUS_RANK_IN_DEPT
GEYER	E01	800.00	1
HENDERSON	E11	600.00	1
SCHNEIDER	E11	500.00	2
SCHWARTZ	E11	500.00	2
SMITH	E11	400.00	3
PARKER	E11	300.00	4
SETRIGHT	E11	300.00	4
SPRINGER	E11	300.00	4
SPENSER	E21	500.00	1
LEE	E21	500.00	1
GOUNOT	E21	500.00	1
WONG	E21	500.00	1
ALONZO	E21	500.00	1
MENTA	E21	400.00	2

 

2. 집계함수 + 순위함수 + 그룹내 비율 함수

부서별 평균 연봉을 계산하고(집계함수 AVG), 그에 따른 부서별 순위(순위함수 RANK)와 순위 그룹을 3등분(그룹내 비율 함수 NTILE)하여 나누어보겠습니다. 

SELECT WORKDEPT AS "부서코드", INT(AVG(SALARY)) AS "평균 연봉", 
         RANK() OVER(ORDER BY AVG(SALARY) DESC) AS "순위", 
         NTILE(3) OVER(ORDER BY AVG(SALARY) DESC) AS "등급" 
   FROM EMPLOYEE 
   GROUP BY  WORKDEPT

부서별 평균 연봉 및 순위와 등급 조회 결과

부서코드	평균 연봉	순위	등급
B01	41,250	1	1
A00	40,850	2	1
E01	40,175	3	1
C01	29,722	4	2
D21	25,668	5	2
D11	25,147	6	2
E21	24,086	7	3
E11	21,020	8	3

 

3. 집계 함수 + 그룹 내 비율 함수

특정 부서에 있는 직원들의 연봉을 누적 합계를 내고, 누적 분포값을 계산하는 방법입니다. 

SELECT ROW_NUMBER() OVER() AS ROW, LASTNAME, SALARY,
     SUM(SALARY) OVER(ORDER BY SALARY 
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "RANGE 누적합계",
     SUM(SALARY) OVER(ORDER BY SALARY 
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "ROWS 누적합계",
     DECIMAL(CUME_DIST() OVER (ORDER BY SALARY),4,3) AS "누적백분율"
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

(참조) 키워드 설명

키워드	설명
ROWS	물리적인 행 단위
RANGE	논리적인 행 집합
CURRENT ROW	현재 행
UNBOUNDED PRECEDING	첫 번째 행
UNBOUNDED FOLLOWING	마지막 행
[위치] PRECEDING	[위치] 이전 행
[위치] FOLLOWING	[위치] 다음 행

 

조회 결과

RANGE 를 기준으로 합계를 낼 때는 동일한 값을 그룹으로 묶어서 하나로 인식하게 되고, ROWS 를 기준으로 합계를 낼 때는 물리적인 행 단위로 누적 합계를 계산합니다. 

ROW	LASTNAME	SALARY	RANGE 누적합계	ROWS 누적합계	누적백분율
1	JONES	18,270.00	18,270.00	18,270.00	.091
2	WALKER	20,450.00	38,720.00	38,720.00	.182
3	SCOUTTEN	21,340.00	60,060.00	60,060.00	.273
4	PIANKA	22,250.00	82,310.00	82,310.00	.364
5	YOSHIMURA	24,680.00	131,670.00	106,990.00	.545
6	YAMAMOTO	24,680.00	131,670.00	131,670.00	.545
7	ADAMSON	25,280.00	156,950.00	156,950.00	.636
8	BROWN	27,740.00	184,690.00	184,690.00	.727
9	LUTZ	29,840.00	244,370.00	214,530.00	.909
10	JOHN	29,840.00	244,370.00	244,370.00	.909
11	STERN	32,250.00	276,620.00	276,620.00	1.000

 

4. 집계함수 + 그룹내 행 순서 함수

특정 부서에 있는 직원들의 연봉을 누적 합계를 내고, 본인의 연봉보다 1000 이내로 적거나, 큰 사람들의 연봉을 합계 내고(구간합계), 구간별 최소값과 최대값을 정리하는 방법입니다.

SELECT LASTNAME, SALARY,
       SUM(SALARY) OVER(ORDER BY SALARY) AS "누적합계", 
       SUM(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS "구간합계",
       FIRST_VALUE(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS "구간내 최소값",
       LAST_VALUE(SALARY) OVER(ORDER BY SALARY 
           RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING) AS "구간내 최대값"
  FROM EMPLOYEE
  WHERE WORKDEPT = 'D11'
  ORDER BY SALARY

조회 결과

LASTNAME	SALARY	누적합계	구간합계	구간내 최소값	구간내 최대값
JONES	18,270.00	18,270.00	18,270.00	18,270.00	18,270.00
WALKER	20,450.00	38,720.00	41,790.00	20,450.00	21,340.00
SCOUTTEN	21,340.00	60,060.00	64,040.00	20,450.00	22,250.00
PIANKA	22,250.00	82,310.00	43,590.00	21,340.00	25,280.00
YOSHIMURA	24,680.00	131,670.00	74,640.00	24,680.00	25,280.00
YAMAMOTO	24,680.00	131,670.00	74,640.00	24,680.00	25,280.00
ADAMSON	25,280.00	156,950.00	74,640.00	24,680.00	25,280.00
BROWN	27,740.00	184,690.00	27,740.00	27,740.00	27,740.00
LUTZ	29,840.00	244,370.00	59,680.00	29,840.00	29,840.00
JOHN	29,840.00	244,370.00	59,680.00	29,840.00	29,840.00
STERN	32,250.00	276,620.00	32,250.00	32,250.00	32,250.00

 

이상 OLAP 함수를 정리해보았습니다.