SQL - Day2

case 함수 : 한 컬럼에 조건을 여러개 주고 싶을 때

case when 조건1 then 값(수식)1 
     when 조건2 then 값(수식)2 
     else 값(수식)3 
end

 

* 출력 결과 컬럼명 옆의 ‘ABC’ 는 문자로 저장이 되어있다는 의미입니다.

 

데이터 타입 변경

--숫자로 변경 
cast(if(rating='Not given', '1', rating) as decimal) 

--문자로 변경 
concat(restaurant_name, '-', cast(order_id as char))

 

 

Subquery

• Query 결과를 Query 에 다시 활용하는 것
• 여러번의 연산을 수행해야 할 때
• 조건문에 연산 결과를 사용해야 할 때
• 조건에 Query 결과를 사용하고 싶을 때

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)

 

* 사용 예)

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

* 서브쿼리만 실행을 원할 경우 해당 부분만 선택(마우스 드래그)후 쿼리 실행

해석
첫번째 서브쿼리
from 
(
select restaurant_name, avg(price/quantity) price_per_plate
from food_orders
group by 1
) a

> restaurant_name별 단가 컬럼으로 구성된 테이블1(=restaurant_name별 단가값이 price_per_plate 값으로 연산 됨)

두번째 서브쿼리
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

> 첫번째 서브쿼리에서 연산된 price_per_plate이 case에 조건으로 활용 됨
> restaurnt_name, ratio_of_add, price_per_plate로 구성된 테이블 

메인쿼리
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
> 첫번째 서브쿼리에서 연산된  price_per_plate, 두번째 서브쿼리에서 연산된 ratio_of_add 사용

 

* 실습)
음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
(할인조건 수량이 5개 이하 → 10% 수량이 15개 초과, 총 주문금액이 300000 이상 → 0.5% 이 외에는 일괄 1%)
> food_orders 테이블 (구성 컬럼 정보 : order_id, customer_id, restaurant_name, cuisine_type, price, quantity, day_of_the_week, rating, food_preparation_time, delivery_time, addr)

select restaurant_name,
	total_quantity,
	total_price
	case 
		when total_quantity <= 5 then 0.1
		when total_quantity > 15 and total_price >= 300000 then 0.005
		else 0.01 end rate
from
(select restaurant_name,
	sum(quantity) total_quantity,
	sum(price) total_price
from food_orders
group by 1
) a

join

2개 이상 테이블 동시 조회 필요 시
* 조인 기능 이용 시, 조인할 테이블끼리 동일한 컬럼이 있어야 함
* 두 테이블의 공통 컬럼명은 달라도 Ok. 예를 들어 주문정보에는 ‘고객ID’, 고객정보에는 ‘고객아이디’ 라고 컬럼명이 되어있다면, 테이블1.고객ID=테이블2.고객아이디 와 같이 묶을 수 있음

-- LEFT JOIN : a 테이블 컬럼 기준으로 추출
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명

-- INNER JOIN : a, b 테이블에 모두 존재하는 컬럼만 추출(a, b 교집합)
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명