오늘은 조회한 데이터가 상식적이지 않을 때, 그리고 SQL을 이용하여 피벗테이블 만드는 법을 공부했다.
고객 나이가 너무 어리거나 많을 때는 case문을 활용해 임의의 값으로 대체해줄 수 있다.
SQL로 피넛테이블 만들기
먼저 음식점별로 시간대별 주문 수를 확인하는 베이스 데이터를 생성한다.
select f.restaurant_name,
substr(p.time, 1, 2) hh,
count(1) cnt_order
from food_orders f inner join payments p on f.order_id=p.order_id
where substr(p.time, 1, 2) between 15 and 20
#주문 시간 중 시만 필요하기 때문에 substr을 이용해서 자르고, 15에서 20시 사이에 발생한 주문만 출력
group by 1, 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
order by 7 desc 피벗테이블을 만들 때는 기준이 되는 행(여기서는 식당 이름)을 제일 먼저 select에 써주고 그 뒤는 max를 이용하여 count명령문을 입력한다.
10대부터 50대 사이의 고객 정보를 나이대별로 묶고 성별과 나이대의 고객 수를 count한다.
select gender,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) cnt_order
from food_orders f inner join customers c on f.customer_id=c.customer_id
where age between 10 and 59
group by 1, 2 
select age,
max(if(gender='male', cnt_order, 0)) "male",
max(if(gender='female', cnt_order, 0)) "female"
from
(
select gender,
case when age between 10 and 19 then 10
when age between 20 and 29 then 20
when age between 30 and 39 then 30
when age between 40 and 49 then 40
when age between 50 and 59 then 50 end age,
count(1) cnt_order
from food_orders f inner join customers c on f.customer_id=c.customer_id
where age between 10 and 59
group by 1, 2
) a
group by 1
order by 1 desc 나이대별로 여성과 남성의 고객 수를 count하는 피벗테이블을 완성했다.
