Pivot table

[문제]

음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (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

[정리 및 새롭게 알게된 점]

먼저 음식점별 시간별 주문건수를 검색하기 위해 아래와 같은 서브쿼리를 작성하였다.

 

[서브쿼리]

(
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

서브쿼리에서는 food_orders 테이블과 payment 테이블 간 공통된 값을 가진 행들을 반환하기 위해 inner join을 사용하였으며, where 조건문을 통해 15시와 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 (
    ...서브쿼리...
) a
group by 1
order by 7 desc

메인쿼리에서는 서브쿼리의 결과를 바탕으로 각 레스토랑 별로 집계된 주문 수를 시간대별 피벗 테이블 형태로 변환하였다. 각 시간대(hh)에 해당하는 주문 수(cnt_order)를 추출하기 위해서는 max(if(hh='15', cnt_order, 0)) 와 같은 구문을 사용하였다. 

if함수는 각 시간에 따른 주문건 수를 반환하는 역할을 하였고, max 함수는 각 레스토랑별로 시간대별 최대 주문 수를 하나의 행으로 집계하여 피벗 테이블 형태로 변환하는 역할을 하였다.