[TIL] 240507 <SQL> REPLACE,SUBSTRING,CONCAT,IF,CASE,Subquery,JOIN

 

 

>> sql문 실행 단축키 : ctrl + enter

 

 

REPLACE : 특정 문자를 다른 문자로 바꾸기

replace(바꿀 컬럼, 현재 값, 바꿀 값)

 

select addr "원래 주소",
       replace(addr, '문곡리', '문가리') "바뀐 주소"
from food_orders
where addr like '%문곡리%'

 

 

---

SUBSTRING(SUBSTR) : (특정 위치의) 특정 문자만 조회

substr(조회 할 컬럼, 시작 위치, 글자 수)

 

select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

// 서울지역의 음식타입별 평균음식 주문금액
select substring(addr, 1, 2) "시도",
       cuisine_type "음식 종류",
       avg(price) "평균 금액"
from food_orders
where addr like '%서울%'
group by 1, 2
//group by절의 1은 select의 첫번째에 있는 substring(addr, 1, 2) "시도"이고,
//2는 select의 두번째에 있는 cuisine_type "음식 종류"인데
//위처럼 select문의 몇번째 것을 이용할 것인지 숫자로 작성가능!!!

// 이메일 도메인별 고객 수와 평균 연령
select substring(email, 10) "도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

 

 

---

CONCAT : 여러 컬럼의 문자 합치기

concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)

• 붙일 수 있는 문자의 종류
  • 컬럼
  • 한글
  • 영어
  • 숫자
  • 기타 특수문자

 

select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

// ‘[지역(시도)] 음식점이름 (음식종류)’ 컬럼을 만들고, 총 주문건수 구하기
select concat('[', substring(addr, 1, 2), '] ', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
       count(1) "주문건수"
from food_orders
group by 1

 

 

---

IF문 : 조건에 따라 다른 방법을 적용하고 싶을 때

if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)

 

select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
       count(customer_id) "고객 수",
       avg(age) "평균 연령"
from customers
group by 1

select order_id,
       price,
       quantity,
       day_of_the_week,
       if(day_of_the_week='Weekday', 3000, 3500)*(if(quantity<=3, 1, 1.2)) "할증료"
from food_orders

 

 

---

CASE문 : 조건을 여러가지 지정하고 싶을 때

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

 

select order_id,
       price,
       quantity,
       case when quantity=1 then price
            when quantity>=2 then price/quantity end "음식 단가"
from food_orders

select restaurant_name,
       addr,
       case when addr like '%경기도%' then '경기도'
            when addr like '%특별%' or addr like '%광역%' then substring(addr, 1, 5)
            else substring(addr, 1, 2) end "변경된 주소"
from food_orders

select name,
       age,
       gender,
       case when (age between 10 and 19) and gender='male' then "10대 남자"
            when (age between 10 and 19) and gender='female' then "10대 여자"
            when (age between 20 and 29) and gender='male' then "20대 남자"
            when (age between 20 and 29) and gender='female' then "20대 여자" end "그룹" 
from customers
where age between 10 and 29

select restaurant_name,
       price/quantity "단가",
       cuisine_type,
       order_id,
       case when (price/quantity <5000) and cuisine_type='Korean' then '한식1'
            when (price/quantity between 5000 and 15000) and cuisine_type='Korean' then '한식2'
            when (price/quantity > 15000) and cuisine_type='Korean' then '한식3'
            when (price/quantity <5000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식1'
            when (price/quantity between 5000 and 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식2'
            when (price/quantity > 15000) and cuisine_type in ('Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '아시아식3'
            when (price/quantity <5000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타1'
            when (price/quantity between 5000 and 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타2'
            when (price/quantity > 15000) and cuisine_type not in ('Korean', 'Japanese', 'Chinese', 'Thai', 'Vietnamese', 'Indian') then '기타3' end "식당 그룹"
from food_orders

select restaurant_name,
       order_id,
       delivery_time,
       price,
       addr,
       case when delivery_time>25 and delivery_time<=30 then price*1.05*(if(addr like '%서울%', 1.1, 1))
            when delivery_time>30 then price*1.1*(if(addr like '%서울%', 1.1, 1))
            else 0 end "수수료"
from food_orders

 

 

---

Data Type 오류 해결하기

--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal) 
 
--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

 

 

---

Subquery (여러 번의 연산을 한 번의 SQL문으로 수행)

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)

 

// 음식점의 평균 단가별 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

// 음식점의 지역과 평균 배달시간으로 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

// 음식 타입별 총 주문수량과 음식점 수를 연산하고, 
//          주문수량과 음식점수 별 수수료율을 산정하기
    
//    (음식점수 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

// 음식점의 총 주문수량과 주문 금액을 연산하고, 주문 수량을 기반으로 수수료 할인율 구하기
    
//    (할인조건
//      수량이 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

 

 

 

 

 

---

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

 

// 한국 음식의 주문별 결제 수단과 수수료율을 조회하기
    
//    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 결제 수단, 수수료율)
//    *결제 정보가 없는 경우도 포함하여 조회
 
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'

// 고객의 주문 식당 조회하기
    
//    (조회 컬럼 : 고객 이름, 연령, 성별, 주문 식당) 
//    *고객명으로 정렬, 중복 없도록 조회 (distinct)
 
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

// 주문 가격과 수수료율을 곱하여 주문별 수수료 구하기
    
//    (조회 컬럼 : 주문 번호, 식당 이름, 주문 가격, 수수료율, 수수료)
//    *수수료율이 있는 경우만 조회
 
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

// 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

// 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기
//      평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과
//      평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상
//      두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬
 
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 f.restaurant_name,
	   avg(price) price,
	   avg(age) age
from food_orders f inner join customers c on f.customer_id = c.customer_id
group by 1
) a
order by 1