23.12.26. D6

주제 : 엑셀보다 쉽고 빠른 SQL 4weeks

소주제 :Subquery

메모

• 여러번의 연산 수행
  조건문에 연산 결과 사용
  조건에 Query 결과 사용
• select column1, special_column
  from
  ( / subquery /
  select column1, column2 special_column
  from table1
  ) a

• 4-3 실습 코드
  

코드를 입력하세요

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-4 실습 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.005
              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
  
  1. 음식 타입, 지역별??? 총 주문 수량, 음식점 수 연산하기 (group by 필수)
  2. 연산을 다시 할 괄호 지정
  3. case 사용해서 단위별 수수료 지정
  

• 4-4 실습 2
  [실습] 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기

  (할인조건 :
    수량이 5개 이하 → 10%
    수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5%
     이 외에는 일괄 1%)
  
  ```jsx
  select restaurant_name,
         sum_price,
         sum_quantity,
         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 discount_rate
  from 
  (
  select restaurant_name,
         sum(quantity) sum_of_quantity,
         sum(price) sum_of_price
  from food_orders
  group by 1
  ) a
  
  1. 음식점, 총 주문수량, 총 주문금액 연산하기
  2. 해당 연산 괄호 지정
  3. 음식점, 할인율(case 사용해서 단위별 할인율 지정) 연산
   ( ) : 알아보기 쉽게 앞서 연산한 값 다시 불러오기
  ```

소주제 : 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.공통컬럼명

• 4-5 실습
  JOIN 을 이용하여 두 개의 테이블에서 데이터를 조회해보기

  ```jsx
  주문 테이블과 고객 테이블을 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
  
  * 테이블도 컬럼처럼 별명을 붙여줄수 있다.
  ```

• 4-6 실습
  [실습] 고객의 주문 식당 조회하기

  (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) 
  *고객명으로 정렬, 중복 없도록 조회
  
  ```jsx
  select distinct a.name,
         a.age,
         a.gender,
         b.restaurant_name
  from customers a inner join food_orders b on a.customer_id=b.customer_id
  
  * 어떤 테이블에서 어떤 컬럼을 이용하고 어떤 수식을 활용할지 미리 생각하자
    left join on / order by / distinct : 중복 삭제
  ```

• 4-7 실습 2
  [실습] 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기

  (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
  *수수료율이 있는 경우만 조회
  
  ```jsx
  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
  
  * 두 테이블의 칼럼을 연산해야할 경우 : 테이블명.칼럼명(연산기호)테이블
  ```

• 4-7 실습 3
  [실습] 50세 이상 고객의 연령에 따라 경로 할인율을 적용하고, 음식 타입별로 원래 가격과 할인 적용 가격 합을 구하기

(조회 컬럼 : 음식 타입, 원래 가격, 할인 적용 가격, 할인 가격)
*할인 : 나이-500.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

1. 어떤 테이블 어떤 컬럼을 쓸 건지, left or inner 정하기
2. 불러와야할 칼럼(테이블명 포함)과 연산 작성 후 조회(where로 조건 설정하기)
3. 불러온 칼럼으로 필요한 정보 나열하기 
4. 필요하다면 group by, order by로 조건 설정하기