SQL 5주차②(피벗테이블)

SQL로 Pivot Table 만들어보기

[실습1] 음식점별 시간별 주문건수 Pivot Table 뷰 만들기 (15~20시 사이, 20시 주문건수 기준 내림차순)

풀이 (총 2단계)

1. 음식별 시간별 주문건수 집계

1-1) 음식은 food_orders, 시간은 payments에 있으므로 공통 컬럼인 order_id를 통해 join 필요

         from food_orders f join payments p on f.order_id=p.order_id

1-2) select에 "00:00:00" 꼴인 time의 값 앞 두 글자만 추출하는 new_time 만들고, 괄호의 조건을 where로 만들기

         substr(time,1,2) new_time

         ...

        where substr(time,1,2) between 15 and 20

1-3) 음식점별 시간별 주문건수를 요구했으므로, select와 group by 써주기

        주문건수 count(1)로 구하기

select restaurant_name,
       substr(time,1,2) new_time,
       count(1) cnt_order
from food_orders f join payments p on f.order_id=p.order_id
where substr(time,1,2) between 15 and 20
group by 1,2

2. Pivot view 구조 만들기

2-1) 앞에 만든 '1.읍식별 시간별 주문건수 집계'는 SUBQUERY a로 명함

2-2) 각각 XX시일 때, cnt_order(주문건수 집계)를 가져와줘

        주의. Pivot View를 만들 때는 MAX를 입력해야 한다

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

       max(if(new_time='16', cnt_order, 0)) "16",

       max(if(new_time='17', cnt_order, 0)) "17",

       max(if(new_time='18', cnt_order, 0)) "18",

       max(if(new_time='19', cnt_order, 0)) "19",

       max(if(new_time='20', cnt_order, 0)) "20"

2-3) MAX와 같이 함수가 들어갈 때 꼭 넣어줘야 하는 GROUP BY 추가하기 (쉼표도 추가)

2-4) 20시 주문건수 기준 내림차순 = 7번째 컬럼을 기준으로 내림차순 = order by 7 desc

select restaurant_name,
       max(if(new_time='15', cnt_order, 0)) "15",  # 쉼표 추가!
       max(if(new_time='16', cnt_order, 0)) "16",  # 쉼표 추가!
       max(if(new_time='17', cnt_order, 0)) "17",  # 쉼표 추가!
       max(if(new_time='18', cnt_order, 0)) "18",  # 쉼표 추가!
       max(if(new_time='19', cnt_order, 0)) "19",  # 쉼표 추가!
       max(if(new_time='20', cnt_order, 0)) "20"   
from 
(
select restaurant_name,
       substring(time, 1, 2) new_time,
       count(1) cnt_order
from food_orders f inner join payments p on f.order_id=p.order_id
where substring(time, 1, 2) between 15 and 20
group by 1, 2
) a
group by 1
order by 7 desc

 

[실습2] 성별, 연령별 주문건수 Pivot Table 뷰 만들기 (나이는 10~59세 사이, 연령 순으로 내림차순)

풀이 (총 2단계)

1. 성별, 연령별 주문건수 집계

1-1) 주문은 food_orders, 성별과 연령은 customers에 있으므로 공통 컬럼인 customer_id를 통해 join 필요

         from food_orders f join payments p on f.order_id=p.order_id

1-2) 나이 보기 편하게 바꾸고 10~59세 사이 값만 추출, 성별/연령별로 그룹화

        주의 when age between 10 and 19 then 10 을 when age>=10 and age<=19 then 10로 하면 에러 뜸

        주문건수 count(1)로 구하기

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)
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

2. Pivot view 구조 만들기

2-1) 앞에 만든 '1.성별 연령별 주문건수 집계'는 SUBQUERY a로 명함

2-2) MAX와 같이 함수가 들어갈 때 꼭 넣어줘야 하는 GROUP BY 추가하기  (쉼표도 추가)

2-3) 나이순으로 내림차순 = order by 1 desc

select age,
       max(if(gender='male', cnt, 0)) male,             # 쉼표 추가!
       max(if(gender='female', cnt, 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
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                                    # 맨 위 select에 함수식 max 써줬으므로 필수
order by 1 desc

 

 

[숙제] 음식 타입별, 연령별 주문건수 pivot view 만들기(연령은 10~59세 사이)

결과

 

풀이

1. 음식타입, 연령별 주문건수 집계

1-1) 음식타입이 있는 food_orders와 연령이 있는 customers를 공통 컬럼 customer_id로 join

        from food_orders f inner join customers c on f.customer_id=c.customer_id

1-2) SELECT에 필요한 항목 추출 및 주문건수 구하기

select cuisine_type,
       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 new_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

2. Pivot view 구조 만들기

2-1) 앞에 만든 '1.음식타입, 연령별 주문건수 집계 '는 SUBQUERY a로 명함

2-2) 중요1. SELECT 첫번째 컬럼(cuisine_type)을 pivot table의 첫 세로축으로 설정하면 나중에 group by 1하면 되므로 편함

        중요2. 가로로 길게 만들고 싶은 부분은 MAX로 하면 됨

        중요3. MAX의 경우, 쉼표 꼭 넣어줘야 함

        중요4. (중요1과 연결됨) group by 꼭 해줘야 함

select cuisine_type,
       max(if(new_age='10대', cnt_order, 0)) '10대',
       max(if(new_age='20대', cnt_order, 0)) '20대',
       max(if(new_age='30대', cnt_order, 0)) '30대',
       max(if(new_age='40대', cnt_order, 0)) '40대',
       max(if(new_age='50대', cnt_order, 0)) '50대'
from
(
select cuisine_type,
       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 new_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