SQL 5주차 강의 내용

SELECT restaurant_name,
   avg(rating) avg_rating,
   avg(if(rating<>'Not given', rating, null)) avg_rating2
from food_orders f
group by restaurant_name


값을 제외
select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.customer_id is not null 


값의 변경
select a.order_id,
       a.customer_id,
       a.restaurant_name,
       a.price,
       b.name,
       b.age,
       coalesce(b.age, 20) "null 제거",
       b.gender
from food_orders a left join customers b on a.customer_id=b.customer_id
where b.age is null


값의 변경 (조건을 통해)
SELECT name,
   age,
   case when age < 15 then 15
        when age >= 80 then 80
        else age
        end re_age
from customers c 


피벗테이블
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




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



윈도우함수 - rank over (partition by        order by  )


SELECT cuisine_type,
   restaurant_name,
   cnt_order,
   ranking
FROM 
(
SELECT cuisine_type,
       restaurant_name,
       cnt_order,
       rank() over (partition by cuisine_type order by cnt_order desc) ranking
FROM 
(
SELECT cuisine_type,
       restaurant_name,
       count(1) cnt_order
from food_orders f
group by 1,2
) a
) b
where ranking<=3


윈도우함수 누적 합

SELECT cuisine_type,
   restaurant_name,
   cnt_order,
   sum(cnt_order) over (partition by cuisine_type) sum_cuisine,
   sum(cnt_order) over (partition by cuisine_type order by cnt_order) cum_cuisine
from
(
SELECT cuisine_type,
       restaurant_name,
       count(1) cnt_order
from food_orders f
group by 1,2
) a
order by cuisine_type, cnt_order



날짜!!!
select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일"
from payments



select date(date) date_type,
       date_format(date(date), '%Y') "년",
       date_format(date(date), '%m') "월",
       date_format(date(date), '%d') "일",
       date_format(date(date), '%w') "요일" # 0은 일요일 1은 월요일
from payments



SELECT date_format(date(date), '%Y') '년',     #Y xxxx 4자리  y xx 두자리
   date_format(date(date), '%m') '월',     #M 영어 m 숫자
   date_format(date(date), '%y%m') '연월',
   count(1) '주문건수'
from food_orders f inner join payments p on f.order_id =p.order_id 
where date_format(date(date), '%m')='03'
group by 1,2,3
order by 1