SQL subquery, join

1. 포맷 변경과 조건문 복습
1) 문자변경과 조건문
- 문자 변경
1. Replace : 지정한 문자를 다른 문자로 변경
2. Substring : 특정 문자만 추출
3. Concat : 여러 문자를 합하여 포맷팅
- 조건문
1. If : if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
2. Case when end :
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end
2) 이번 수업에서 배울 내용 맛보기
    1. 연산을 여러번 해야하는데, 쿼리문에 길게 쓰는 것밖에는 방법이 없을까?
    2. 연산한 결과를 다른 연산이나 조건문에 사용하고 싶은데, 계속 반복해서 적어줘야할까? 
    3. 필요한 데이터가 여러 테이블에 나누어져 있는데 한 번에 조회해서 사용할 수는 없을까?
    
2. 여러 번의 연산을 한 번의 SQL 문으로 수행하기 (Subquery)
- 연산이 한 번에 끝나면 참 좋은데 아닐 때가 많죠.
문자 변경한 다음에 숫자 연산 하고 그 결과로 또 연산을 해야할 때, 긴긴 쿼리문 보다는 조금
더 효율적이고 알아보기 쉽게 사용해봅시다.
1) Subquery가 필요한 경우
- 여러번의 연산을 수행해야 할 때
-> 수수료를 부과할 수 있는 시간을 구하고
-> 구해진 시간에 주문 금액별로 가중치를 주고
-> 가중치를 적용한 결과로 최종 예상 배달비를 계산할 때
- 조건문에 연산 결과를 사용해야 할 때
-> 음식 타입별 평균 음식 주문금액 따라 음식비 상/중/하 를 나누고 싶을 때
- 조건에 Query 결과를 사용하고 싶을 때
-> 30대 이상이 주문한 결과만 조회하고 싶을 때
2) Subquery 문의 기본 구조
- Sub 라는 명칭에서 알 수 있듯이, Query 안에 sub 로 들어간 구문이라고 생각해주시면 됩니다.
select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) a

select column1, column2
from table1
where column1 = (select col1 from table2)

3) [실습] Subquery 문을 이용하여 연산문 적어보기
- 주문 테이블에서 주문 번호, 음식점명, 음식 준비시간을 가져오기
-> select 기본문
-> 가져올 컬럼 적기
-> subquery 문으로 추가
select order_id, restaurant_name, food_preparation_time
from 
(
select order_id, restaurant_name, food_preparation_time
from food_orders
) a

- Subquery 문 안을 수정해서, 음식 주문시간이 25분보다 초과한 시간을 가져오기
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

3. [실습] User Segmentation 와 조건별 수수료를 Subquery로 결합해보기
1)[실습] 음식점의 평균 단가별 segmentation을 진행하고, 그룹에 따라 수수료 연산하기
(수수료 구간 -
~5000원 미만 0.05%
~20000원 미만 1%
~30000원 미만 2%
30000원 초가 3%)
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

2)[실습] 음식점의 지역과 평균 배달시간으로 segmentation 하기
select restaurant_name,
       sido,
       case when avg_time<=20 then '<=20'
            when avg_time>20 and avg_time <=30 then '20<x<=30'
            when avg_time>30 then '>30' end time_segment
from 
(
select restaurant_name,
       substring(addr, 1, 2) sido,
       avg(delivery_time) avg_time
from food_orders
group by 1, 2
) a

4. [실습] 복잡한 연산을 Subquery 로 수행하기
1) [실습] 음식 타입별 총 주문수량과 음식점 수를 연산하고,
주문수량과 음식점수 별 수수료율을 산정하기
(음식점수 5개 이상, 주문수 30개 이상 -> 수수료 0.05%
음식점수 5개 이상, 주문수 30개 미만 -> 수수료 0.08%
음식점수 5개 미만, 주문수 30개 이상 -> 수수료 1%
음식점수 5개 미만, 주문수 30개 미만 -> 수수료 2%)
select cuisine_type, 
total_quantity
count_res
       case when count_of_restaurant>=5 and count_of_orders>=30 then 0.0005
            when count_of_restaurant>=5 and count_of_orders<30 then 0.008
            when count_of_restaurant<5 and count_of_orders>=30 then 0.01
            when count_of_restaurant<5 and count_of_orders<30 then 0.02 end ratio_of_add
from
(
select cuisine_type,
       sum(quantity) total_quantity,
       count(distinct restaurant_name) count_res
from food_orders
group by 1
) a

2) [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건
  수량이 5개 이하 → 10%
  수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
   이 외에는 일괄 1%)
select restaurant_name,
       case when sum_of_quantity<=5 then 0.1
            when sum_of_quantity>15 and sum_of_price>=300000 then 0.005
            else 0.01 end ratio_of_add
from 
(
select restaurant_name,
       sum(quantity) sum_of_quantity,
       sum(price) sum_of_price
from food_orders
group by 1
) a

5. 필요한 데이터가 서로 다른 테이블에 있을 때 조회하기 (JOIN)
1) Join 이 필요한 경우
💡 주문 가격은 주문테이블에 있지만, 어떤 수단으로 결제를 했는지는 결제테이블에 있어요
💡 주문을 한 사람을 확인하려면, 주문 테이블과 고객 테이블에서 각각 정보를 가져와서 엑셀에서 합쳐줘야 해요
💡 주문 건별 수수료를 계산하려면 수수료율이 필요한데, 결제 테이블에 있어서 어떻게 연산할 수 있을지 모르겠어요

2) Join의 기본 원리와 중류
- Join 은 기본적으로 엑셀의 Vlookup 과 유사합니다
- 주문 정보에서 고객 이메일을 알기 위해서는, 고객 정보에서 동일한 고객 ID 의 이메일을 가져와야 합니다
(엑셀에서는 `vlookup(고객ID, 고객 정보, 3, False)` 라고 적으실 것입니다)
- JOIN 은 동일한 원리를 가집니다. 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 고객 ID 를 기준으로 필요한 값을 가져와 주는 것입니다.
- 이 때 중요한 것은, 두 테이블이 공통으로 갖고 있는 컬럼이 됩니다. 위의 예시에서는 ‘고객 ID’가 되겠죠
- 공통 컬럼을 기준으로 묶은 형태는 아래와 같이 됩니다. 
즉, 공통 컬럼을 기준으로 두 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것입니다.
조인은 하는 방법에 따라 여러가지가 있어요. 
갑자기 복잡해지죠? 전혀 걱정하지 마세요. 원리는 동일합니다!
- LEFT JOIN : 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우를 의미합니다. 우리의 예시로 보면 아래와 같습니다. 
- INNER JOIN : 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회합니다. 
즉, 위의 예시에서 주문번호 14의 경우, 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외됩니다.

2) Join의 기본 구조
-- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

3) [실습] JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기
    주문 테이블과 고객 테이블을 cusomer_id 를 기준으로 left join 으로 묶어보기
    (조회 컬럼 : order_id, customer_id, restaurant_name, price, name, age, gender)
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
    
6. [실습] JOIN 으로 두 테이블의 데이터 조회하기
1) [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회
select a.order_id,
       a.restaurant_name,
       a.price,
       b.pay_type,
       b.vat
from food_orders a left join payments b on a.order_id=b.order_id
where cuisine_type='Korean'

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

 3) [실습] null 값을 제거하려면
select distinct c.name,
       c.age,
       c.gender,
       f.restaurant_name
from food_orders f left join customers c on f.customer_id=c.customer_id
where c.name is not null // null 값 제거
order by c.name

7. [실습] JOIN 으로 두 테이블의 값을 연산하기
1) [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기 
    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
    *수수료율이 있는 경우만 조회
select a.order_id,
       a.restaurant_name,
       a.price,
       b.vat,
       a.price*b.vat "수수료율"
from food_orders a inner join payments b on a.order_id=b.order_id  
    
2) [실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기
    (조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
    *할인 : 나이-50*0.005
    * 고객 정보가 없는 경우도 포함하여 조회, 할인 금액이 큰 순서대로 정렬
select cuisine_type,
       sum(price) "원래 가격",
       sum(price)-sum(discount_price) "할인 적용 가격",
       sum(discount_price) "할인 가격" 
from 
(
select a.cuisine_type,
       price,
       price*((b.age-50)*0.005) discount_price
from food_orders a inner join customers b on a.customer_id=b.customer_id
where b.age>=50
) t
group by 1
order by 4 desc

ORDER BY는 SELECT문에서 나온 결과를 정렬할 때 사용한다.
정렬에는 오름차순(Ascending) 과 내림차순(Descending)이 존재하고
각각 앞자리를 따서 ASC(오름차순), DESC(내림차순)라고 명시한다.

8. HW. 4주차 숙제 해설
식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
- 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬
1)
select name,
avg_price,
avg_age,
case when 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 'price_group',
case when avg_age < 30 then 'age_group1'
when avg_age >= 30 and avg_age < 40 then 'age_group2'
when avg_age >= 40 and avg_age < 50 then 'age_group3'
else 'age_group4'
end 'age_group'
from
(
select f.restaurant_name name,
avg(f.price) avg_price,
avg(c.age) avg_age
from food_orders f inner join customers c on f.customer_id = c.customer_id 
group by 1
) a
order by name

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