01. 문자 포맷 가공 : REPLACE, SUBSTRING, CONCAT

  • -Query 결과를 바로 사용할 수 있도록 문자 데이터의 형태를 바꾸는 함수 
  • 전체주소에서 '시도' 정보만 필요할 경우
  • 식당 이름과 지역이 함께 나오도록 포멧을 변경해야 하는 경우

1) REPLACE

REPLACE 특정 문자를 바꾸는 기능 replace(바꿀 컬럼, 현재 값, 바꿀 값) 
SELECT addr "원래주소",
       replace(addr,'팔판동','팔동') "바뀐주소"
FROM food_orders fo 
where addr like'%팔판동%'

 

2) SUBSTRING

SUBSTRING 특정 문자만 골라서 조회하는 기능 substr(조회 할 컬럼, 시작 위치, 글자 수) 
select addr "원래 주소",
       substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'

 

3) CONCAT

CONCAT 여러 컬럼에 있는 문자를 하나로 합치는 기능 concat(붙이고 싶은 값1, 붙이고 싶은 값2, .....) 
select restaurant_name "원래 이름",   
       addr "원래 주소",
       concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'

 

 

02. 조건에 따라 포맷 가공 : IF, CASE

  • 조건에 따라 다른 연산을 하는 방법
  • SQL 은 조건에 따라 연산을 적용할 수 있는 기능을 제공하여 ’내가 원하는 범주’ 를 조건으로 주고, 해당 범주에 적용하고 싶은 것을 지정해 주는 방식

1) IF

IF 조건을 지정해주는 가장 기본 문법
※ 엑셀의 기능과 유사		 if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때) 
#음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정
select restaurant_name,
       cuisine_type "원래 음식 타입",
       if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders

 

2) CASE

CASE  두 개 이상 지정해야 하는 경우 
CASE문을 이용해 여러번의 IF문을 적용한 효과 낼 수 있음		 case when 조건1 then 값(수식)1
        when 조건2 then 값(수식)2
         else 값(수식)3 end 
#주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용
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

 

03. Subquery

  • 여러 번의 연산을 한 번의 SQL 문으로 수행
  • '문자 변경 > 숫자 연산> 결과로 연산' 등의 경우 Subquery으로 효율적이고 알아보기 쉽게 사용
select column1, special_column
from
    ( /* subquery */
    select column1, column2 special_column
    from table1
    ) 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

 

 

04. JOIN

  • 여러 테이블에서 데이터를 불러오는 방법, 엑셀의 vlookup과 유사함
  • JOIN 은 각각 주문 정보와 고객 정보가 테이블이라고 할 때, 두 테이블이 공통으로 가지고 있는 컬럼인 고객 ID 를 기준으로 필요한 값을 가져옴.
LEFT JOIN 공통 컬럼 (키값) 을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우 (중복값이 없어도 불러옴) -- LEFT JOIN
select 조회 할 컬럼
from 테이블1 a left join 테이블2 b on a.공통컬럼명=b.공통컬럼명
INNER JOIN 공통 컬럼 (키값) 을 기준으로, 두 테이블 모두에 있는 값만 조회 -- INNER JOIN
select 조회 할 컬럼
from 테이블1 a inner join 테이블2 b on a.공통컬럼명=b.공통컬럼명

※ 컬럼명이 달라도 불러올 수 있음. ex) a.주문아이디=b.order_id

 

예시) 

식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

- 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 30,000 / 30,000 초과

- 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

 

① Join을 사용하여 필요한 컬럼(price, age)이 있는 테이블(food_orders, customers)을 불러옴. 

② avg를 사용해 평균 주문 금액과, 평균 나이를 계산함 

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

 

③ 값을 구한 테이블에서 case 를 사용해 문제에 제시된 기준으로 평균 주문 금액, 평균 연령을 재분류함 

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

티스토리툴바