업무에 필요한 문자 포맷이 다를 때 SQL로 가공하기(REPLACE, SUBSTRING, CONCAT)
REPLACE 함수
특정문자를 다른 것으로 바꿀 수 있는 기능을 제공
replace(바꿀 컬럼, 현재 값, 바꿀 값)
EX) 식당 명의 ‘Blue Ribbon’ 을 ‘Pink Ribbon’ 으로 바꾸기
select restaurant_name "원래 상점명",
replace(restaurant_name, 'Blue', 'Pink') "바뀐 상점명"
from food_orders
where restaurant_name like '%Blue Ribbon%'
주의사항:
- REPLACE 함수는 대소문자를 구분하지 않는다.
- 문자열의 일부를 일치시키기 위해 LIKE 연산자와 함께 사용할 수 있다.
- UPDATE 문에서 사용할 때, 조건에 맞는 행에 대해서만 대체 작업이 수행된다.
SUBSTRING(SUBSTR) 함수
원하는 문자만 남기기
전체 데이처가 아닌 특정 문자만 필요할 때, SQL로 필요한 부분만 조회할 수 있다.
substr(조회 할 컬럼, 시작 위치, 글자 수)
EX) 서울 음식점들의 주소를 전체가 아닌 ‘시도’ 만 나오도록 수정
select addr "원래 주소",
substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'
주요 사항:
- 시작 위치: 대개 첫 번째 문자가 1번째 위치입니다. 몇몇 데이터베이스 시스템에서는 시작 위치를 0부터 시작하는 경우도 있다.
- 길이: 선택적 매개변수로, 생략하면 시작 위치부터 문자열의 끝까지 반환된다.
CONCAT 함수
원하는 문자가 여러 컬럼에 있을 때, 하나로 합쳐서 업무에 필요한 형태로 만들 수 있다.
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
붙일 수 있는 문자의 종류
- 컬럼
- 한글
- 영어
- 숫자
- 기타 특수문자
서울시에 있는 음식점은 ‘[서울] 음식점명’ 이라고 수정
select restaurant_name "원래 이름",
addr "원래 주소",
concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'
주요 사항:
- CONCAT 함수는 매개변수로 전달된 문자열들을 순서대로 이어붙여서 하나의 문자열로 반환한다.
- 매개변수로 넘긴 값들은 모두 문자열로 형변환된다. 필요한 경우 형변환 함수를 사용하여 데이터 타입을 맞추어야 할 수 있다.
문자 데이터를 바꾸고, GROUP BY 사용하기
EX)서울 지역의 음식 타입별 평균 음식 주문금액 구하기
(출력 : ‘서울’, ‘타입’, ‘평균 금액’)
select substring(addr, 1, 2) "시도",
cuisine_type "음식 종류", //컬럼 지정
avg(price) "평균 금액" //AGE, SUBSTRING 함수이용
from food_orders //테이블에서 데이터 뽑기
where addr like '%서울%' //조건 지정
group by 1, 2
조건에 따라 포맷을 다르게 변경해야한다면 (IF, CASE)
IF 문
원하는 조건에 충족할 때 적용할 방법과 아닌 방법을 지정이 가능하다. 프로그래밍에서 조건을 평가하고, 그 결과에 따라 다른 코드 블록을 실행하도록 하는 제어 구조이다. SQL에서도 IF 문이나 CASE 문을 사용하여 조건에 따라 다른 결과를 반환하거나 실행할 수 있다
.
if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
EX) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Korean’ 이 아닌 경우에는 ‘기타’ 라고 지정
select restaurant_name,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders
EX2) '문곡리’ 가 평택에만 해당될 때, 평택 ‘문곡리’ 만 ‘문가리’ 로 수정
select addr "원래 주소",
if(addr like '%평택군%', replace(addr, '문곡리', '문가리'), addr) "바뀐 주소"
from food_orders
where addr like '%문곡리%'
EX3) 잘못된 이메일 주소 (gmail) 만 수정을 해서 사용
CASE 문
각 조건별로 적용 할 값을 지정해 줄 수 있습니다.
if 문과의 비교
- SQL에서 IF 문은 CASE 문과 유사한 기능을 수행할 수 있지만, IF 문은 주로 프로시저나 함수 등에서 사용됩니다. 일반적으로 SQL에서는 CASE 문을 사용하여 조건에 따라 값을 반환하거나 다양한 처리를 수행하는 것이 효율적입니다.
- CASE 문은 여러 조건을 한 번에 처리할 수 있는 반면, IF 문은 단일 조건을 처리하는 데 주로 사용됩니다.
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end
EX) 음식 타입을 ‘Korean’ 일 때는 ‘한식’, ‘Japanese’ 혹은 ‘Chienese’ 일 때는 ‘아시아’, 그 외에는 ‘기타’ 라고 지정
select restaurant_name,
cuisine_type "원래 음식 타입",
case when (cuisine_type='Korean', '한식', '기타') then "음식 타입"
from food_orders
EX2) 음식 단가를 주문 수량이 1일 때는 음식 가격, 주문 수량이 2개 이상일 때는 음식가격/주문수량 으로 지정
select order_id,
price,
quantity,
case when quantity=1 then price
when quantity>=2 then price/quantity end "음식 단가"
from food_orders
EX3) 주소의 시도를 ‘경기도’ 일때는 ‘경기도’, ‘특별시’ 혹은 ‘광역시’ 일 때는 붙여서, 아닐 때는 앞의 두 글자만 사용
조건을 사용할 수 있는 경우
- 새로운 카테고리 만들기
- 음식 타입과 같이 새로운 카테고리를 만들 수 있습니다.
- 한국 음식, 아시아 음식, 미국 음식, 유럽 음식 이런 식의 새로운 cuisine_category 를 생성할 수 있죠
- 고객들의 분류도 만들 수 있습니다.
- 10대 여성, 10대 남성, 20대 여성, 20대 남성 등, 이런 식의 성별과 나이별로 새로운 고객 군 카테고리를 생성할 수 있죠
- 음식 타입과 같이 새로운 카테고리를 만들 수 있습니다.
- 연산식을 적용할 조건 지정하기
- 수수료를 계산할 때 흔히들 현금 사용, 카드사용을 나누고는 하죠
- 현금일 때의 수수료율과 카드일 때의 수수료율이 다르다면, 연산식을 만들 때 if 문 혹은 case 문으로 각각 다른 수수료율 혹은 수수료 계산 방식을 적용할 수 있습니다
- 다른 문법 안에서 적용하기
- if, case 문 안에 다른 문법이나 연산을 넣을 수도 있지만, 다른 문법 안에 조건문을 넣을 수도 있습니다
- 예를 들어 concat 문으로 여러 컬럼을 합칠 때, rating 이 있을 때는 rating 을 넣어주고 없을 때는 아무것도 넣지 않도록, concat 안에 if 문을 넣어줄 수 있다.
조건문과 수식을 이용하여 간단한 User Segmentation
10세 이상, 30세 미만의 고객의 나이와 성별로 그룹 나누기 (이름도 같이 출력)
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대 남자" //조건 지정 나이가 10세 이상, 30세 미만
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 "식당 그룹"
// 조건은 없지만 case, when ...end 수식을이용해 칼럼내에서 범위를 정한다.
from food_orders // 주문 테이블에서 데이터를 뽑는다.
Data Type 오류 해결하기
문자/숫자 계산을 했더니 오류가 날 경우
rating 은 숫자가 포함되어 있지만 문자 형으로 저장이 되어있다.
따라서 문자, 숫자를 혼합하여 함수에 사용 할 때에는 데이터 타입을 변경해주어야 한다.
--숫자로 변경
cast(if(rating='Not given', '1', rating) as decimal)
--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))