사전캠프 : SQL 5_완강

1.데이터가 없을 때의 연산 결과 변화 케이스

[방법1] 없는 값을 제외해주기

명확하게 연산을 지정해주기 위해 null 문법을 이용

 

ex) where b.customer_id is not null 

 

[방법2] 다른 값을 대신 사용하기

다른 값으로 변경하고 싶을 때, 다음 두 개의 문법을 이용할 수 있습니다.

-다른 값이 있을 때 조건문 이용하기 : if(rating>=1, rating, 대체값)

-null 값일 때 : coalesce(age, 대체값)

 

ex) coalesce(b.age, 20) "null 제거"

 

2.Pivot table : 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것

ex)

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

 

3.Window Function

RANK

ex)

select cuisine_type,
            restaurant_name,
            order_count,
            rn "순위"
from
(
select cuisine_type,
            restaurant_name,
            rank() over (partition by cuisine_type order by order_count desc) rn,
            order_count
from
(
select cuisine_type,

            restaurant_name

            count(1) order_count
from food_orders
group by 1, 2
) a
) b
where rn<=3
order by 1, 4

 

SUM : 월요일 튜터님 확인 후 작성

 

4.날짜 포맷

ex)

select date_format(date(date), '%Y') "년",
           date_format(date(date), '%m') "월",
           date_format(date(data), 'Y%m') "년월",
           count(1) "주문건수"
from food_orders a inner join payments b on a.order_id=b.order_id
where date_format(date(date), '%m')='03'
group by 1, 2
order by 1

 

<문제>

Q : 음식 타입별, 연령별 주문건수 pivot view 만들기

A : 

select cuisine_type,

            max(if(age=10, order_count, 0)) "10대",

            max(if(age=20, order_count, 0)) "20대",

            max(if(age=30, order_count, 0)) "30대",

            max(if(age=40, order_count, 0)) "40대",

            max(if(age=50, order_count, 0)) "50대"

from

(

select a.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 age,

         count(1) order_count

from food_orders a inner join customers b on a.customer_id=b.customer_id

where age between 10 and 59

group by 1, 2

) t

group by 1