TIL 2024-08-29 (SQL - 3주차)

3주차 SQL 강의는 

업무에 필요한 문자 포맷이 다를 때, SQL로 가공하기(REPLACE, SUBSTRING. CONCAT)

 

REPLACE?

• SQL에서 특정 문자를 다른 것으로 바꿀 수 있는 기능을 제공.

EX) 최근 상점 이름이 바뀌었지만 데이터엔 예전 이름으로 저장되어있다.

EX) 지역명이 바뀌어서 수정이 필요할 때

 

사용 방법은 어렵지 않았다.

 

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

replace (addr, '문곡리', '문가리')

 

이런 식으로 바꿔주면 되는데 결과를 더욱 쉽게 보기 위해서

 

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

 

위 처럼 적으면 addr 컬럼에 문곡리가 들어가는 데이터만 추출해주며 문곡리를 문가리로 바꿔준다.

 

SUBSTRING(SUBSTR)

• 전체 데이터가 아닌 특정 문자만 필요할 때, SQL로 필요한 부분만 조회 가능.

EX) 전체 주소의 시도 부부만 필요할 때

 

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

 

나머지 구문은 앞서서 배운 내용이라 알고 있고 substr는 맨 처음 컬럼을 선택하고, 가운데에선 몇 번째 글자부터 가져올 것인지, 마지막 칸은 몇 글자를 가져올 것 인지를 정하는 것이다. (addr, 1, 2) 라는 건 addr 컬럼에서 첫 번째 글자부터 시작해서 2글자를 가져오라는 뜻.

 

위 Query문을 보면 substr이 있는데 substring이라고 적어도 되고 substr이라고 적어도 되는데 나는 더 짧은 걸 선호한다.

 

CONCAT 

• 원하는 문자가 여러 컬럼에 있을 때, 하나로 합치는 기능

EX) 서울시에 있는 음식점은 '[서울] 음식점명' 이라고 수정하고 싶을 때

 

붙일 수 있는 문자 종류는 컬럼, 한글, 영어, 숫자, 문자, 기타 특수문자 거의 다 된다고 보면 된다.

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

 

 

concat은 그냥 합치고 싶은 것들을 계속 순서대로 넣어주면 되는데

'[서울] 음식점명'으로 합치고 싶으면 '[' 문자이니 '' 붙혀주고 가운데 substr를 이용하여 addr에서 앞에 서울 2글자만 가져오고 뒤에 음식점명 컬럼을 적어주면 합쳐진 이름으로 결과가 나온다.

 

---

 

 

 

문자 데이터를 바꾸고, GROUP BY 사용하기

 

음식점별 주문건수를 확인하는 방법은 어렵지 않았다.

 

앞에서 배웠던 Query문에 group by,  count를 추가로 적어주면 된다.

 

EX) [지역(시도)] 음식점이름 (음식종류)의 컬럼을 만들고 주문 건수를 구하고 싶다면.

 

select concat('[', substr(addr, 1, 2), ']', restaurant_name, ' (', cuisine_type, ')') "바뀐이름",
count(1) "주문 건수"
from food_orders
group by 1

 

count문이 주문 건수를 계산해주고, group by가 중복을 없애준다.

 

바뀐 이름의 데이터는 [서울] Hangawi (Korean) 이런 식으로 나온다.

 

 

---

 

SQL에도 조건에 따른 연산하는 방법이 존재한다.

바로 IF, CASE 인데.

 

두 가지 사용 기준은.

 

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

CASE문은 조건을 여러가지 지정하고 싶을 때 사용한다.

 

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

if(cuisine_type = 'Korean', '한식', '기타')

 

 

case when 조건1 then 값(수식)1
     when 조건2 then 값(수식)2
     else 값(수식)3
     end
     
case when (cuisine_type = 'Korean') then '한식'
     when (cuisine_type = 'japanese') then '일식'
     else '기타' 
     end "음식 타입"

 

이처럼 사용하면 된다. 

 

if는 2가지 경우의 수만 있는 경우 사용하면 편리하고, 더 많은 조건과 값이 필요할 땐 case를 사용하는 것이 편하다.

 

---

 

SQL문에 문제가 없는 것 같은데 오류가는 나는 이유??(Data Type 오류 해결하기)

 

숫자 계산이나 문자 가공 시 자주 발생하는 오류라고합니다.

 

avg, substr 등 함수를 썼을 때 에러메시지에 'data type' 단어가 뜨면서 실행이 안되는 경우 해결방법인데

 

칼럼명 바로 옆에 'ABC' 혹은 '123'을 확인해야한다. 'ABC'로 적혀 있는 경우에는 문자이므로, 숫자가 적혀있어도 문자로 취급 되기 때문에 data type 에러가 발생한다.

 

따라서 문자, 숫자를 혼합하여 함수를 사용할 땐 데이터 타입을 변경해주어야 하는데.

 

--숫자로 변경
cast(if(rating = 'Not given', '1', rating) as decimal)

--문자로 변경
concat(restaurant_name, '-', cast(order_id as char))

 

decimal은 수를 뜻한다고 생각하고 char은 문자를 뜻한다고 생각하면 이해하기 어렵지 않다.

 

이번 강의는 대부분 실습으로 이해를 도왔고 모두 너무 길었기 때문에 굳이 TIL엔 쓰지 않았다.

마지막으로 숙제만 해석하며 끝내겠다.

 

3주차 숙제는
다음 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

- 주중 : 25분 이상

- 주말 : 30분 이상

 

숙제가 원하는 결과

똑같이 하기 위해 컬럼의 순서까지 맞춰줄 예정이다.

select order_id, restaurant_name, day_of_the_week, delivery_time,
       case when day_of_the_week = 'Weekday' and delivery_time >= 25 then 'Late'
            when day_of_the_week = 'Weekend' and delivery_time >= 30 then 'Late'
            else 'On-time' end "지연 여부"
from food_orders

 

칼럼의 순서를 맞추기 위해 왼쪽부터 순서대로 작성하였고 마지막에 case 문을 사용하였다.

 

주중과 주말을 구별해야하기에 case 문을 사용하여 'Weekday', 'Weekend'를 구별하였고 주말, 주중에 맞게 배달 시간의 조건을 붙여주고 그 조건을 만족할 시 'Late'로 지연 여부에 나오게끔 작성하였다. 두 조건을 제외한 나머지 조건은 else를 사용하여 'On-time'를 적어주면 완성이다. 마지막에 꼭 end를 쓰는 걸 까먹으면 안된다.