[SQL]열을 행으로, 행을 열로 변환하기 및 데이터 분석 함수(PIVOT, UNPIVOT, SUM OVER, PATIO_TO_REPORT, ROLLUP, CUBE, GROUPING SETS, ROW_NUMBER)

047 COLUMN을 ROW로 출력하기 1. SUM+DECODE

테이블의 행(row)을 열(col)로 출력하려면 합을 계산하는 SUM 함수와 조건 함수 DECODE를 사용합니다.

행을 열로 출력하는 과정은 조건 함수 DECODE를 통해 조건을 만족하는 데이터 중 특정 컬럼의 합을 구하는 것으로 아래와 같이 나타낼 수 있습니다.

SUM( DECODE(조건COL, 조건, 출력COL) )

예를 들어 A학교 학생인 학생의 한달 소비금액의 총합을 구하는 경우 SUM(DECODE(학교, A학교, 소비금액))과 같이 나타낼 수 있습니다. 가로로 출력하는 과정을 아래 문제를 통해 풀어보겠습니다.

 

Q. 부서 번호, 부서 번호별 월급의 총합을 출력하는데, 가로로 출력하시오

1. 부서 별 월급 정보를 출력

문제를 통해 알 수 있듯 이 부서 번호별 월급의 총합을 구하기 때문에, 부서 번호가 10번, 20번, 30번 인 사원들 각각의 월급을 출력할 수 있게 해야 합니다. 예를 들어 부서 번호가 10번인 조건을 만족하면 월급을 출력한다면 조건을 만족하면 특정 컬럼을 출력하는 DECODE함수를 사용하여 다음과 같은 쿼리를 작성할 수 있습니다.

SELECT DEPTNO, DECODE(DEPTNO, 10, SAL) AS "10"
FROM EMP;

 

2. 월급의 합계 구하기 

위 쿼리는 부서 번호가 10번이면 월급이 출력되고, 아니면 NULL이 출력되는 쿼리입니다. 이 결과에서 DEPTNO 컬럼을 제외하고, DECODE(DEPTNO, 10, SAL)의 결과 값을 SUM 함수로 다 더해서 출력하면 다음과 같습니다.

SELECT SUM(DECODE(DEPTNO, 10, SAL)) AS "10"
FROM EMP;

여기에 부서 번호가 20번일 때와 30번일 때의 월급 합계를 같이 출력하면 다음 쿼리가 됩니다.

SELECT SUM(DECODE(DEPTNO, 10, SAL)) AS "10",
        SUM(DECODE(DEPTNO, 20, SAL)) AS "20",
        SUM(DECODE(DEPTNO, 30, SAL)) AS "30"
FROM EMP;

 

Q. 직업별 월급의 총합을 가로로 출력하시오.

EMP 테이블에서 나타나는 직업은 ANALYST, CLERK, MANAGER, SALESMAN이 있습니다.

1. 직업별 월급 출력 -> DECODE(JOB, 'ANALYST', SAL), ...

2. 월급의 합계 SUM(DECODE(JOB, 'ANALYST', SAL)),...

SELECT SUM(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST",
       SUM(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
       SUM(DECODE(JOB, 'MANAGER', SAL)) "AS MANAGER",
       SUM(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN"
FROM EMP;

위의 쿼리는 EMP 테이블에 직업이 ANALYST, CLERK, MANAGER, SALESMAN이 있다는 것을 안다는 가정 하에 작성된 쿼리입니다. 모른다고 가정하고 출력하게 하려면 PL/SQL을 사용해야 합니다.

 

Q. 부서 번호별로 각 직업의 월급 총합을 가로로 출력하시오.

직업뿐만 아니라 부서별로도 월급의 총합을 구해야 하는 문제입니다. 따라서 첫 번째 컬럼에 DEPTNO 컬럼을 추가 해  부서별로 합계를 확인할 수 있게 합니다. DEPTNO를 그룹 함수와 같이 나열하였으므로 GROUP BY DEPTNO를 추가하여 실행합니다. (부서 번호를 오름 차순으로 정렬하기 위해 ORDER BY DEPTNO를 추가적으로 실행했습니다.)

SELECT DEPTNO, SUM(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST",
               SUM(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
               SUM(DECODE(JOB, 'MANAGER', SAL)) "AS MANAGER",
               SUM(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

위 결과를 통해 CLERK과 MANAGER는 모든 부서에 존재하지만, ANALYST는 20번에만, SALESMAN은 30번에만 있음을 알 수 있습니다.

 

048 COLUMN을 ROW로 출력하기 2. PIVOT

PIVOT은 47번 예제에서 봤던 DECODE와  SUM 함수보다 쉽게 column을 row를 출력할 수 있습니다. 여기서 PIVOT 문은 다음과 같이 씁니다.

PIVOT(출력결과 FOR 기준column IN (value1, value2, value3,...))

자세한 PIVOT문 사용방법은 다음 예제를 통해 살펴보겠습니다.

 

Q. 부서 번호, 부서 번호별 월급의 총합을 PIVOT문을 사용하여 가로로 출력하시오

PIVOT문을 사용할 때 중요한 점은 필요한 데이터만을 FROM 절을 통해 조회한다는 점입니다. 예제에서 필요한 데이터는 부서 번호(DEPTNO)와 월급(SAL) 뿐입니다. 그래서 FROM 절에 EMP 테이블을 입력하는 것이 아닌 쿼리문을 사용하여 EMP 테이블에서 부서 번호와 월급만을 조회합니다.

SELECT *
FROM (SELECT DEPTNO, SAL FROM EMP)

 그리고 출력되는 부서 번호와 월급은 이 전체 쿼리문에서 마치 테이블처럼 사용됩니다. 이제 FROM 절에서 추출한 부서 번호와 월급을 가지고 부서 번호별 월급의 총합을 PIVOT문을 통해 출력합니다. 부서 번호는 10번, 20번, 30번에 대한 것을 출력합니다.

SELECT *
FROM (SELECT DEPTNO, SAL FROM EMP)
PIVOT (SUM(SAL) FOR DEPTNO IN (10, 20, 30));

 

Q. 직업과 직업별 월급의 총합을 PIVOT문을 사용하여 가로로 출력하시오

필요한 데이터 : 직업(JOB), 월급(SAL)

SELECT *
FROM (SELECT JOB, SAL FROM EMP)
PIVOT (SUM(SAL) FOR JOB IN ('ANALYST', 'CLERK', 'MANAGER', 'SALESMAN'));

 

※ 작은따옴표('') 없이 출력하려면 별칭을 설정할 때와 마찬가지로 AS 뒤에 큰따옴표("") 사이에 별칭을 지정하면 위의 결과와 같이 작은따옴표 없이 출력할 수 있습니다.

EX ) PIVOT (SUM(SAL) FOR JOB IN ('ANALYST' AS "ANALYST"))

 

049 ROW를 COLUMN으로 출력하기(UNPIVOT)

PIVOT문과 반대로 테이블의 행(row)을 열(column)으로 출력할 때 UNPIVOT 함수를 사용합니다. UNPIVOT의 경우 PIVOT문을 사용할 때와 유사한 구조로 작성됩니다.

UNPIVOT(결과column FOR 기준column IN (value1, value2, value3,...))

 여기서 결과 column은 가로로 저장되어 있는 데이터를 세로로 UNPIVOT 시킬 결과(출력) 열 이름입니다. 이 열 이름은 임의로 지정하면 됩니다. FOR 다음의 기준 column은 가로로 되어 있는 order2 테이블의 컬럼명을 UNPIVOT 시켜 세로로 출력할 열 이름입니다. 이 열 이름 또한 임의로 지정하면 됩니다.

예제를 통해 살펴보기 위해 아래 쿼리를 실행하여 새로운 테이블 ORDER2를 커밋합니다.

-- 테이블 리셋
DROP TABLE ORDER2

-- 테이블 생성
CREATE TABLE order2
( ENAME VARCHAR2(10),
  BICYCLE NUMBER(10),
  CAMERA NUMBER(10),
  NOTEBOOK(10) );
  
-- 데이터 INSERT
INSERT INTO ORDER2 VALUES('SMITH', 2, 3, 1);
INSERT INTO ORDER2 VALUES('ALLEN', 1, 2, 3);
INSERT INTO ORDER2 VALUES('KING', 3, 2, 2);

COMMIT;

위의 쿼리를 실행하면 아래와 같은 order2 테이블이 만들어집니다

여기서 UNPIVOT문을 사용하여 COLUMN을 ROW로 출력해보겠습니다.

order2 테이블에서 컬럼은 BICYCLE, CAMERA, NOTEBOOK으로 각각의 ITEM에 해당됩니다. 이 ITEM들을 SMITH, ALLEN, KING 이 각각 주문한 정보이므로 이에 대한 건수(CNT)를 ROW로 나타내 세로로 나열할 것입니다.

SELECT *
FROM ORDER2
UNPIVOT (건수 FOR 아이템 IN (BICYCLE, CAMERA, NOTEBOOK));

만약 order2 테이블의 데이터에 NULL이 포함되어 있었다면 UNPIVOT 된 결과에서 출력이 되지 않습니다. 다음의 UPDATE문을 실행하여 SMITH의 NOTEBOOK을 NULL로 변경하고 UNPIVOT문을 다시 실행해봅니다.

UPDATE ORDER2 SET NOTEBOOK = NULL WHERE ENAME = 'SMITH';

SMITH의 NOTEBOOK을 NULL로 변경하고 UNPIVOT문을 다시 실행해본 결과 SMITH의 NOTEBOOK CNT가 아예 표시되지 않음을 확인할 수 있습니다.

만약 SMITH의 NOTEBOOK 정보처럼 NULL값이 있지만 출력하고 싶은 경우 NULL 값인 행도 결과에 포함시키기 위해 다음과 같이 INCLUDE NULLS를 사용합니다.

SELECT *
FROM order2
UNPIVOT INCLUDE NULLS (CNT FOR ITEM IN (BICYCLE, CAMERA, NOTEBOOK));

 

 

050 데이터 분석 함수로 누적 데이터 출력하기(SUM OVER)

데이터를 누적해서 출력할 때 SUM OVER 함수를 사용합니다.  SUM OVER 함수는 아래와 같은 형식으로 사용됩니다.

SUM(col1) OVER (ORDER BY col2 ROWS BETWEEN UNBOUNDED PERCENDING AND CURRENT ROW)

SUM 다음의 괄호의 col 자리에는 누적할 값의 컬럼을 입력합니다. OVER 다음의 괄호 안에는 값을 누적할 윈도우(ROW)를 지정할 수 있습니다. ORDER BY col2을 통해 col2를 기준으로 정렬하고, 정렬된 것을 기준으로 col1의 누적치를 출력합니다.

UNBOUNDED PRECENDING은 제일 첫 번째 행을 가리킵니다. 그리고 BETWEEN UNBOUNDED AND CURRENT ROW는 제일 첫 번째 행부터 현재 행까지의 값을 말합니다. 

윈도우 기준	윈도우 방식	설명
ROWS	UNBOUNDED PRECENDING	맨 첫 번째 행
	UNBOUNDED FOLLOWING	맨 마지막 행
	CURRENT ROW	현재 행

다음의 예제를 통해 자세히 알아보겠습니다.

Q. 직업이 ANALYST, MANAGER인 사원들의 사원 번호, 이름, 월급, 월급의 누적치를 출력하시오

사원번호를 기준으로 사원 월급의 누적치를 구하므로 누적할 값은 월급이며, 정렬 기준은 사원 번호가 될 것입니다. 따라서 다음과 같이 쿼리를 작성할 수 있습니다.

SELECT EMPNO, ENAME, SAL, SUM(SAL) OVER (ORDER BY EMPNO
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 누적치
FROM EMP
WHERE JOB IN ('ANALYST', 'MANAGER');

 

051 데이터 분석 함수로 비율 출력하기(RATIO_TO_REPORT)

특정 컬럼의 데이터의 합을 기준으로 각 ROW의 상대적 비율을 구할 때 RATIO_TO_REPORT 함수를 사용합니다. RATIO_TO_REPORT 함수는 아래와 같은 방법으로 사용됩니다.

RATIO_TO_REPORT(col1) OVER()

RATIO_TO_REPORT 다음의 괄호에는 상대적 비율을 구할 컬럼을 대입합니다. 

 

Q. 부서 번호가 20번인 사원들의 사원 번호, 이름, 월급을 출력하고 20번 부서 번호 내에서 사원들의 월급 비율이 어떻게 되는지 출력하시오

부서 번호가 20번인 사원들에 대한 조건 → WHERE DEPTNO = 20

사원들의 월급 비율을 구함 → RATIO_TO_REPORT(SAL)

SELECT EMPNO, ENAME, SAL, RATIO_TO_REPORT(SAL) OVER() AS 비율
FROM EMP
WHERE DEPTNO = 20;

RATIO_TO_REPORT 함수는 각 사원들의 월급을 월급의 총합으로 나눈 값과 같음을 다음 쿼리의 결과를 통해 알 수 있습니다.

SELECT EMPNO, ENAME, SAL,
		RATIO_TO_REPORT(SAL) OVER() AS 비율,
        SAL / SUM(SAL) OVER() AS 비교비율

FROM EMP
WHERE DEPTNO = 20;

 

052 데이터 분석 함수로 집계 결과 출력하기 1. ROLLUP

데이터를 집계한 결과에 추가적으로 전체 집계하려면 ROLLUP 함수를 이용합니다. ROLLUP을 사용하면 그룹 맨 아래에 특정 컬럼의 총합을 출력됩니다. ROLLUP은 GROUP BY절에 사용되는 함수로 다음과 같이 사용됩니다.

GROUP BY ROLLUP(col, ...)

집계하려는 컬럼은 한개 이상으로 할 수 있습니다. 예를 들어 컬럼을 2개 사용하면 집계 결과는 컬럼의 개수(2개) + 1 로 3개가 출력됩니다.

집계 결과	1	col1, col2	col2 별 col1별 total
	2	col1	col1별 total
	3	()	전체 total

 

Q. 직업과 직업별 월급의 총합을 출력하시오. 단, 맨 마지막 행에 월급의 총합을 출력하시오.

직업과 직업별 월급의 총합을 출력하는 쿼리에 ROLLUP만 붙여주면 전체 토탈 월급을 추가적으로 볼 수 있습니다. 

SELECT JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(JOB);

※ ROLLUP을 사용하면 데이터가 오름차순으로 정렬되어 출력됩니다.

 

Q. 부서 번호별, 직업별 월급을 출력하고, 마지막 행에 월급의 총합을 출력하시오.

그룹 기준이 부서번호(DEPTNO), 직업(JOB)으로 ROLLUP에 2개의 컬럼을 사용합니다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

 

053 데이터 분석 함수로 집계 결과 출력하기 2. CUBE

데이터를 집계한 결과에 추가적으로 전체 집계를 위쪽에 출력하기위해 CUBE 함수를 사용합니다. 즉 이전에 봤던 ROLLUP의 경우 맨 마자막에 총합을 출력했다면, 가장 첫 번째 행에 출력할 경우 CUBE 함수를 사용합니다. CUBE함수 또한 ROULLUP과 마찬가지로 GROUP BY절에서 사용하는 함수입니다.

GROUP BY CUBE(col, ...)

 

Q. 직업과 직업별 월급의 총합을 출력하시오. 단, 맨 첫 번째 행에 월급의 총합을 출력하시오.

부서 번호와 부서 번호별 월급의 총합을 출력하는 쿼리에 CUBE만 붙여주면 전체 토탈 월급을 첫 번째 행에서 추가적으로 볼 수 있습니다. 

SELECT DEPTNO, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO);

※ CUBE를 사용하면 데이터가 오름차순으로 정렬되어 출력됩니다.

 

Q. 부서 번호별, 직업별 월급을 출력하고, 첫 번째 행에 월급의 총합을 출력하시오.

그룹 기준이 부서번호(DEPTNO), 직업(JOB)으로 CUBE에 2개의 컬럼을 사용합니다.

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);

GROUP BY CUBE(DEPTNO, JOB)은 총 4가지 경우 집계결과가 나옵니다. 전체 총합, 직업별 총합, 부서 번호별 총합, 직업별 부서 번호별 총합으로 총 4가지 집계 결과가 출력됩니다. 이는 GROUP BY ROLLUP(DEPTNO, JOB)과 비교했을 때 JOB에 대한 집계가 하나 더 출력된 것을 알 수 있습니다.

 

054 데이터 분석 함수로 집계 결과 출력하기 3. GROUPING SETS

집계할 컬럼을 직접 선택하여 추가 집계하기 위해서 GROUPING SETS 함수를 사용합니다. GROUPING SETS는 앞에서 배웠던 ROLLUP과 CUBE보다 집계된 결과를 예상하기 쉽습니다. 왜냐하면 GROUPING SETS에 집계하고 싶은 컬럼들을 기술하면 그대로 출력되기 때문입니다. GROUPING SETS 괄호 안에 집계하고 싶은 컬럼명을 기술하면, 기술한대로 결과를 출력합니다.

Q. 부서 번호와 직업, 부서 번호별 월급의 총합과 직업별 월급의 총합, 전체 월급의 총합을 출력하시오

부서 번호별 월급의 총합 → (DEPTNO), 직업별 월급의 총합 → (JOB), 전체 월급의 총합 → ()

SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS((DEPTNO), (JOB), ());

 

예를 들어 ROLLUP문을 사용한 쿼리를 GROUPING SETS로 변경하여 쿼리를 작성하면 다음과 같습니다

ROULLUP을 사용한 경우	GROUPING SETS를 사용한 경우
SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO);	SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY GROUPING SETS((DEPTNO), ());

 

055 데이터 분석 함수로 출력 결과 넘버링 하기(ROW_NUMBER)

ROW_NUMBER()는 출력되는 각 행에 고유한 숫자 값을 부여하는 데이터 분석 함수입니다. 즉 출력되는 순서대로 순위를 부여할 때 ROW_NUMBER 함수를 사용합니다. 우리가 알고있는 순위를 출력하는 RANK나 DENSE_RANK 함수와는 어떻게 다른지 다음 예제를 통해 알아보겠습니다.

SELECT EMPNO, ENAME, SAL, RANK() OVER (ORDER BY SAL DESC) AS RANK,
                           DENSE_RANK() OVER (ORDER BY SAL DESC)  AS DENSE_RANK,
                           ROW_NUMBER() OVER (ORDER BY SAL DESC) AS 번호
FROM EMP
WHERE DEPTNO = 20;

위의 결과를 보면 첫 번째 행인 FORD와 두 번째 행인 SCOTT의 월급이 서로 같아 RANK와 DENSE_RANK는 순위를 둘다 1로 출력하고 있으나, ROW_NUMBER는 1,2 로 출력하고 있습니다. 

ORA-30485

ROW_NUMBER 함수는 OVER 다음에 반드시 ORDER BY 절을 기술해야 합니다. 이를 기술하지 않은 경우 다음과 같은 에러가 발생합니다.

윈도우 지정에 ORDER BY 표현식이 없다는 의미로, ORDER BY 표현식은 필수라는 의미의 에러를 발생시킵니다.

 

Q. 부서번호가 10번 또는 20번인 부서에서 부서 번호, 사원이름, 월급과 부서 번호별 월급에 대한 순위를 출력하시오

부서 번호별 순위를 부여해야하므로 PARTITION BY 절을 사용해야합니다.

SELECT DEPTNO, ENAME, SAL, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 번호
FROM EMP
WHERE DEPTNO IN (10, 20);