오라클 SQL과 PL/SQL ( PIVOT / 달력만들기 )

PIVOT()함수 / 11g에서 추가

PIVOT 함수는 ROW 단위를 COLUMN 단위로 변경해 주고 UNPIVOT 함수는 반대로 COLUMN 단위를 ROW단위로 변경해 주는 기능을 합니다.
CAL테이블로 설명하겠습니다.

이 CAL테이블로 달력을 만들어 보겠습니다.

1. PIVOT를 사용하지 않고 DECODE 함수를 활용하여 달력만들기

SELECT 	MAX(DECODE(DAY, 'SUN', DAYNO)) SUN,
		MAX(DECODE(DAY, 'MON', DAYNO)) MON,
		MAX(DECODE(DAY, 'TUE', DAYNO)) TUE,
		MAX(DECODE(DAY, 'WED', DAYNO)) WED,
		MAX(DECODE(DAY, 'THU', DAYNO)) THU,
		MAX(DECODE(DAY, 'FRI', DAYNO)) FRI,
		MAX(DECODE(DAY, 'SAT', DAYNO)) SAT
FROM CAL
GROUP BY WEEKNO
ORDER BY WEEKNO

 

하나하나 살펴보겠습니다.

첫번쨰 단계로 DECODE를 보면

SELECT 	DECODE(DAY, 'SUN', DAYNO) SUN,
		DECODE(DAY, 'MON', DAYNO) MON,
		DECODE(DAY, 'TUE', DAYNO) TUE,
		DECODE(DAY, 'WED', DAYNO) WED,
		DECODE(DAY, 'THU', DAYNO) THU,
		DECODE(DAY, 'FRI', DAYNO) FRI,
		DECODE(DAY, 'SAT', DAYNO) SAT
FROM CAL

이 쿼리를 수행하면 아래와 같이 각 요일에 해당되는 숫자가 길게 출력이 됩니다.
두번째 단계로 MAX() 또는 MIN() , 또는 AVG() 함수를 사용합니다.

 

SELECT 	MAX(DECODE(DAY, 'SUN', DAYNO)) SUN,
		MAX(DECODE(DAY, 'MON', DAYNO)) MON,
		MAX(DECODE(DAY, 'TUE', DAYNO)) TUE,
		MAX(DECODE(DAY, 'WED', DAYNO)) WED,
		MAX(DECODE(DAY, 'THU', DAYNO)) THU,
		MAX(DECODE(DAY, 'FRI', DAYNO)) FRI,
		MAX(DECODE(DAY, 'SAT', DAYNO)) SAT
FROM CAL

 

위 결과들을 보면 SUN에 출력된 숫자가 8, 15, 22, 29가 나왔습니다. 근데 거기서 MAX함수를 사용하면 당연히 29가 나와야 하는데 8이 나왔습니다.
이것은 사실 위의 숫자가 사실은 숫자가 아니라 문자라는 것에 있습니다.
크기를 비교할 때는 숫자만 비교할 수 있는데 문자의 크기를 비교하라고 하면 오라클은 해당 문자를 ASCII코드로 변환해서 비교를 합니다.
ASCII코드로 문자 '2' 가 50으로 변환되는 것이죠.
그런데 중요한 부분은 ASCII코드로 변환할 때 두 글자 이상일 경우 가장 앞에 있는 글자만 변환해서 비교를 한다는 점 입니다. 즉 '2' 와 '29', 혹은 'A' 'AB'의 ASCII코드 값이 같다는 말입니다.

 

SELECT ASCII('2') FROM DUAL;
SELECT ASCII('29') FROM DUAL;
SELECT ASCII('8') FROM DUAL;

이 쿼리의 결과를 보면 '2' 와 '29' 모두 50 입니다. 그런데 '8'은 56으로 확인이 됩니다.
그래서 MAX값을 구하면 '29'보다 '8'이 크다고 나오는 것입니다.

자 그리고 그다음에 WEEKNO(주) 별로 그룹핑을 하겠습니다.

 

 

SELECT 	MAX(DECODE(DAY, 'SUN', DAYNO)) SUN,
		MAX(DECODE(DAY, 'MON', DAYNO)) MON,
		MAX(DECODE(DAY, 'TUE', DAYNO)) TUE,
		MAX(DECODE(DAY, 'WED', DAYNO)) WED,
		MAX(DECODE(DAY, 'THU', DAYNO)) THU,
		MAX(DECODE(DAY, 'FRI', DAYNO)) FRI,
		MAX(DECODE(DAY, 'SAT', DAYNO)) SAT
FROM CAL
GROUP BY WEEKNO

주별로 요일별로 이제 다 나오기는 하지만 정렬이 되지 않았군요. 그래서 다시 WEEKNO별로 정렬을 수항하면 최종적으로 결과가 나오는 것입니다.

 

2. PIVOT 기능을 사용하여 달력만들기

SELECT *
FROM (SELECT WEEKNO "WEEK", DAY, DAYNO FROM CAL)
PIVOT
( MAX(DAYNO) FOR DAY IN ('SUN' AS "SUN",
			'MON' AS "MON",
			'TUE' AS "TUE",
			'WED' AS "WED",
			'THU' AS "THU",
			'FRI' AS "FRI",
			'SAT' AS "SAT"
					)
)
ORDER BY "WEEK";

PIVOT절에 MAX(DAYNO) 절은 DECODE 문장에서 사용되는 함수를 적으면 되고 FOR 절에는 화면에 집계될 그룹핑할 컬럼을 적으면 됩니다. 그리고 IN 연산자 뒤에는 서브쿼리를 사용할 수 있습니다.

 

 

한번 더 해보죠, 이번에는 EMP테이블에서 부서별로 각 직급별 인원이 몇명인지 계산하는 쿼리입니다.

1. DECODE 사용

SELECT  DEPTNO,
		COUNT(DECODE(JOB, 'CLERK', '0'))		"CLERK", 
		COUNT(DECODE(JOB, 'MANAGER', '0'))		"MANAGER", 
		COUNT(DECODE(JOB, 'PRESIDENT', '0'))		"PRESIDENT", 
		COUNT(DECODE(JOB, 'ANALYST', '0'))		"ANALYST", 
		COUNT(DECODE(JOB, 'SALESMAN', '0'))		"SALESMAN"
FROM EMP
GROUP BY DEPTNO 
ORDER BY DEPTNO

 

자 이 쿼리도 하나하나 분석을 해보면 일단,

SELECT  DEPTNO,
		DECODE(JOB, 'CLERK', '0')		"CLERK", 
		DECODE(JOB, 'MANAGER', '0')		"MANAGER", 
		DECODE(JOB, 'PRESIDENT', '0')		"PRESIDENT", 
		DECODE(JOB, 'ANALYST', '0')		"ANALYST", 
		DECODE(JOB, 'SALESMAN', '0')		"SALESMAN"
FROM EMP;

DEPTNO별로 해당 JOB 부분에 0으로 표시되어있습니다. DECODE(JOB, 'CLERK', 0)부분의 뜻이 JOB이 CLERK이라면 0을 출력해라 라는 뜻인데 JOB이 있는 자리에 0 표시한 것을 모두 DEPTNO별로 더하면 총 인원수가 나오게 됩니다.
그래서 DEPTNO컬럼으로 GROUP BY를 수행하면 됩니다. 위 예에서는 0으로 표시했지만 당연히 다른 것도 됩니다.
자 그러고 나서 카운트 하고 정렬을 하면 됩니다.

 

2. PIVOT 사용

SELECT * FROM (SELECT DEPTNO, JOB, EMPNO FROM EMP)
PIVOT
( COUNT(EMPNO) FOR JOB IN 	(	'CLERK' AS 		"CLERK",
								'MANAGER' AS 	"MANAGER",
								'PRESIDENT' AS 	"PRESIDENT",
								'ANALYST' AS 	"ANALYST",
								'SALESMAN' AS 	"SALESMAN"
							)
)
ORDER BY DEPTNO;

 

DECODE 부분에서 사용했던 COUNT함수를 쓰고 FOR 부분에 그룹핑할 컬럼이름과 IN 부분에 분류할 목록을 적어주었습니다. 조금 복작하긴 하지만 PIVOT부분에 다음과 같이 그룹핑할 조건을 여러 개 사용해도 됩니다.

SELECT * FROM (SELECT DEPTNO, JOB, EMPNO, SAL FROM EMP)
PIVOT
(
	COUNT(EMPNO) AS COUNT,
	SUM(NVL(SAL, 0)) AS SUM FOR JOB IN 	(	'CLERK' AS 		"C",
											'MANAGER' AS 	"M",
											'PRESIDENT' AS 	"D",
											'ANALYST' AS 	"A",
											'SALESMAN' AS 	"S"
										)
)
ORDER BY DEPTNO;