오라클(Oracle) 피봇(Pivot) 사용 방법: 그룹핑 개념으로

머리말

  오라클 함수인 Pivot 및 Unpivot 기능은 Oracle 11g 버전부터 제공하는 강력한 함수이다. 피봇 함수가 지원되기 이전에는 decode, case문을 이용해서 행과 열을 뒤집어야 했지만 피봇 함수가 지원되면서 행과 열을 간결하게 뒤집을 수 있다. 피봇 함수를 사용하기 위해서는 피봇 대상 데이터를 정재하는 과정이 필요하지만 더욱 간결한 쿼리를 작성할 수 있어서 강력한 함수다. 본 포스팅에서는 Oracle 11g Pivot 함수에 대해 일반 문법인 GROUP BY 절과 연관 지어서 자세히 소개할 예정이다.

  [+2022.02.24] 수년 전에 작성한 포스팅이 지금까지도 많은 도움이 되었다는 덧글을 받고 있다. 진심으로 기쁜 마음에 덧글 하나하나씩 감사의 뜻을 전하고 있지만 기존에 작성된 포스팅의 가독성이 많이 부족하다 판단되었다. 그렇기 때문에 새롭게 단장한 블로그 디자인에 어울리도록 포스팅을 정돈하고 오탈자도 일부 수정했다. 또한 본 포스팅의 애드센스 광고도 가독성을 덜 해치도록 최소로 적용했다. 지금까지 많은 응원에 감사한 뜻을 표하며 앞으로도 좋은 내용의 포스팅으로 보답할 예정이니 많은 응원의 메시지를 바랄 뿐이다.

Pivot 함수 사용법

  본 설명에 사용할 예시 데이터는 오라클에서 교육용으로 제공해 주는 EMP 테이블이며 자세한 데이터는 아래와 같다. 교육용 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)을 입력했다. 물론 현재 테이블에 존재하는 모든 부서 번호를 입력했다. WHERE 절은 데이터 행에 필터를 적용하는 반면, PIVOT IN 절은 컬럼 자체의 출력 여부를 결정한다는 의미에서 필터링으로 생각해도 좋다.

PIVOT 함수를 위한 데이터 정제

  지금까지 내용을 정리해 보자면 결과적으로 아래와 같은 의미를 가진 PIVOT 함수를 작성했다. 비교적 친근한 GROUP 함수의 의미로 풀어서 표현하면 쉽게 이해할 수 있다.

DEPTNO 컬럼으로 그룹핑하되

그룹핑 결과는 SUM(SAL)로 표현하며

DEPTNO 가 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 함수로 동일할지 궁금하다면 결과는 각자 확인하길 바란다.

꼬리말

  본인이 PIVOT 함수에 대해서 이해하기 위해 각 절마다 하나씩 의미를 부여해 가면서 이해한 개념을 본 포스팅에 최대한 담았다. 비교적 쉬운 방법으로 풀어서 설명하려고 노력했지만 내용이 잘 전달되었는지 걱정이다. GROUP BY 절과 PIVOT은 개념이 비슷한 점이 많은 듯싶다. 오라클 11g 버전부터 제공되는 피봇 함수에 대해서 오랜 시간 고민하고 작성한 본 포스팅은 이로써 마무리 짓도록 한다.