[Oracle] 다중행 함수

 

다중행 함수

다중행 함수는 그룹화와 관련된 함수입니다.

ROLLUP, CUBE, GROUPING SETS, PIVOT, UNPIVOT 등이 있습니다.

예를 들고자, EMP라는 테이블을 사용할 것입니다.

 

1. ROLLUP

이 함수는 GROUP BY절에 넣어서 사용할 수 있습니다.

ROLLUP(그룹화 컬럼1, 그룹화 컬럼2 ...)

결과는 다음과 같습니다. 각각 마지막 컬럼 결과가 하나씩 제거된 그룹화 결과가 출력됩니다.

컬럼1별 컬럼2별 컬럼3별 ... 컬럼n별 결과
...
컬럼1별 컬럼2별 컬럼3별 결과
컬럼1별 컬럼2별 결과
컬럼1별 결과
전체 데이터의 결과

예를 들어, EMP 테이블에서 ROLLUP 없이 부서번호, 업무별 통계를 내볼 수 있습니다.

SELECT DNO
     , JOB
     , MAX(SAL)
     , SUM(SAL)
     , ROUND(AVG(SAL), 2) AS AVG_SAL
     , COUNT(*)
    FROM EMP
    GROUP BY DNO, JOB;

그러면 위와 같이 각 부서번호별로, JOB에 맞게 각각의 통계 값이 출력이 됩니다.

 

여기서 ROLLUP을 사용해보겠습니다.

SELECT DNO
     , JOB
     , MAX(SAL)
     , SUM(SAL)
     , ROUND(AVG(SAL), 2) AS AVG_SAL
     , COUNT(*)
    FROM EMP
    GROUP BY ROLLUP(DNO, JOB)
    ORDER BY DNO, JOB;

아래와 같이 결과가 나오고, 해석해보면 각 부서별로 JOB마다 통계를 냅니다. 그 다음 (null)로 된 부분을 유심히 살펴보시면, JOB에 상관없이 같은 부서일때 통계값을 출력합니다.

마지막 40번 부서 이후에 부서가 (null)인 경우도 동일한 원리로 통계를 내고, 최종 마지막 행에는 부서번호에 상관없이 전체 인원을 대상으로 하는 통계값도 출력하는 것을 알 수 있습니다.

 

 

2. CUBE

위의 ROLLUP과 동일하게 GROUP BY에 넣고, 지정도 동일하게 컬럼들을 입력합니다.

CUBE(그룹화 컬럼1, 그룹화 컬럼2 .....)

다만 컬럼들을 조합할 때 위에 적은 컬럼의 순서는 유지가 됩니다.

ROLLUP과 다른 점은 하나씩 줄어드는 것 뿐아니라 모든 조합이 순서대로 출력된다는 점입니다.

컬럼1별 컬럼2별 컬럼3별 ... 컬럼N별 결과
...
 컬럼1별 컬럼2별 컬럼3별 결과
 컬럼1별 컬럼3별 컬럼N별 결과
 컬럼1별 컬럼2별 컬럼N별 결과
 컬럼2별 컬럼3별 컬럼N별 결과
 컬럼1별 컬럼2별 결과
 컬럼1별 컬럼3별 결과
 컬럼1별 컬럼N별 결과
 컬럼2별 컬럼3별 결과
 컬럼2별 컬럼N별 결과
 컬럼3별 컬럼N별 결과
 컬럼1별 결과
 컬럼2별 결과
 컬럼3별 결과
 컬럼N별 결과
 전체 데이터의 결과

CUBE는 ROLLUP과 동일한 양식을 갖지만, 다른 방법으로 작동합니다. 일반적으로 그룹화되는 컬럼들의 모든 조합을 그룹화해서 결과를 출력합니다. 또한, 그룹화 순서는 변하지 않습니다.

 

실제로 EMP 테이블에 적용해보겠습니다.

SELECT DNO
     , JOB
     , MAX(SAL)
     , SUM(SAL)
     , ROUND(AVG(SAL), 2) AS AVG_SAL
     , COUNT(*)
    FROM EMP
    GROUP BY CUBE(DNO, JOB)
    ORDER BY DNO, JOB;

ROLLUP과 차이가 보이시나요? 처음부터 40번 부서까지는 동일하지만, 부서가 (null)이고 JOB이 개발인 경우를 끝으로, 거기서부터 부서에 상관없이 각 JOB별로 통계를 내는 부분에서 차이가 나는 것을 확인할 수 있습니다. 결과적으로 부서와 JOB에 상관없이 최종 통계는 동일하게 출력됩니다.

 

 

3. GROUPING SETS

GROUP BY에 넣을 수 있고, 위 함수처럼 컬럼을 지정할 수 있습니다.

각 단일 컬럼을 기준으로 그룹화 진행한 결과를 보여줍니다.

컬럼1별 결과
컬럼2별 결과
컬럼3별 결과
....
컬럼n별 결과

이를 EMP 테이블에 적용해보면, CUBE에서 보았던 각 컬럼별 결과만 출력되는 것을 확인할 수 있습니다. 해당 컬럼의 통계값만 내는 것입니다. 각 부서별로 하나씩, JOB별로도 하나씩 그룹화된 통계값을 출력합니다.

SELECT DNO
     , JOB
     , MAX(SAL)
     , SUM(SAL)
     , ROUND(AVG(SAL), 2) AS AVG_SAL
     , COUNT(*)
    FROM EMP
    GROUP BY GROUPING SETS(DNO, JOB)
    ORDER BY DNO, JOB;

3-1. GROUPING

이번에는 반대로 위의 그룹화 함수의 결과값이 어떤 컬럼을 기준으로 그룹화된 것인지 알고 싶을 수 있습니다. 이때 GROUPING 함수를 사용합니다. 아래 결과를 보면, GROUPING(DNO)가 1이면 DNO에 의해 그룹화 된 것이며, GROUPING(JOB)이 1이면 JOB별로 그룹화된 통계값이라는 것을 나타냅니다.

JOB에 의해서 부분적으로 그룹화되고, 최종적으로는 DNO, JOB 모두에 의해 그룹화된 결과가 나타났다고 해석할 수 있습니다.

SELECT DNO
     , JOB
     , MAX(SAL)
     , SUM(SAL)
     , ROUND(AVG(SAL), 2) AS AVG_SAL
     , COUNT(*)
     , GROUPING(DNO) 
     , GROUPING(JOB)
    FROM EMP
    GROUP BY ROLLUP(DNO, JOB)
    ORDER BY DNO, JOB;

4. PIVOT

기존 테이블의 행을 열로 조회할 수 있습니다. (행 → 열)

PIVOT(행의 값이 될 데이터
    FOR 열로 변경할 컬럼 IN (
        열로 변경될 컬럼의 데이터들의 열이름 지정
    )
)

항상 서브쿼리를 사용합니다.

필요한 데이터만 가져와서 사용할 수 있고, 원하는 데이터를 컬럼으로 바꿀 수 있습니다. 이 때 새로운 서브쿼리로 만들어 사용하는 것을 추천합니다.

 

 

ex) EMP 테이블에서 각 업무별 최고 연봉을 확인하는 칼럼을 작성할 수 있습니다. 업무별로 하나의 행만 나타날 것이라 예상할 수 있습니다.

SELECT *
    FROM (
            SELECT JOB
                 , DNO
                 , SAL
                FROM EMP
    )
PIVOT(MAX(SAL)
    FOR JOB IN ('경영' AS OPER,
                '지원' AS HELP,
                '개발' AS DEV,
                '회계' AS ACCOUNT,
                '분석' AS ANALYS
                )
)
ORDER BY DNO;

이렇게 출력이 됩니다. 각 행에 있던 성분들(업무)이 컬럼으로 변환되고, 내부의 value는 연봉의 최대치로 입력된 것을 확인할 수 있습니다.

 

5. UNPIVOT

기존 테이블의 열을 행으로 조회할 수 있습니다. (열 → 행)

UNPIVOT(
        컬럼명 FOR 컬럼명 IN (현재 열 이름들)
        --기존 행데이터를 받아올 컬럼명 FOR 열을 행데이터로 받아줄 컬럼명 IN (현재 열의 이름들)
    )

예를 들어, EMP 테이블이 있다고 가정합니다. 이 때, UNPIVOT을 위해서는 앞서 PIVOT으로 출력된 컬럼마다 하나씩 값이 있는 데이터를 만들어주는 작업이 필요합니다. 그러면, 여기서 컬럼이 다시 행으로 전환되고, 값은 연봉최대치로 표기되는 열이름으로 설정해야 합니다.

 

이 때, DECODE를 활용해서 JOB에서 경영인 것의 SAL(연봉)을 구할 수 있고 이를 반복해서 각 업무별 최대 연봉만을 따로 모아서, 그것을 AS 함수를 통해 새로운 컬럼명으로 붙이는 작업이 선행됩니다.

그 결과는 아래와 같습니다.

SELECT MAX(DECODE(JOB, '경영', SAL)) AS "경영"
     , MAX(DECODE(JOB, '지원', SAL)) AS "지원"
     , MAX(DECODE(JOB, '회계', SAL)) AS "회계"
     , MAX(DECODE(JOB, '개발', SAL)) AS "개발"
     , MAX(DECODE(JOB, '분석', SAL)) AS "분석"
    FROM EMP

 

이 쿼리를 서브쿼리로 설정하고, UNPIVOT 함수를 붙이면, 이것을 열을 행으로 바꿀 수 있습니다.

이 때, SAL FOR JOB IN (위에서 설정한 컬럼명) 처럼 JOB을 기준으로, SAL의 값을 설정하는 코드가 추가됩니다.

SELECT *
    FROM (
            SELECT MAX(DECODE(JOB, '경영', SAL)) AS "경영"
                 , MAX(DECODE(JOB, '지원', SAL)) AS "지원"
                 , MAX(DECODE(JOB, '회계', SAL)) AS "회계"
                 , MAX(DECODE(JOB, '개발', SAL)) AS "개발"
                 , MAX(DECODE(JOB, '분석', SAL)) AS "분석"
                FROM EMP
        )
        
    UNPIVOT(
            SAL FOR JOB IN (경영, 지원, 회계, 개발, 분석) --컬럼이라 그냥 컬럼명으로 잡기. 문자열 X
--            SAL FOR JOB IN ("경영", "지원", "회계", "개발", "분석") --혹은 큰따옴표로 잡아두기.
    );

출력하면 위와 같이 나타나며, JOB에 있던 값들이 하나의 행이름이 되고, SAL(연봉)은 값으로 대응되어 하나씩 나타납니다. PIVOT 결과를 TRANSPOSE(전치)한 것과 동일한 결과가 나타나는 것을 확인할 수 있습니다.