오라클 피벗 함수 사용법 쉽고 자세하게 설명 부탁 드립니다.
피벗 굉장히 유용하죠~ 데이터를 입맛에 맞게 잘 뽑아주게 해주는 장점도 있구요 .
내용은 쉬운 예제 링크 퍼왔습니다^.^
설명에 사용할 예시 데이터는 오라클에서 교육용으로 제공해주는 EMP 테이블이며, 자세한 데이터는 아래와 같다. EMP 테이블은 인터넷에 검색만해도 스크립트를 쉽게 얻을 수 있다.
피봇의 구문은 아래의 사진과 같이 PIVOT, PIVOT FOR, PIVOT IN 총 3개의 절이 필요하다.
각 절의 의미에 대해서 알아가면서 하나씩 적용해 볼 것이다.
-. PIVOT 절: 그룹함수가 적용된 컬럼을 정의
피봇함수는 내부적으로 그룹핑된 결과를 제공한다. 그렇기 때문에 PIVOT 절에서 그룹핑된 결과를 정의해야 하는데, 마치 GROUP BY가 들어간 쿼리에서 SELECT SUM(SAL), AVG(SAL) 구절과 같다. PIVOT 절에서는 그룹핑할 대상에 대해서 정의를 해주면 된다. PIVOT 절의 예시로 아래의 사진에서 숫자 값을 예로 들 수 있다. 각 숫자 값들은 부서번호 10, 20, 30에 대한 각각의 그룹핑 결과다. 물론, JOB 컬럼 때문에 조금 더 세분화 되었지만 지금은 부서번호 별로 그룹핑 되었다는 사실까지만 인지하도록 한다. 아래의 결과물을 얻기 위해 본인은 PIVOT 절에 'SUM(SAL)'을 입력했다.
-. PIVOT FOR 절: 피봇의 기준의 되는 컬럼을 정의
PIVOT 절이 그룹핑된 결과물을 제공한다는 사실은 이해했다. 그렇다면 어떠한 기준들로 그룹핑 할 것인지를 PIVOT FOR 절에서 정의해줘야 한다. 마치, GROUP BY 절에 뒤따르는 컬럼과 같은 역할로 이해하면 된다. 아래의 결과물은 피봇과 관계없이 DEPTNO 컬럼을 GROUP BY한 결과다.
'SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO'
위의 SQL 중 SELECT 절의 SUM(SAL)이 PIVOT 절의 역할이라면, GROUP BY DEPTNO은 PIVOT FOR의 역할이다. 아래의 결과물을 얻기 위해 본인은 PIVOT FOR 절에 'DEPTNO'을 입력했다.
-. PIVOT IN 절: PIVOT FOR절에서 정의한 컬럼에서 필터링을 정의
지금까지 PIVOT FOR 절에서 그룹핑하고, 그룹핑한 결과물을 PIVOT 절로 출력하는 개념까지 도달했다. PIVOT IN 절은 WHERE 절과 같은 필터링 역할이다. 아래의 사진과 같이 피봇과 관련없는 결과물을 또 준비했다. 아래의 결과물은 WHERE 필터링을 추가해서 부서번호 10, 20만 출력하도록 했다.
'SELECT DEPTNO, SUM(SAL) FROM EMP WHERE DEPTNO IN (10, 20) GROUP BY DEPTNO'
위의 SQL 중 WHERE IN(10, 20)은 PIVOT IN 절의 역할이다. 아래의 결과물을 얻기 위해 본인은 PIVOT IN 절에 '(10, 20, 30)'을 입력했다. 물론, 현재 테이블의 존재하는 모든 부서번호를 입력했다.
지금까지 내용을 정리해보자면 결과적으로 각각의 절에 아래와 같이 입력했다.
" DEPTNO 컬럼으로 그룹핑하되, 그룹핑의 결과 값은 SUM(SAL)로 표현하며, DEPTNO IN (10, 20, 30)의 필터를 걸어주세요 "
이제 피봇 함수를 읽어 내리는데 큰 어려움이 없을 것 이다. 다만, 피봇을 구현하기 위해 한가지만 더 기억해야 한다. 바로, 데이터의 정제다. 위의 피봇 함수를 EMP 테이블에 그대로 걸어서 사용하면 전혀 다른 결과가 나온다. 이유는 PIVOT 절에 있다. 피봇은 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)
);위와 같은 PIVOT 절의 성질을 이해하면 여러가지 리포팅 화면을 작성할 수 있다. With 절에 JOB 컬럼을 추가하면 아래와 같이 JOB 별로 세분화된 GROUP BY 결과를 볼 수 있다. 물론, 세분화를 하고 싶은 만큼 Sub-Query 또는 With 절에 컬럼을 계속 추가할 수 있다.
여기까지가 피봇에 대한 이야기의 끝이다.
추가로 연습해 보자면, PIVOT 절 PIVOT IN 절에 Alias를 줄 수 있다. 아래의 사진은 PIVOT IN 절에 별칭을 넣은 예제다.
궁금증을 더해서, 우리가 GROUP BY할 때 SELECT 절에 그룹함수를 한개만 쓰지는 않는다. 두개 이상을 기입해도된다. PIVOT 함수로 동일할지 궁금하다.
비교적 쉬운 방법으로 접근하려고 노력했다. GROUP BY 절과 PIVOT은 개념이 비슷한점이 많은 듯 싶다. 본인이 전달하려고한 내용이 잘 전달되었는지 걱정이다. 오랜시간 고민하고 작성한 PIVOT 함수에 대한 가이드 포스팅을 이로써 마무리 짓도록 한다.
출처:https://wookoa.tistory.com/240[Wookoa]
