SQL 실무 기초 (3) - 문자 다듬기, 수치 계산/문자 연산
* 문자 데이터를 원하는 포맷으로 출력하기 위해서는 어떻게 할까?
* 배달 시간 구간에 따라서 수수료를 다르게 계산하고 싶은데, 시간을 조건으로 둘 수는 없을까?
* 왜 Query를 쓰는데 오류가 나는걸까?
이 3가지 키포인트를 중점으로 오늘 SQL을 공부해보겠다.
============================
먼저 문자를 다듬는 방법으로, 크게 3가지 함수를 살펴보려고 한다!
바로 Replace, Substring, Concat 함수다.
데이터베이스 값을 만약 사람이 썼다면, 실수는 언제나 발생할 수 있다. 데이터베이스 값을 애초에 적을 때 잘못 적었을 수도 있을 것이고, 그 값이 중간에 수정되어 다른 값으로 변경해 줘야 할 필요도 있을 것이다. 이럴 때 Replace 함수를 사용한다.
replace(바꿀 컬럼, 현재 값, 바꿀 값)
예제는 다음과 같다.
select addr "원래 주소",
replace(addr, '문곡리', '문가리') "바뀐 주소"
from food_orders
where addr like '%문곡리%'
Select 내에서 사용할 값과, 그 값을 바꿔서 어떻게 출력할건지에 대한 구문이다. 주소 중에 '문곡리'라는 지역이 있거든 모두 '문가리'로 변경해서 출력하게 한다.
두번째는 Substring.
주소가 '서울특별시 성북구 장위1동' 과 같은 형태이지만 내가 필요한 정보는 오직 '서울특별시' 일 수 있다. 이럴 때 특정 부분만 추출하는 것이 가능하다.
substr(조회 할 컬럼, 시작 위치, 글자 수)
예제는 다음과 같다.
select addr "원래 주소",
substr(addr, 1, 2) "시도"
from food_orders
where addr like '%서울특별시%'
'서울특별시'가 들어간 문자의 첫번째 글자부터 2개의 글자를 따오도록 하고 있다.
보통 주소는 ..시로 시작하기 때문에 여기서는 '서울'을 가져오려는 의도를 갖고 있다.
마지막은 Concat 함수다.
데이터베이스에 저장된 데이터를 합쳐서 출력하고 싶은 경우도 있을 것이다. [서울시] 한식집 한가위... 이런 방식으로 말이다. 이럴 때 여러 컬럼의 값을 하나로 합칠 수 있는 기능이다.
concat(붙이고 싶은 값1, 붙이고 싶은 값2, 붙이고 싶은 값3, .....)
예제는 다음과 같다.
select restaurant_name "원래 이름",
addr "원래 주소",
concat('[', substring(addr, 1, 2), '] ', restaurant_name) "바뀐 이름"
from food_orders
where addr like '%서울%'
concat을 사용하면 다양한 문자와 데이터를 하나의 형식으로 합치는 게 가능하다.
여기에서는 '서울'이 주소라면 '[서울] 음식점 이름'으로 이름을 바꾼 걸 볼 수 있다.
=================================
두번째로는 조건문에 대해 알아보자.
SQL에서는 IF 와 CASE를 사용해서 조건문을 만들 수가 있는데, 두 가지의 차이점으로는,
조건에 따라 다른 방법을 적용하고 싶을 때 : IF 문
조건을 여러가지 지정하고 싶을 때 : CASE 문
먼저 IF 문은 엑셀 기능과 상당히 비슷한데,
if(조건, 조건을 충족할 때, 조건을 충족하지 못할 때)
select restaurant_name,
cuisine_type "원래 음식 타입",
if(cuisine_type='Korean', '한식', '기타') "음식 타입"
from food_orders
배달정보를 담은 Table에서 레스토랑 이름과 원래 음식 타입, 그리고 IF문을 사용해서 Korean일 때는 '한식'으로 적고 그렇지 않으면 '기타'로 음식 타입을 적게 했다.
결과는 다음과 같다.
그리고 아래와 같이 다른 함수나 구문 안에서도 사용할 수 있다.
select substring(if(email like '%gmail%', replace(email, 'gmail', '@gmail'), email), 10) "이메일 도메인",
count(customer_id) "고객 수",
avg(age) "평균 연령"
from customers
group by 1
이메일 형식이 gmail인 경우 데이터베이스에서 삭제되는 오류가 있어서, gmail이라면 데이터베이스 값을 @gmail로 바꾸고 나서 @ 이후에 나오는 이메일 도메인을 얻어오려는 것을 보여준다.
다음은 CASE문이다.
case when 조건1 then 값(수식)1
when 조건2 then 값(수식)2
else 값(수식)3
end
조건 1, 2 말고도 더 많은 조건에 대해서도 작성이 가능하다. 조건 1에 해당하면 수식 1을, 조건 2에 해당하면 수식 2를, 어디에도 포함되지 않으면 수식 3을 실행한다. 시작과 끝은 CASE와 END로 감싸준다.
select restaurant_name,
cuisine_type AS "원래 음식 타입",
case when (cuisine_type='Korean') then '한식'
else '기타'
end as " 음식 타입"
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
그리고 여긴 주소에 '경기도'가 포함되었다면 '경기도'로, '특별'이나 '광역'이 포함되었다면 '서울특별시', '대전광역시' 등으로, 그렇지 않다면 '강원' 등으로 변경된 주소를 표시했다.
이와 같이 조건은 새로운 카데고리를 만들 때, 연산식을 적용할 조건을 지정할 때 사용할 수 있고, 다른 문법 안에서도 자유롭게 적용할 수 있다.
======================================
마지막으로 SQL문에 문제가 없는 것 같은데 오류가 날 때 대처법을 알아보자.
avg, substring 등을 썼을 때 'data type' 부분에 오류가 있다는 메시지를 받으면 어떻게 할까?
MYSQL은 조금 유연해서 연산에 있어 자료형을 다 맞추지 않아도 알아서 변환을 해 줌으로 문제가 없지만, 다른 SQL 문법에서는 자료형이 다를 때 연산이 되지 않는 경우가 있다.
예를 들어서 데이터베이스에 rating이라는 값이 있다고 하자.
분명히 내가 생각했던 평점은 언제나 숫자 값이지만, 평점이 없는 경우 Not given으로 표시되었다.
이런 경우 Cast 함수를 사용해서 의도적으로 이 자료형은 숫자값이라는 명시를 꼭 해줘야만 한다.
자료형 타입이 문제라면, 데이터베이스를 한번 보면서 당연하다 생각했던 자료형 타입과 다른 값이 데이터로 들어있지는 않은지 확인을 한번 해보자!
4주차 때는 Subquery를 사용해서 복잡한 연산을 수행하고, join을 사용해서 많은 여러 테이블 정보를 한번에 조회하는 방법을 알아보자!