본문 바로가기

Python

SQL 4주차 (완료)

아 오늘 파이썬 과제 하느라 에너지 다 쓴거 같다

 

너무 힘드넹

 

내일 마저 수강 해야겠다.

 

select price/quantity
from
(select price, quantity
from food_orders fo )




select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
from food_orders
) a
order by over_time desc


select restaurant_name,
       price_per_plate*ratio_of_add "수수료"
from 
(
select restaurant_name,
       case when price_per_plate<5000 then 0.005
            when price_per_plate between 5000 and 19999 then 0.01
            when price_per_plate between 20000 and 29999 then 0.02
            else 0.03 end ratio_of_add,
       price_per_plate
from 
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a
) b




#음식점의 지역과 평균 배달시간으로 segmentation 하기





SELECT restaurant_name,
   sido,
   avg_delivery_time,
   case when avg_delivery_time<=20 then '<=20'
        when avg_delivery_time>20 and avg_delivery_time<=30 then '20<x<=30'
   else '>30' end delivery_time_segment
from
(
SELECT restaurant_name,
   substr(addr, 1, 2) sido,
   avg(delivery_time) avg_delivery_time
from food_orders fo 
group by 1,2 
) a

#[실습1] 한국음식의 주문별 결제 수단과 수수료율을 조회하기
#(조회컬럼: 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
#*결제 정보가 없는 경우도 포함하여 조회
# payments: order_id pay_type vat date time
# food_order: order_id customer_id restauramt_name cuisine_type



SELECT  f.order_id,
f.restaurant_name,
f.price,
p.pay_type,
p.vat
from food_orders f left join payments p on f.order_id=p.order_id 
where f.cuisine_type = 'Korean'


#[실습2] 고객의 주문 식당 조회하기
#(조회컬럼: 고객 이름, 연령, 성별, 주문식당)
#*고객명으로 정렬, 중복 없도록 조회
#customers: customer_id / name / age / gender 
#food_orders: customer_id / restautant_name

SELECT DISTINCT c.name,
   c.age,
   c.gender,
   f.restaurant_name 
from customers c left join food_orders f on c.customer_id=f.customer_id 
order by 1


#[실습3] 주문가격과 수수료율을 곱하여 주문별 수수료 구하기
#(조회컬럼: 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
#*수수료율이 있는 경우만 조회
#food_order : order_id / customer_id / restaurant_neame / price / 
#payments : order_id / vat

SELECT f.order_id,
   f.restaurant_name,
   f.price,
   p.vat,
   f.price*p.vat "수수료"
from food_orders f left join payments p on f.order_id = p.order_id 
where p.vat is not null
group by f.order_id


선생님이 작성한 것
SELECT f.order_id,
   f.restaurant_name,
   f.price,
   p.vat,
   f.price*p.vat "수수료"
from food_orders f inner join payments p on f.order_id = p.order_id 


#[실습4] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격합을 구하기
#(조회컬럼: 음식타입, 원래 가격, 할인 적용 가격, 할인 가격)
#할인: 나이-50*0.005
#고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬

#customers : customer_id / age
#food_order: cutomer_id / cuisine_type / price 

SELECT c.customer_id,
       f.cuisine_type,
   f.price,
   c.age,
   f.price-(c.age-50)*0.005 '할인 적용 가격',
   (c.age-50)*0.005 '할인 가격'
from customers c left join food_orders f on c.customer_id=f.customer_id 
where c.age >= 50
order by 6 desc


선생님이 작성한 것  - 난 합을 구한게 아니라 그냥 한거네
SELECT cuisine_type,
       sum(price) 'price',
       sum(price*discount_rate) 'discounted_price'
from
(
SELECT f.cuisine_type,
   f.price,
   c.age,
   (c.age-50)*0.005 'discount_rate'
   from food_orders f left join customers c on f.customer_id=c.customer_id 
where c.age >= 50
) a
group by 1
order by 3 desc



 





아직 해결 못함



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

#food_orders : cuisine_type / order_id / customer_id / quantity 
#customers : customer_id / gender / age
SELECT case when f.cuisine_type='Korean' and NAE='10대' co


SELECT f.order_id,
       c.customer_id,
   f.cuisine_type,
   case when c.age<=19 then '10대'
    when c.age<=29 then '20대'
    when c.age<=39 then '30대'
    when c.age<=49 then '40대'
    when c.age<=59 then '50대'
    else '기타'
       end as NAE,
       count(f.order_id) '주문건수'
from food_orders f left join customers c on f.customer_id =c.customer_id  
group by 3
order by 4


오늘 들어가보니까 숙제가 바꼈는데 아 왜케 어렵니.

1. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

  • 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
  • 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
  • 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬


오답

SELECT a.restaurant_name,

avg(a.age),

case when avg(a.age) between 0 and 29 then '20대'

when avg(a.age) between 29 and 39 then '30대'

WHEN avg(a.age) between 39 and 49 then '40대'

else '50대 이상'

end as NAE,

a.가격대

from

(

SELECT f.restaurant_name,

c.age,

avg(f.price),

case when avg(f.price) between 0 and 5000 then '5000까지'

when avg(f.price) between 5000 and 10000 then '5000~10000'

when avg(f.price) between 10000 and 30000 then '10000~30000'

else '30000 이상'

end as '가격대'

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

) a

group by 1

 

결과가 안나온다 다시 해보자

 

 

 

case when 에서는 else 및 end 를 해야 합니다.

 

SELECT restaurant_name,

case when avg_price >0 and avg_price <=5000 then 'price_group1'

when avg_price >5000 and avg_price <=10000 then 'price_group2'

when avg_price >10000 and avg_price <=30000 then 'price_group3'

when avg_price >30000 then 'price_group4'

end '평균가격',

case when avg_age between 0 and 29 then 'age_20대'

when avg_age between 30 and 39 then 'age_30대'

when avg_age between 40 and 49 then 'age_40대'

else 'age_50대이상'

end '평균연령'

from

(

SELECT restaurant_name,

avg(price) 'avg_price',

avg(age) 'avg_age'

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

group by restaurant_name

order by restaurant_name

) a