피벗 테이블(Pivot Table)
- 많은 양의 데이터에서 필요한 자료를 뽑아 표를 작성하는것.
- 년도별 통계처럼 세로기반의 테이블을 가로 기반의 테이블로 변경한다.
아래는 주문 테이블과 결제방식 테이블을 조인하여 시간대별 주문건수를 보여주는 예시 쿼리이다.
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;오!! 뭔가 알것같으면서도 모르겠다..😂
복잡해보이지만 하나씩 보면 이해할 수 있다.
쿼리에서 서브 쿼리를 실행해보면 다음과 같이 시간대별 주문량이 나온다. 여기까지는 큰 어려움이 없다.
group by나 order by 옆 숫자는 몇번째 컬럼을 기준으로 하겠다라는 의미이다.
초록색 부분은 시간대별로 컬럼을 추가해준 것이다. 여기까지도 괜찮다.
여기서 의문점이 생겼다. 총 주문량인데 count도 sum도아니고 가장 큰값을 구하는 max는 갑자기 왜 튀어나온거지??
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
-- group by 1
order by 1 desc
group by와 max를 지우고 ordery by를 레스토랑이름순으로 정렬한 뒤 실행한 모습이다.
주문이 들어온 시간대별로 행이 하나씩 생겼고 그외 시간대는 0으로 채워졌다.
이렇게 주문이 들어오지 않은 시간은 0이기 때문에 max를 사용하면 시간대별 주문건수를 한행으로 추려낼 수 있다.
[최종 결과]
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 1 desc;
깔끔하게 나온다.
그러면 꼭 max를 사용해야하는건가??
그건 아니다. 아래는 나이대별 주문량을 구하는 과정이다.
select f.cuisine_type, c.age, count(1) as order_count
from food_orders as f inner join customers as c on f.customer_id = c.customer_id
group by f.cuisine_type, c.age
order by f.cuisine_type asc, c.age asc
서브쿼리에서는 나이별 주문량을 그룹화했고
select s.cuisine_type,
if(s.age between 10 and 20, s.order_count, 0) as '10대',
if(s.age between 20 and 30, s.order_count, 0) as '20대',
if(s.age between 30 and 40, s.order_count, 0) as '30대',
if(s.age between 40 and 50, s.order_count, 0) as '40대',
if(s.age between 50 and 60, s.order_count, 0) as '50대'
from (select f.cuisine_type, c.age, count(1) as order_count
from food_orders as f inner join customers as c on f.customer_id = c.customer_id
group by f.cuisine_type, c.age
order by f.cuisine_type asc, c.age asc) as s
where s.age between 10 and 59
그룹화 전 1~8번째 행을 보면 10~19살까지 나이별로 총 주문건수가 한행씩 들어간 것을 볼 수 있다. 10대의 총 주문건수를 알려면 모든걸 더하는 sum함수를 호출해야한다.
[최종]
select s.cuisine_type,
sum(if(s.age between 10 and 20, s.order_count, 0)) as '10대',
sum(if(s.age between 20 and 30, s.order_count, 0)) as '20대',
sum(if(s.age between 30 and 40, s.order_count, 0)) as '30대',
sum(if(s.age between 40 and 50, s.order_count, 0)) as '40대',
sum(if(s.age between 50 and 60, s.order_count, 0)) as '50대'
from (select f.cuisine_type, c.age, count(1) as order_count
from food_orders as f inner join customers as c on f.customer_id = c.customer_id
group by f.cuisine_type, c.age
order by f.cuisine_type asc, c.age asc) as s
where s.age between 10 and 59
group by 1
첫번째 행 10대를 보면 위에 값들을 모두 더한 값이 나온다.
이렇게 max는 group by와 함께 쓸 수있는 함수 중 하나일 뿐이다. 주어진 상황에 따라 얼마든지 달라질 수 있다.
