SQL을 사용해 Pivot table 만들기

피벗테이블을 만드는 방법을 정리해보려고 한다.

먼저, 피벗테이블은 엑셀을 사용하면서 많이 들어보고 사용했지만, 설명할 수 있을 정도로 알진 못했다. 테이블이 아예 다른 형태로 전환되다 보니, 구성이 어렵고 로직이 헷갈려 정리해본다.

백과사전에 정의된 피벗 테이블은 다음과 같다.

피벗 테이블(pivot table)은 커다란 표(예: 데이터베이스, 스프레드시트, 비즈니스 인텔리전스 프로그램 등)의 데이터를 요약하는 통계표이다. 이 요약에는 합계, 평균, 기타 통계가 포함될 수 있으며 피벗 테이블이 이들을 함께 의미있는 방식으로 묶어준다.
피벗 테이블은 데이터 처리의 한 기법이다. 유용한 정보에 집중할 수 있도록 하기 위해 통계를 정렬 또는 재정렬(피벗)한다. - 위키백과

내가 이해한 것으로 요약하자면 피벗 테이블(Pivot table)은

데이터베이스같은 형식의 데이터를 사용자가 원하는 방식으로 요약하고 재정렬한 표(테이블)이다.
이 요약은 종방향 / 행방향에 따라 다른 컬럼을 나타내더라. 따라서, 보통 요약하는 기준이 2개 이상인 경우부터 사용하게 되는 것 같다.

다음은 음식점별, 시간별 두 가지 기준으로 나눈 주문건수 피벗테이블 쿼리문이다.
(15시~20시 사이, 20시 주문건수 기준 내림차순)

select 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"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

피벗테이블에 필요한 몸체인 from 안의 서브쿼리문을 먼저 살펴보면서 이전 SQL 기본기도 복습해본다.

select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2

food_orders, payments 두 테이블을 합친(join) 새로운 뷰로부터 -- from
식당이름(restaurant_name), 시간(hh, time에서 시간 부분만 substring함), 주문건수(cnt_order) 세 컬럼을 표시하기 -- select
조건은 시간이 15 ~ 20시인 데이터만 -- where
식당이름(컬럼1)별 같은 시간값(컬럼2)을 그룹화하고 그룹당 주문건수 표시하기 -- group by

서브쿼리문을 통해 식당이름별+시간별 그룹화하였다. cnt_order는 그 그룹에 해당하는 count, 즉 주문건수다. 쉽게 말하자면, 첫 행의 의미는 "Anjappar Chettinad에서 17시에는 주문건수가 1번".

만약 같은 식당 Anjappar Chettinad에서 다른 시간에 먹은 사람 데이터가 저장되어 있다면, 다른 행에 포함된다. (일단 1행은 아님, order by 되어있지 않으니 위 사진에는 없음)

이제 위 뷰를 가지고
음식점별, 시간별 두 가지 기준으로 나눈 주문건수를 구하는 피벗 테이블을 구하면 된다.
(15시~20시 사이, 20시 주문건수 기준 내림차순)

select 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"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

이제 처리해야할 데이터의 베이스는 from절에 서브쿼리문으로 둔다.
복잡하게 생각할 필요없이 아까 만든 뷰 테이블이 있다고 생각하고 새 쿼리문을 작성한다고 생각하면 편하다.

SELECT문에서 MAX(IF(조건, 참, 0))를 활용하는 것이 포인트이다.
이건 마치 엑셀에서 배열수식과 비슷한, 아니 어쩌면 같은 원리인 것 같다..

max(if~)를 사용해야 하는 이유를 말로 설명하기 뭔가 복잡한데..
일단 MAX 역할 비교를 해야할 듯 하다.

다음은 식당이름 오름차순으로 정렬한 사진이다.

다음과 같이 MAX 함수만 제외한 다른 쿼리문을 실행했을때 (※맨 아래 order by 위에 group by를 지워야 한다.)

select restaurant_name,
       if(hh='15', cnt_order, 0) "15",
       if(hh='16', cnt_order, 0) "16",
       if(hh='17', cnt_order, 0) "17",
       if(hh='18', cnt_order, 0) "18",
       if(hh='19', cnt_order, 0) "19",
       if(hh='20', cnt_order, 0) "20"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
order by restaurant_name

뭔가 보이는 것 같다.
if문만 사용했을 때는 그저 시간이 15면 cnt_order를 표시하고, 아니면 0을 표시한다.

       if(hh='15', cnt_order, 0) "15",

이것을 반복해서 총 여섯 컬럼을 나타낸다. (같은 방식)

이런 방법이라면 식당이름 컬럼을 제외한, 나머지 여섯 컬럼에는 반드시 하나를 제외하고 모두 0값을 가진다. 왜냐면 먼저 서브쿼리문에서 구한 데이터가 이미 식당별+시간별 분류가 되어있어서, 한 식당에서 15시에 먹은 주문건수들과 16시에 먹은 주문건수가 한 행에 나타날리 없다.

       max(if(hh='15', cnt_order, 0)) "15",

그래서 max를 입히고, 뒤에 group by 절로 식당별로 묶어주면 (max 함수는 그룹 내에서 maximum이기 때문에 그룹화를 해야 의미가 있다.)
하나의 값을 제외하고 모두 0이기 때문에 (물론 모두 0일 수도 있다.) 원하는 피벗 테이블이 탄생된다.

요약

피벗 테이블은 RDB같은 테이블형태의 데이터를 다른 형태로 요약해준다.

가장 왼쪽 열(컬럼)을 기준으로, 나머지 열(컬럼)들에 대해 해당하는 값들을 보여주는 형태가 보편적이다. (종/횡으로 나뉘어진 2개의 기준표랄까?)

<코드 예시>

select 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"
from 
(
select a.restaurant_name,
       substring(b.time, 1, 2) hh,
       count(1) cnt_order
from food_orders a inner join payments b on a.order_id=b.order_id
where substring(b.time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1

SELECT 문에서 첫 컬럼을 기준으로 두기 위해 표시 -- 음식점 이름별로 구분 (GROUP BY 할 것)
나머지 컬럼은 MAX(IF(조건, 참, 0))를 사용한다. 반드시 거짓값이 0이어야 한다.
마지막에 꼭 GROUP BY 추가하기