"오라클 피봇 Pivot"의 두 판 사이의 차이
DB CAFE
잔글 (Dbcafe님이 Pivot 문서를 오라클 피봇 Pivot 문서로 이동했습니다) |
|||
1번째 줄: | 1번째 줄: | ||
− | + | == 오라클 피벗 == | |
− | 업무를 하다보면 행을 열으로 | + | {{틀:고지 상자 |
+ | |내용= | ||
+ | 업무를 하다보면 행을 열으로 전환 한다던지, 열을 행으로 전환해야하는 업무가 간혹 발생한다. | ||
이때 보통 decode 또는 case when을 통해 행 => 열 전환을 하였을 것이다. | 이때 보통 decode 또는 case when을 통해 행 => 열 전환을 하였을 것이다. | ||
− | 이를 편하게 해결 할 수 있는 기능이 Oracle | + | 이를 편하게 해결 할 수 있는 기능이 Oracle 11g 부터 제공된다. |
− | + | }} | |
− | PIVOT | + | === PIVOT === |
− | + | * 행을 열로 | |
− | + | * 행을 열로 변환할 때 사용하는 방법으로 기존 GROUP BY 와 집계함수(MAX, SUM 등), DECODE를 사용하는 방법을 대체한다. 코드가 매우 간결 해진다. | |
− | + | * PIVOT 절은 GROUP Function 을 포함한 계산식을 정의 하며 FOR 절은 값을 구분할 (DECODE 사용시 조건식이 정의될) 컬럼명과 IN 으로 값을 정의한다. | |
− | + | *:* 이때 IN 연산자는 Subquery 는 포함 할 수 없다. | |
− | + | *:* Alias 정의도 가능하다. | |
− | + | * DECODE와 집계함수를 이용한 피벗 | |
− | |||
− | |||
− | |||
− | |||
<source lang=sql> | <source lang=sql> | ||
WITH TEMP_TABLE AS ( | WITH TEMP_TABLE AS ( | ||
40번째 줄: | 38번째 줄: | ||
; | ; | ||
</source> | </source> | ||
− | + | * PIVOT 함수를 이용 | |
− | + | <source lang=sql> | |
− | |||
− | <source lang=sql> | ||
WITH TEMP_TABLE AS ( | WITH TEMP_TABLE AS ( | ||
SELECT (TO_DATE('20160101','YYYYMMDD') + LEVEL-1) DTE | SELECT (TO_DATE('20160101','YYYYMMDD') + LEVEL-1) DTE | ||
70번째 줄: | 66번째 줄: | ||
* 피봇은 FROM 절에 걸어준 테이블의 모든 컬럼 중 PIVOT 절에 기술한 컬럼을 제외하고 모두 GROUP BY 해버린다. | * 피봇은 FROM 절에 걸어준 테이블의 모든 컬럼 중 PIVOT 절에 기술한 컬럼을 제외하고 모두 GROUP BY 해버린다. | ||
− | 즉, GROUP BY 할 대상들만 Sub-Query 또는 With 절로 묶어서 추려낸 뒤 피봇을 해야 한다. | + | *: 즉, GROUP BY 할 대상들만 Sub-Query 또는 With 절로 묶어서 추려낸 뒤 피봇을 해야 한다. |
<source lang=sql> | <source lang=sql> | ||
WITH TEMP AS ( | WITH TEMP AS ( | ||
84번째 줄: | 80번째 줄: | ||
); | ); | ||
</source> | </source> | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
+ | === UNPIVOT === | ||
+ | * 열을 행으로 | ||
+ | * PIVOT 의 반대 개념으로 열을 행으로 변환할 때 사용하는 방법으로 기존 복제용 집합과의 CROSS JOIN 을 통한 행복제 방법과 DECODE 를 조합하는 방법을 대체합니다. | ||
+ | * UNPIVOT 함수를 위한 쉬운 예제 | ||
+ | * UNPIVOT (열 => 행) | ||
+ | *: ex) 언피벗 | ||
+ | <source lang=sql> | ||
+ | SELECT '스캇' 성명 , '2' 일월, '1' 이월 FROM DUAL | ||
+ | UNION ALL | ||
+ | SELECT '홍길동' 성명, '1' 일월, '1' 이월 FROM DUAL | ||
+ | ) | ||
+ | SELECT * FROM TEMP_LOGIN_HIS | ||
+ | UNPIVOT ( | ||
+ | 횟수 FOR 월 IN (일월, 이월) | ||
+ | ); | ||
+ | </source> | ||
+ | * PIVOT (행 => 열) | ||
<source lang=sql> | <source lang=sql> | ||
WITH TEMP_LOGIN_HIS AS ( | WITH TEMP_LOGIN_HIS AS ( | ||
− | SELECT '1월' 월, ' | + | SELECT '1월' 월, '스캇' 성명 FROM DUAL UNION ALL |
SELECT '1월' 월, '홍길동' 성명 FROM DUAL UNION ALL | SELECT '1월' 월, '홍길동' 성명 FROM DUAL UNION ALL | ||
− | SELECT '1월' 월, ' | + | SELECT '1월' 월, '스캇' 성명 FROM DUAL UNION ALL |
− | SELECT '2월' 월, ' | + | SELECT '2월' 월, '스캇' 성명 FROM DUAL UNION ALL |
SELECT '2월' 월, '홍길동' 성명 FROM DUAL | SELECT '2월' 월, '홍길동' 성명 FROM DUAL | ||
) | ) | ||
105번째 줄: | 112번째 줄: | ||
IN ('1월', '2월') ) -- 필터링 조건 | IN ('1월', '2월') ) -- 필터링 조건 | ||
; | ; | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> | ||
[[Category:oracle]] | [[Category:oracle]] |
2023년 4월 2일 (일) 21:15 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 오라클 피벗[편집]
android 업무를 하다보면 행을 열으로 전환 한다던지, 열을 행으로 전환해야하는 업무가 간혹 발생한다. 이때 보통 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 (열 => 행)
- ex) 언피벗
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월') ) -- 필터링 조건
;