[SQL] 피봇에 대해 알아보자 (PIVOT, UNPIVOT)

1. PIVOT (Oracle, SQL Server Only)

피봇 연산자는 행으로 나열되어 있는 데이터를 열로 나열하여 보기 쉽게 가공하는 것이다. 시간순으로 차곡차곡 쌓이는 값이나 대규모 인원의 정보는 세로로 길어 한 눈에 알아보기 어렵다. 피봇은 세로행을 가로 열로 가독성을 향상한다.

SELECT 필드목록 FROM 테이블
PIVOT(집계함수 FOR 대상필드 IN (필드값 목록)) 별명

 

대상 필드의 값 목록을 새로운 열로 만들고 각 열에 대해 집계 함수를 호출하여 결과셋을 만든다.

/* Oracle */
SELECT * FROM tSeason
PIVOT (MAX(sale) FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

/* SQL Server */
SELECT * FROM tSeason
PIVOT (MAX(sale) FOR season IN (봄, 여름, 가을, 겨울)) pvt;

 

전과 후 비교

전

 

 

오라클은 필드 값을 따옴표 안에 적어야 하지만, SQL Server는 따옴표로 감싸지 않는다. MariaDB는 PIVOT을 아예 지원하지 않는다.

 

IN 절에 모든 필드값을 다 나열해야 하는 것은 아니다, 회전시킬 값만 나열하면 나머지는 피봇 대상에서 제외하며 순서도 마음대로 변경할 수 있다.

SELECT * FROM tSeason
PIVOT (MAX(sale) FOR season IN (여름, 봄, 가을)) pvt;

2. 피봇 집계

피봇의 FOR 절은 대상 칼럼, IN 절은 그 칼럼에서 열로 만들 값의 목록이다. 집계 함수는 회전한 후 각 칸에 쓸 값을 지정하지만 여러 값이 단일 값으로 표현되어야 하기 때문에 집계 기준이 필요하다. AVG, SUM, MIN 등을 모두 쓸 수 있다.

값이 존재하지 않으면 NULL 값을 반환한다.

SELECT * FROM tSeason2 PIVOT (MAX(sale) FOR season IN (봄, 여름, 가을, 겨울)) pvt;

SELECT * FROM tSeason2 PIVOT (SUM(sale) FOR season IN (봄, 여름, 가을, 겨울)) pvt;

 

 

COUNT도 가능하다

SELECT * FROM tSeason2 PIVOT (COUNT(sale) FOR season IN (봄, 여름, 가을, 겨울)) pvt;

3. 통계 필드 선택

아래 테이블은 도로별, 시간별, 차종별 통행량을 저장한 테이블이다.

 

car 필드에 있는 승용차, 트럭 값을 회전하고 통행량의 총합을 구해 차종별로 피봇 해보자

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR car IN (승용차, 트럭)) pvt;

 

PIVOT 연산자는 대상 테이블의 모든 칼럼 중 피봇 대상 칼럼만 빼고 GROUP BY 연산을 수행한다.

다음은 도로명이나 시간을 기준으로 피봇 해보자

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR line IN (경부, 호남)) pvt;

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN ([1], [2], [3])) pvt;
/* SQL Server는 숫자를 [] 안에 넣어야 한다. */

 

통계 기준이 여러 개인 테이블은 원하는 기준으로 통계를 자유롭게 만들 수 있다. 피봇 대상만 빼고 남은 필드를 전부 그룹핑 한다, 만일 일부를 그룹핑에서 제외하고 싶다면, 필드 목록을 지정해주면 될 것 같지만 에러가 발생한다.

 

이는 SELECT보다 PIVOT이 먼저 처리되기 때문이다. SELECT은 명령이고 PIVOT은 연산자여서 PIVOT의 우선순위가 더 높다. PIVOT 연산이 먼저 이루어지면 car, traffic 필드가 '승용차', '트럭' 필드로 바뀌기 때문에, SELECT의 명령 필드인 car, traffic은 존재하지 않게 되며 결국 출력할 수 없다.

 

이 문장에서 피봇 대상은 tTraffic 전체다다. 시간으로 그룹핑하지 않으려면 피봇 하기 전에 hour를 제외해야 하며, 그러려면 tTraffic 전체 테이블 대신 쓸 서브 쿼리가 필요하다. 피봇 대상 테이블을 인라인 뷰로 정의한 후 피봇 하고 그 결과를 출력해야 한다.

SELECT 출력대상 FROM
(
	SELECT 피봇대상 FROM tTraffic
) prepvt
PIVOT (SUM(집계할필드) FOR 피봇필드 IN (열로 만들 값)) pvt;

 

위와 같이 서브 쿼리에서 원하는 필드만 선정하여 prepvt 인라인 뷰를 정의하고, 이를 피봇 한 후 외부 쿼리에서 결과를 출력한다. 이제 인라인 뷰의 필드 목록에서 hour를 생략해보자.

SELECT * FROM
(
	SELECT line, car, traffic FROM tTraffic
) prepvt
PIVOT (SUM(traffic) FOR car IN (승용차, 트럭)) pvt;

 

이 뒤에 WHERE 절이나 ORDER BY 절이 더 올 수 있고, 다른 테이블과 조인도 가능하다.

인라인 뷰로 피봇 대상을 선택하는 대신 전체 테이블을 피봇 한 후 GROUP BY를 따로 수행할 수도 있다.

SELECT line, SUM(승용차), SUM(트럭) FROM tTraffic
PIVOT(SUM(traffic) FOR car IN (승용차, 트럭)) pvt
GROUP BY line;

 

서브 쿼리가 없어 구문이 짧고 직관적이지만, SELECT 절에서 집계 함수를 써야 하는 불편함이 있다. 두 구문은 피봇 할 때 원하는 기준으로 그룹핑하면서 집계까지 할 것인지, 아니면 일단 해 놓고 그룹핑하면서 집계를 직접 할 것인지가 다르다.

피봇 대상만 잘 지정하면 PIVOT 연산자가 그룹핑, 집계를 알아서 다 하도록 되어 있어 편리하고 한 번에 처리하니 속도도 빠르다. 외부 쿼리에서 일부 필드를 생략하면 해당 필드는 출력 대상에서 제외한다.

SELECT line, 트럭, 승용차 FROM
(
	SELECT line, hour, car, traffic FROM tTraffic
) prepvt
PIVOT(SUM(traffic) FOR car IN(승용차, 트럭))pvt;

4. 피봇의 활용

 

그냥 적재해둔 위와 같은 테이블이 존재한다. 도시별로 인구밀도를 계산하고 싶지만, 위의 테이블로는 불가능하다, 이럴 때 피봇을 활용하면 계산이 가능해진다.

 

SELECT name, ROUND(popu * 10000 / area, 2) AS 인구밀도 FROM
(
	SELECT * FROM tCityStat
	PIVOT (MAX(value) FOR attr IN ('area' AS area, 'popu' AS popu)) pvt
) A;

 

위와 같이 인라인 뷰를 통해 피봇 테이블을 부여하면, 원하는 값을 출력할 수 있다.

5. UNPIVOT

UNPIVOT은 이름과 같이 피봇의 반대 동작을 수행한다. 피봇이 값을 열로 바꾸는데 비해 언피봇은 열을 값으로 변환하여 레코드에 기록한다.

UNPIVOT (값컬럼 FOR 대상컬럼 IN (언피봇 대상 컬럼 목록))

 

위와 같은 피봇팅 된 테이블을 언피봇 해보자

SELECT * FROM tSeasonPivot
UNPIVOT (sale FOR season IN (봄, 여름, 가을, 겨울)) unpvt;

 

언피봇한 결과가 동일할 수도 있지만, 중복된 레코드가 존재한다면 값이 달라질 수 있다.

 

이미 '짬뽕', '겨울'을 기준으로 SUM 집계를 했기 때문에 다시 UNPIVOT 하더라도 원래대로 돌아가지 않는다.

UNPIVOT은 PIVOT과 동작만 반대일 뿐 완전한 반대 연산자는 아니다.

 

피봇에 대해 알아보자 (feat. PIVOT, UNPIVOT) (tistory.com)