행위

오라클 피봇 Pivot

DB CAFE

(Pivot에서 넘어옴)
thumb_up 추천메뉴 바로가기


1 오라클 피벗[편집]

record_voice_over

  • 업무를 하다보면 행을 열으로 전환 한다던지, 열을 행으로 전환해야하는 업무가 간혹 발생한다.
    • 이때 보통 decode 또는 case when을 통해 행 => 열 전환을 하였을 것이다.
    • 이를 편하게 해결 할 수 있는 기능이 Oracle 11g 부터 제공된다.


1.1 PIVOT[편집]

  • 행을 열로
  • 행을 열로 변환할 때 사용하는 방법으로 기존 GROUP BY 와 집계함수(MAX, SUM 등), DECODE를 사용하는 방법을 대체한다. 코드가 매우 간결 해진다.
  • PIVOT 절은 GROUP Function 을 포함한 계산식을 정의 하며 FOR 절은 값을 구분할 (DECODE 사용시 조건식이 정의될) 컬럼명과 IN 으로 값을 정의한다.
    • 이때 IN 연산자는 Subquery 는 포함 할 수 없다.
    • Alias 정의도 가능하다.
  • 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
;
  • 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 
      (
        -- group 값
        COUNT (DTE)
        -- group 대상,값을 구분 
          FOR MON 
        -- 필터링
           IN ('1월', '2월', '3월', '4월', '5월', '6월', '7월', '8월', '9월', '10월', '11월', '12월')
      )
ORDER BY YEAR
;
  • 피봇은 FROM 절에 걸어준 테이블의 모든 컬럼 중 PIVOT 절에 기술한 컬럼을 제외하고 모두 GROUP BY 해버린다.
    즉, GROUP BY 할 대상들만 Sub-Query 또는 With 절로 묶어서 추려낸 뒤 피봇을 해야 한다.
WITH TEMP AS (
   SELECT DEPTNO, SAL
    FROM EMP
)
SELECT *
  FROM TEMP
 PIVOT(
        SUM(SAL)
        FOR DEPTNO
        IN (10, 20, 30)
      );

1.2 UNPIVOT[편집]

  • 열을 행으로
  • PIVOT 의 반대 개념으로 열을 행으로 변환할 때 사용하는 방법으로 기존 복제용 집합과의 CROSS JOIN 을 통한 행복제 방법과 DECODE 를 조합하는 방법을 대체합니다.
  • UNPIVOT 함수를 위한 쉬운 예제
  • UNPIVOT (열 => 행)
SELECT '스캇' 성명 , '2' 일월, '1' 이월 FROM DUAL 
 UNION ALL
SELECT '홍길동' 성명, '1' 일월, '1' 이월 FROM DUAL
)
SELECT * FROM TEMP_LOGIN_HIS
UNPIVOT (  
         횟수 FOR 월 IN (일월, 이월) 
        );
  • 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월') ) -- 필터링 조건
;