데이터 분석 공부 일지 4일차 250424

지난 시간 배웠던 내용

 

문자변경

1. replace

2. substr

3. concat

조건

1. if

2. case 

 

4-2 서브쿼리문

 

# 실습 1 음식 준비시간이 25분보다 초과한 시간을 가져오기

# select order_id, restaurant_name, food_preparation_time-25 over_time
# from food_orders 
# 먼저 준비시간에서 -25를 하면 초과되는 시간을 알 수 있으니 먼저 쿼리를 작성해
# 서브쿼리로 만들어준다.

select order_id, restaurant_name, if(over_time>=0, over_time, 0) over_time
# 고객번호 , 식당이름, 만약 오버타임이 0 이상이면 오버타임 출력, 아니면 0 출력 을 오버타임이란 컬럼으로 만들겠다.
from 
(
select order_id, restaurant_name, food_preparation_time-25 over_time
# 여기서부터 서브쿼리문 고객번호,식당이름, 음식준비시간-25을 오버타임으로 별칭으로 변경
from food_orders
# 이 쿼리문은 음식정보테이블에서 가져오고 a라는 별칭으로 변경
) a

# 음식 준비시간이 25분을 초과하는 연산을 먼저하기위해 
# 서브쿼리로 연산이 되어있는 테이블을 만들어 사용해 쿼리를 보다 간단하게 만든다.

 

4-3 실습

 

* 꿀팁

= 서브쿼리문 작성 시 보고싶은 쿼리만 선택해서 실행시키면 그 쿼리의 결과만 볼 수 있다.

 

[실습] User Segmentation 와 조건별 수수료를 Subquery 로 결합해보기

 

- 1) [실습] 음식점의 평균 단가별 segmentation 을 진행하고, 그룹에 따라 수수료 연산하기
    
    (수수료 구간 - 
   
      ~5000원 미만 0.5%
    
      ~20000원 미만 1%
    
      ~30000원 미만 2%
    
      30000원 초과 3%)
      
# 음식점들의 수수료를 구하니 restaurant_name
# 그리고 평균단가 * 수수료 비율을 하면 수수료

select restaurant_name, price_per_plate * ratio_of_add
FROM 
(
# 2. 두 번째 서브쿼리 평균단가를 구했으니 구간별 수수료 적용 아래 쿼리 작성
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 39999 then 0.02
	when price_per_plate > 30000 then 0.03
	end ratio_of_add,
	price_per_plate
from
(
# 1. 첫 번째 서브쿼리 평균단가를 구해야 구간별 수수료를 할 수 있으니 아래 쿼리 작성
SELECT restaurant_name, avg(price/quantity) price_per_plate
FROM food_orders
GROUP BY restaurant_name
) a
) b

 

# case를 활용해 그룹을 나눠줌. 
# 풀다보니 between, else를 썼는데 결과 값은 강의 쿼리랑 다르지 않고 가독성도 더 좋은 것 같아서 그냥 둠

SELECT restaurant_name , a.시도, # 한글로 시도 써봄 서브쿼리를 a라고 해서 a. 해줘야함
case
	when avg_time <= 20 then '<= 20'
	when avg_time between 21 and 29 then'20<x<30'
	else '>30' end time_segment 
from
(

# 평균 배달시간을 알아야 그룹화 할 수 있으니 아래 서브쿼리 작성

select restaurant_name , substr(addr,1,2) as "시도", AVG(delivery_time) avg_time
from food_orders
group by 1,2
) a

 

4-4 [실습] 복잡한 연산을 Subquery 로 수행하기

- [실습] 음식 타입별 ~~지역별~~ 총 주문수량과 음식점 수를 연산하고, 
               주문수량과 음식점수 별 수수료율을 산정하기
    
    (음식점수 5개 이상, 주문수 30개 이상 → 수수료 0.5%
     음식점수 5개 이상, 주문수 30개 미만 → 수수료 0.8%
     음식점수 5개 미만, 주문수 30개 이상 → 수수료 1%
     음식점수 5개 미만, 주문수 30개 미만 → 수수로 2%)

select
a.시도,
cuisine_type,
total_quantity,
count_of_res,
# 서브쿼리에서 구한 값으로 조건에 맞게 수수료율 산정
CASE
	when count_of_res >= 5 and total_quantity >= 30 then 0.005
	when count_of_res >= 5 and total_quantity < 30 then 0.008
	when count_of_res < 5 and total_quantity >= 30 then 0.01
	when count_of_res < 5 and total_quantity < 30 then 0.02
END rate
from
(

# 총 주문수량, 음식점 수 계산을 위한 서브쿼리 먼저
select SUBSTR(addr,1,2) "시도",cuisine_type , sum(quantity) total_quantity, count(distinct restaurant_name) count_of_res
from food_orders
group by 시도, cuisine_type
) a

# 지역별로 묶기 위해서는 지역+타입별로 그룹화 필요
# 서브쿼리에서 지역을 추가해서 지역+타입을 하나로 묶으면 전체 지역+타입별로 나옴
# 이렇게 묶으면 예를 들어 경기 French의 수수료와 서울 French의 수수료가 달라짐 중요함

 

/*- 2) [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
    
    (할인조건
      수량이 5개 이하 → 10%
      수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
       이 외에는 일괄 1%)*/

# 서브쿼리를 이용해 할인조건에 맞는 할인율 구하기

select res_name,
case
	when total_quantity <= 5 then 0.1
	when total_quantity > 15 and total_price > 300000 then 0.005
	else 0.01
end "할인율"
from
(

# 총 주문수량, 주문금액을 구하는 서브쿼리 먼저
select 
restaurant_name as res_name,
sum(quantity) as total_quantity,
sum(price) as total_price
from food_orders
group by restaurant_name 
) a

# 처음에 sum(price)가 아닌 price * sum(quantity)로 했는데
# 그래도 결과는 같게 나오지만 다른 문제에서는 가격이 다르다면 값이 틀림
# 그래서 총 주문금액 구할 때 sum(price)를 해줘야함
# 추가로 case문에 0.1 대신 '10%'이렇게 적었는데 SQL에서는 나중에 연산을 할 수 있으니
# 0.1로 적는게 맞음

 

4-5 join

 

join이란 ?

join 원리

join 실습

 

4-6 join 실습

- 1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
    
    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
    *결제 정보가 없는 경우도 포함하여 조회
    =(정보가 없는 null값을 포함하는건 left join)

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

 

- 2) [실습] 고객의 주문 식당 조회하기
    
    (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) 
    *고객명으로 정렬, 중복 없도록 조회
	= 정렬 -> order by, 중복x -> distinct(select절 전용 기능이다.)
select distinct c.name , c.age , c.gender, fo.restaurant_name
from customers c join food_orders fo on c.customer_id = fo.customer_id
ORDER BY c.name

 

4-7 [실습] JOIN으로 두 테이블의 값을 연산하기

 

- 1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
    
    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
    *수수료율이 있는 경우만 조회
    
select 
fo.order_id, 
fo.restaurant_name, 
fo.price, p.vat,
fo.price*p.vat "수수료율"
from food_orders fo join payments p on fo.order_id = p.order_id

 

- 2) [실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
    (**먼저 연산해야하는 부분이 있다면 서브쿼리 활용**)
    
    (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격)
    *할인 : (나이-50)*0.005
    * 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
    
    
    select cuisine_type "음식 타입", sum(price) "원래 가격", sum(price * discount_rate)"할인 적용 가격"
FROM 
(
select fo.cuisine_type ,c.age, fo.price, (c.age-50)*0.005 as discount_rate
from customers c join food_orders fo on c.customer_id = fo.customer_id
where c.age >= 50
) a
group by cuisine_type
order by sum(price -(price * discount_rate)) desc

 

4주차 숙제

 

1. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
평균 음식 주문 금액 기준 : 5,000 이하 / ~10,000 / ~30,000 / 30,000 초과
평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
두 테이블 모두에 데이터가 있는 경우만 조회(*** inner join 쓰라는 말 ***),
식당 이름 순으로 오름차순 정렬 (*** order by ***)
SQL 기본구조 적어보기
각 테이블에서 필요한 컬럼 정리하기
Join 문으로 데이터 조회문 적어보기
데이터를 연산해보기
연산한 데이터를 segmentation 에 활용하기 -> 그룹화 -> case

# 실수로 case문에 then price_group1라고 적어서 실행 안됌.
# then 'price_group1'인데 다 풀어놓고 작은 따옴표 실수..

select restaurant_name,
       case when price <=5000 then 'price_group1'
            when price >5000 and price <=10000 then 'price_group2'
            when price >10000 and price <=30000 then 'price_group3'
            when price >30000 then 'price_group4' end price_group,
       case when age <30 then 'age_group1'
            when age between 30 and 39 then 'age_group2'
            when age between 40 and 49 then 'age_group3'
            else 'age_group4' end age_group
from
(
select a.restaurant_name,
       avg(price) price,
       avg(age) age
from food_orders a inner join customers b on a.customer_id=b.customer_id
group by 1
) t
order by 1

 

* 오늘 자주 했던 실수 *

- 서브쿼리를 사용하면 from에 새롭게 내가 만든 테이블이 온다는 느낌

- 그래서 밖에 select문에서 서브쿼리에 없는 컬럼을 선택하면 안됌. 테이블에 없는 컬럼을 선택했기때문