행위

오라클 피봇 Pivot

DB CAFE

Dbcafe (토론 | 기여)님의 2019년 12월 20일 (금) 08:40 판
thumb_up 추천메뉴 바로가기

[Oracle] 오라클 피벗(2) - PIVOT, UNPIVOT, 행을 열로, 열을 행으로 업무를 하다보면 행을 열으로 전환한다던지, 열을 행으로 전환해야하는 업무가 간혹 발생한다. 이때 보통 decode 또는 case when을 통해 행 => 열 전환을 하였을 것이다. 이를 편하게 해결 할 수 있는 기능이 Oracle 11g부터 제공된다.

PIVOT 

- 행을 열로 변환할 때 사용하는 방법으로 기존 GROUP BY 와 집계함수(MAX, SUM 등), DECODE를 사용하는 방법을 대체한다. 코드가 매우 간결 해진다.
- PIVOT 절은 GROUP Function 을 포함한 계산식을 정의 하며 FOR 절은 값을 구분할 (DECODE 사용시 조건식이 정의될) 컬럼명과 IN 으로 값을 정의한다. 이때 IN 연산자는 Subquery 는 포함 할 수 없다. Alias 정의도 가능하다.

UNPIVOT 

- PIVOT 의 반대 개념으로 열을 행으로 변환할 때 사용하는 방법으로 기존 복제용 집합과의 CROSS JOIN 을 통한 행복제 방법과 DECODE 를 조합하는 방법을 대체합니다.

다음과 같은 예제를 보며 이해 하도록 하자.

1) DECODE와 집계함수를 이용한 피벗 


WITH TEMP_TABLE AS (
 SELECT (TO_DATE('20160101','YYYYMMDD') + LEVEL-1) DTE
 FROM DUAL
 CONNECT BY LEVEL-1 <= TO_DATE('20171231','YYYYMMDD') - TO_DATE('20160101','YYYYMMDD')
)

SELECT  TO_CHAR(DTE, 'YY') || '년' YEAR
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'01', 1, 0)) AS "1월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'02', 1, 0)) AS "2월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'03', 1, 0)) AS "3월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'04', 1, 0)) AS "4월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'05', 1, 0)) AS "5월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'06', 1, 0)) AS "6월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'07', 1, 0)) AS "7월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'08', 1, 0)) AS "8월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'09', 1, 0)) AS "9월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'10', 1, 0)) AS "10월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'11', 1, 0)) AS "11월"
        , SUM(DECODE(TO_CHAR(DTE, 'MM'),'12', 1, 0)) AS "12월"
FROM    TEMP_TABLE
GROUP BY TO_CHAR(DTE, 'YY')
ORDER BY YEAR
;





2) PIVOT 함수를 이용 

WITH TEMP_TABLE AS (
 SELECT (TO_DATE('20160101','YYYYMMDD') + LEVEL-1) DTE
 FROM DUAL
 CONNECT BY LEVEL-1 <= TO_DATE('20171231','YYYYMMDD') - TO_DATE('20160101','YYYYMMDD')
)
SELECT *
FROM (
      SELECT  TO_CHAR(DTE, 'YY') || '년' YEAR
              , DECODE (TO_CHAR(DTE, 'MM'), '01', '1월', '02', '2월', '03', '3월', '04', '4월', '05', '5월', '06', '6월', '07', '7월', '08', '8월', '09', '9월', '10', '10월', '11', '11월', '12', '12월') AS MON
              , DTE
      FROM    TEMP_TABLE
      )
      PIVOT 
      (
        COUNT (DTE) FOR MON IN ('1월', '2월', '3월', '4월', '5월', '6월', '7월', '8월', '9월', '10월', '11월', '12월')
      )
ORDER BY YEAR
;





3) UNPIVOT 함수를 위한 쉬운 예제

ex) 기본 데이터 =>




3.1 PIVOT (행 => 열) 


WITH TEMP_LOGIN_HIS AS (
    SELECT '1월' 월, '갓댐' 성명 FROM DUAL UNION ALL
    SELECT '1월' 월, '홍길동' 성명 FROM DUAL UNION ALL
    SELECT '1월' 월, '갓댐' 성명 FROM DUAL UNION ALL
    SELECT '2월' 월, '갓댐' 성명 FROM DUAL UNION ALL
    SELECT '2월' 월, '홍길동' 성명 FROM DUAL
)
SELECT * FROM TEMP_LOGIN_HIS
PIVOT ( COUNT(월) FOR 월 IN ('1월', '2월') );





3.2 UNPIVOT (열 => 행)


ex) 언피벗 성명, '2' 일월, '1' 이월 FROM DUAL UNION ALL 

   SELECT '홍길동' 성명, '1' 일월, '1' 이월 FROM DUAL

) 

SELECT * FROM TEMP_LOGIN_HIS
UNPIVOT (  횟수 FOR 월 IN (일월, 이월) );
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_피봇_Pivot&oldid=2458"