[SQL] 피봇 테이블(PIVOT TABLE)

<엑셀보다 쉽고 빠른 SQL 5주차 강의 참고>

[PIVOT TABLE] 

2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열한 테이블을 말한다.

2개의 기준 중 1개의 기준(rows)을 여러 열(columns)로 변환(pivot)시켜 테이블을 만드는 작업이다.

<"MySQL" 기준>

다음과 같은 예시를 보면 ) 각 음식점 마다 시간대별 주문 건수를 알고 싶다고 한다.

SELECT A.restaurant_name ,
	   SUBSTRING(B.time, 1, 2) hh,
	   COUNT(1) cnt_order
       -- *를 쓰면 NULL값도 포함해서 계산할 수 있기에 주의
FROM food_orders A JOIN payments B ON A.order_id = B.order_id
WHERE SUBSTRING(B.time, 1, 2) BETWEEN 15 and 20
GROUP BY 1, 2

값의 일부

현재 음식점 이름과 시간, 2개의 기준으로 집계하고 있다. 이를 좀 더 보기 편하게 바꿔주는 것이 PIVOT TABLE이다.

SELECT C.restaurant_name,
	   max(if(hh='15', cnt_order, 0)) "15",
	   max(if(hh='16', cnt_order, 0)) "16",
	   max(if(hh='17', cnt_order, 0)) "17",
	   max(if(hh='18', cnt_order, 0)) "18",
	   max(if(hh='19', cnt_order, 0)) "19",
	   max(if(hh='20', cnt_order, 0)) "20"
-- 참고 : MAX를 사용한 이유는 집계함수를 사용했기에 쓴 것.
--       MIN을 써도 상관없다. 값은 1개밖에 없기 때문. 혹시나 값이 여러개라면 SUM을 사용.
FROM (SELECT A.restaurant_name ,
  		   SUBSTRING(B.time, 1, 2) hh,
	 	   COUNT(1) cnt_order 
	  FROM food_orders A JOIN payments B ON A.order_id = B.order_id
	  WHERE SUBSTRING(B.time, 1, 2) BETWEEN 15 and 20
	  GROUP BY 1, 2) C 
GROUP BY 1
ORDER BY 7 DESC

훨씬 깔금해진 것을 볼 수 있다.

 

<'ORACLE' 기준>

- 10g 버전 이하에서는 decode를 활용해서 위처럼 하면 된다.

- 11c 버전부터 pivot 함수를 통해 간편하게 만들 수 있다고 한다.

문법은 다음과 같지만 예시는 나중에 직접 활용할 때 쓰겠습니다 ㅎㅎ,.

SELECT 출력할 컬럼
FROM [PIVOT 대상 테이블]
PIVOT (
		[집계함수]
        FOR [가로로 출력할 열] IN ([조건 대상 값])
        )
ORDER BY DEPTNO;