[TIL #8]

SQL

case문

select case when cuisine_type ='Korean' then '한식'
			when cuisine_type in ('Japanese','Chinese') then '아시아'
			else '기타' END "음식타입",
		cuisine_type 
from food_orders

• 우선 case를 써준후 when을 통해 어떠한 값을 지정할것인지 설정한 후
  then을 통해 이름을 정해준다.
  두 개의 값을 같은 이름으로 지정할때는 in( )을 사용해서 한번에 묶어줄 수 있다.
  그외의 값들은 else로 정리
  
  

실습

1. 다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.
주중 : 25분 이상
주말 : 30분 이상

문제 조건이 다음과 주어 졌을때

select order_id,
		restaurant_name,
		day_of_the_week,
		delivery_time,
case when day_of_the_week = 'Weekday' then if(delivery_time>24, 'Late','On-time')
			when day_of_the_week = 'Weekend' then if(delivery_time>29, 'Late','On-time') 
			end '지연여부'
from food_orders

답안을 이렇게 작성하였다.

• 우선 case문을 작성하여 day_of_the_week 가 'Weekday','Weekend'로 나누어 주었고, then 이후에 if문을 통하여 delivery_time을 설정하여 조건을 만족하였을때는 Late, 그렇지 않을때는 On-time으로 설정하여 해결했다.
  
  

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

문제 조건이 다음과 같이 주어졌을 때,

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

답안을 작성하였다.

• 총 주문수량과 주문 금액을 먼저 연산하여 subquery형태로 작성해 주고,
  subquery를 기반으로 수수료 할인율을 구하였다.
  
  

JOIN 연산

• 필요한 데이터가 서로 다은 테이블에 있을때 사용한다.
  - 공통 컬럼을 기준으로 테이블을 합쳐서, 각각 테이블에서 필요한 데이터를 조회할 수 있도록 만들어주는 것이 JOIN 연산이다!

기본적으로 공부한 이미지를 직접 가져와서 사용하지는 않지만 가장 좋은 설명의 예시인거 같아 들고 왔다.

• LEFT 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.공통컬럼명

실습

1. [실습] 한국 음식의 주문별 결제 수단과 수수료율을 조회하기

(조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
*결제 정보가 없는 경우도 포함하여 조회

문제 조건이 다음과 같이 주어졌을 때,

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 cuisine_type = 'Korean'

이렇게 답안을 작성하였다.

2. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
	- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
	- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

문제 조건이 다음과 같이 주어졌을 때,

SELECT restaurant_name,
	   price_avg,
	   age_avg,
	   case when price_avg <= 5000 then 'price_group1'
	        when 5000 < price_avg and price_avg <=10000 then 'price_group2'
	   		when 10000 < price_avg and price_avg <=30000 then 'price_group3'
	   		else 'price_group4' end 'price_group',
	   case when 0 < age_avg and age_avg <= 29 then 'age_group1'
	   		when 30 < age_avg and age_avg <= 39 then 'age_group2'
	   		when 40 <age_avg and age_avg <= 49 then 'age_group3'
	   		else 'age_group4' end 'age_group'
FROM 
(
SELECT f.restaurant_name,
	   avg(f.price) price_avg,
	   avg(f.food_preparation_time) age_avg 
From food_orders f inner join payments p on f.order_id = p.order_id 
group by 1 
) a
ORDER BY restaurant_name ASC

1. 연령과 음식을 가지고 있는 테이블을 찾아 두 테이블을 join 한다.
2. 평균 음식과 평균 연령을 기반으로 구해야 하기 때문에
   Subquery를 활용하여 평균을 구해준다.
3. 구해진 평균값을 가지고 case 문을 작성하여 조건을 걸어 그룹을 나누어준다.