SQL 고급 : PIVOT TABLE

 

 

아래의 왼쪽 그림과 같이 어느 백화점의 지역별 점포 리스트 현황 데이터가 있다고 가정해보겠습니다. 지역별로 점포 현황을 깔끔하게 정리해보려고 하는데요. 아래 오른쪽 그림과 같이 나타내주면 조금 더 직관적으로 지역별 점포가 몇개가 있고 어디에 있는지 이해하기 쉬울 것입니다. 이렇게 세로 기반의 테이블을 가로 기반의 테이블로 변경해주는 것을 Pivot 이라고 얘기 합니다. 오늘은 SQL 쿼리를 이용하여 Pivot Table을 만드는 방법에 대해 알아보겠습니다.

 

 

지역별 점포 현황 PIVOT TABLE 만들기

 

활용할 데이터는 위에 소개했던 데이터와 동일합니다. 우선 윈도우 함수인 순위 함수의 ROW_NUMBER()를 이용해서 먼저 각 지역별로 점포수가 몇 개인지 나열해보도록 하겠습니다.

 

SELECT region, store, ROW_NUMBER() OVER(PARTITION BY region ORDER BY store) AS num
FROM store

 

🕒 실행결과

 

region	store	num
서울	강남점	1
서울	서울본점	2
서울	영등포점	3
서울	잠실점	4
서울	청량리점	5
경기	고양점	1
경기	분당점	2
경기	수원점	3
경기	중동점	4
경기	평촌점	5
대구	대구점	1
대구	상인점	2
부산	동부산점	1
부산	부산본점	2
부산	센텀시티점	3
광주	광주점	1

 

서울과 경기는 각각 5개, 부산은 3개, 대구는 2개, 광주는 1개의 점포가 있는 것을 파악할 수 있습니다. 순위 함수인 ROW_NUMBER() 에 대해서 잘 이해가 안 가신다면 아래 글을 참고 하시기 바랍니다.

 

SQL 고급 : 윈도우 함수(순위 함수) - RANK(), DENSE_RANK(), ROW_NUMBER()

 

다음으로 이제 로우에 있는 지역명을 컬럼으로 옮기는 Pivot을 해볼텐데요. 경우에 따라서 PIVOT 함수가 내장되어 있는 SQL 프로그램도 있습니다. 하지만 여기서는 PIVOT 함수가 없다고 가정하고, CASE 문을 이용해 테이블을 전환하는 작업을 해보도록 하겠습니다.

 

SELECT CASE WHEN region = '서울' THEN store ELSE NULL END AS "서울"
     , CASE WHEN region = '경기' THEN store ELSE NULL END AS "경기"
     , CASE WHEN region = '부산' THEN store ELSE NULL END AS "부산"
     , CASE WHEN region = '대구' THEN store ELSE NULL END AS "대구"
     , CASE WHEN region = '광주' THEN store ELSE NULL END AS "광주"
FROM(
     SELECT region, store, ROW_NUMBER() OVER(PARTITION BY region ORDER BY store) AS num
     FROM store
) t

 

 

🕒 실행결과

 

서울	경기	부산	대구	광주
강남점
서울본점
영등포점
잠실점
청량리점
	고양점
	분당점
	수원점
	중동점
	평촌점
		동부산점
		부산본점
		센텀시티점
			대구점
			상인점
				광주점

 

이렇게 실행하면 위와 같이 NULL 값이 포함된 테이블이 만들어지게 되는데요. 마지막으로 불필요한 NULL값만 없애준다면 앞에서 보았던 깔끔한 피벗 테이블을 만들어 줄 수 있습니다. 이를 위해 우리는 집계함수를 사용해줄건데요. 집계함수를 사용해주는 이유는 바로 집계함수가 NULL 값을 제외하고 처리 해주는 역할을 할 수 있기 때문입니다. 아래와 같이 우리가 많이 사용하는 집계함수인 COUNT() 에서도 컬럼명을 넣어 줄 경우 NULL 값을 제외하고 행의 개수를 카운트 하는 것을 알고 계실 것입니다. 

 

COUNT(*) : NULL 값을 포함한 모든 행의 개수를 카운트
COUNT(컬럼명) : NULL 값을 제외한 행의 개수를 카운트

 

여기서는 집계함수인 MAX() 를 넣어서 NULL 값을 제거해주도록 하는 쿼리를 실행해보겠습니다. (MAX() 가 아닌 다른 집계함수를 사용해도 무방합니다.)

 

SELECT MAX(CASE WHEN region = '서울' THEN store ELSE NULL END) AS "서울"
     , MAX(CASE WHEN region = '경기' THEN store ELSE NULL END) AS "경기"
     , MAX(CASE WHEN region = '부산' THEN store ELSE NULL END) AS "부산"
     , MAX(CASE WHEN region = '대구' THEN store ELSE NULL END) AS "대구"
     , MAX(CASE WHEN region = '광주' THEN store ELSE NULL END) AS "광주"
FROM(
     SELECT region, store, ROW_NUMBER() OVER(PARTITION BY region ORDER BY store) AS num
     FROM store
) t
GROUP BY t.num

 

🕒 실행결과

 

서울	경기	부산	대구	광주
강남점	고양점	동부산점	대구점	광주점
서울본점	분당점	부산본점	상인점
영등포점	수원점	센텀시티점
잠실점	중동점
청량리점	평촌점

 

실행을 해보면 위와 같이 깔끔하게 NULL 값이 제거된 형태로 PIVOT TABLE이 완성된 것을 확인할 수 있습니다.

 

 

작성된 내용 중 잘못된 부분이나 궁금한 사항이 있다면 언제든지 피드백 부탁 드립니다.

 

감사합니다.